Summary
Symptom
CONTENTS
[I] Executive Summary
[II] Detailed Analysis
[II.A] Identify Query
[II.B] Run Query in RSRT
[II.C] Detailed Analysis in RSRT
[II.D] Analysis of SQL Statements
[II.E] BWA Access
[III] Optimizing Query Performance
[III.A] Query Design
[III.B] Data Modeling
In order to track down the query please run all queries used in the frontend report seperately in RSRT and assure that the same filter selections are used as in the frontend !
If the runtime problem only occurs for the template/workbook the following notes are helpful:
948158 Performance problems/measuring BI Java Web runtime
1083462 Statistics workbook for performance problems
and also
1025307 Composite note for BW 7.00 / BW 7.01 performance
In case its necessary to analyze a performance problem in a support message please attach the Statistics workbook/web file and refer to the event which needs more time as expected.
If the performance issue only occurs in a BO report, figure out what connection type is used to work with BW (MDX, BICS or RSDRI_DF).
If the runtime problem occurs for a query using the MDX interface, work with note 1381821 Memory and Performance Analysis for MDX
After you have narrowed the problem down to the execution of a single query in RSRT, there are still many influencing factors. Roughly we can devide them into two catagories which refer to the two main OT processing blocks during the query runtime:
OLAP Processor (or Analytic Engine) is the layer responsible for receiving and processing frontend requests. Depending on the navigation, the frontend sends information what characteristics are in the drilldown (rows and columns), what values have been filled into variables and what dynamic filters might have been set. This information is summarized (and merged with the fixed filters) into an OLAP request and handed over to the Data Manager. The Data Manager then retrieves the data from the respective InfoProvider, and hands it back to the OLAP Processor which then performs the calculations, currency translations etc defined in the query. See also note 1591837 where some more information is provided regarding the processing sequence
The first step is to find out in which area of query processing most of the time is spent. The most convenient way to do this is running the query in RSRT and having the Query Runtime Statistics displayed afterwards (see point [II.C]) It might also happen that the query virtually runs 'forever' and hence doesn't give you the possibility to check the runtime statistics. In such a case please monitor the corresponding process in SM50 and try to figure out where the query 'hangs':
-if you can see a long running SQL then please proceed with point [II.D]
-if the query processes ABAP coding its more difficult to locate the issue. You could start the debugger (SM50->menu->Program/Session->Program->Debugger) and take a look at the call stack. The processing blocks where most of the time is spent, can hopefully give you an idea of the area the performance issue is located. The name of the ABAP objects may also be used for a note search.
Please review note 1591837 where you can find more information about this important transaction RSRT (Query Monitor). As already mentioned, try first to get the runtime statistics displayed by clicking on the button 'Execute+Debug' and choosing the setting 'Display Statistics Data'. When you use this function you need to press 'Back(F3)' afterwards in order to get the statistcs displayed. In this note we don't explain all the Statistics Events but only focus on the most important ones. Please see the online help for a complete list:
http://help.sap.com/saphelp_autoid70/helpdata/EN/43/e39fd25ff502d2e10000000a1553f7/frameset.htm
Runtime Statistics
There are two tabs, the Frontend/Calculation Layer and the Aggreagtion Layer (and the tab BWA Access in case an BWA index was read). Pleas see note 1707239 for a detailed description how to use this feature.
Calculation Layer: In the column 'Duration' the runtime for each Event is given in seconds. Watch out for the ones with the highest numbers. The most important BEX OLAP Statistics Events are:
Aggregation Layer: It gives a list of all SQL/BIA accesses which were executed. In the column 'Viewed at' you can see how long it took, the other interesting columns are:
Records, Selected
Records, Transported
Basic Infoprovider
Aggregate
Table Type
In case the Datamanger is responsible for the long runtime this list allows you to break down the problem to single SQL/BIA statements. The columns 'Records, Selected' and 'Records, Transported' give an idea of which anmount of data had to be processed. Please see point [II.D] for further details. The field 'Aggregate' tells you which aggregate was used, if you see infoprov$X the BWA was used.
In case one of the other processing blocks is responsible for the perfomance issue the following general hints should be helpful:
In this chapter its discussed how SQL statements can be analyzed. As a reference data base we use ORACLE, but most of the points can directly be applied to other databases as well. In some cases the procedures are not exactly the same but similar. Lets start with a to-do-list which often already helps to solve the issue (see also note 1705026):
Please
(*) ensure that the database software is up-do-date- see note 1175996
(*) check the database parameters - see note 1171650
(*) ensure that the CBO statistics are current and correct - e.g.run the RSRV check 'Database Indices of an InfoCube and Its Aggregates' and assure that the table DBSTATC does not contain BW tables with ACTIV=I (note 129252 - report SAP_DBSTATC_CLEANUP)
(*) ensure that all indexes are present and usable - run corresponding RSRV checks
(*) ensure that the f-fact table doesn't have too many partitions - see note 590370
In the transaction RSRT there is the possibility to have the SQL statement and the Execution Plan displayed (execute&debug' -> 'Dispaly SQL/BIA Query'&'Display Run Schedule'; in addition use the function 'no parallel processing'). Even when you are not familiar with the language SQL you can carry out some important checks there.
E.g. in the 'Where Clause' you can check whether the filter restrictions are as expected.
The Execution Plan offers a very helpful feature: please click on the tables in order to get more information to these objects displayed. One important field is called 'last statistics update'. In case the statistics are not up-to-date you can start the update directly from this dialog by clicking on the button 'Analyze' Please check this field for all tables used in the SQL.and then run the query again.(you can also use the refresh button and check whether the Execution Plan has changed).
In case of an SQL statement accessing fact tables of an infocube it might also make sense to test whether the usage of the star transformation (see also note 1247763) makes a difference. Please run the query in RSRT and activate the flag at 'Deactivate DB Optimizer Functions' (then the star transformation isn't used by ORACLE) If the execution time of the SQL statement turns out to be less, please proceed as above and search for propper ORACLE notes.
The following note provides more details to the star transformation and other performance relevant points:
Note 1013912 FAQ: Oracle BW performance
In case a BWA access takes longer than expected please review note 1318214. There you can find a long list of points which should be checked in order to optimize the access of the BWA indexes. Please also note that the BWA is not designed for transferring mass data to the BW system. So please check the field DBTRANS in order to get the relevant information regarding data volumn. Note 1018798.provides more information to this topic
In the follwing we would like to address various factors which have to be taken into account when doing a performance analysis.
A large data volume (transfered from the data manager to OLAP) is often responsible for a non-satisfying query performance. As mentioned in [II.C] please activate the OLAP statistics and take a look at the field DBTRANS. The following points normally contribute to a high number of transfered data records.
(*) many infoobjects drilled down
(*) not visible drilldown characteristics (see also note 1591837) - e.g. due do the usage of exception aggregation
(*) too few filter restrictions
(*)usage of 'calculation before aggregation' - note 1151957
(*)if non cumulative key figures are involved please see note 1548125 point [III.B]
Sometimes a query spends most of the time in the event 3200 OLAP: Data Transfer. In this part of the coding the OLAP Engine (among others) calculates the formulas and carries out exception aggregation (note 1151957). In transaction RSRT (button 'Technical Information') you can e.g. check how many single calculation steps ('formula components') have to be carried out for one data record (see also note 1475193). Please try to reduce the complexity of the query in case of performance problems in this area. In addition: its recommended to search for performance optimization notes for OLAP.
In the QueryDesigner there is the possibilty to activate the access type 'Master Data' ('Access Type for Result Values') for an infoobject. Then the query displays not only the values posted in the infoprovider but all from the corresponding master data table. This can lead to large data volumes and high values for the event '3200 OLAP: Data Transfer'. Its recommended to use this feature only if really necessary.
Please also check whether the feature 'zero suppresssion' (or a condition) is active and whether it removes many records from the result set (this may lead to the false impression that only a few records were processed by the OLAP engine)
Dimensions: In general its better to have more small dimensions as only view very big one. As a rule of thumb a dimension table shouldn't be larger than 10% of the fact tables. The report SAP_INFOCUBE_DESIGNS gives a good overview over all cube tables on the system.
Partitioning (E table): partitioning is used to split the total dataset (fact table)into several, smaller, physically independent and redundancy-free units. This separation improves performance when you analyze data and only view partitions have to be read (e.g. you have defined partions using values of the infoobject 0calmonth (only 0calmoth and 0fisper are allowed ) and a certain query retrieves only data from the current month).
LineItem Dimensions: If you have only one characteristic in a dimension then its possible to define it as a lineitem dimesion. Then the dimension is only a view on the corresponding SID table and hence no DIMIDs have to be generated during loading of transactional data. In the query the SID table can be directly joinded (one join less) with the fact table which leads in genral to a better performance.
High Cardinality: This means that the dimension has a large number of data records. Different index types may be used depending on the data base. A general rule is that the number of dimension entries is at least 20% of the fact table entries. If you are unsure, do not select a dimension having high cardinality.
MultiProvider(Logical/Semantic Partitioning): For InfoCubes with an extremely high number of records in the fact table it can make sense to 'split' the cube into several smaller cubes. The transactional data are then partitioned logically according to an 'LP' characteristic (or even several characteristics). All these 'partcubes' are then used in a MultiProvider. Please see the following notes:
Note 728017 Multiprovider processing - exclusion of part providers
Note 911939 Optimization hint for logical MultiProvider partitioning
As with BW73x you can use the new feature 'Semantic Partitioning' which divides the InfoProvider automatically into several smaller, equally sized units:
http://help.sap.com/saphelp_nw73/helpdata/en/3f/559826fc0b4473942195298899653e/frameset.htm
Hierarchies:to achieve a good performance, hierarchiey structure has to fit into the shared buffer (Exp/Imp buffer) - see Note 156957
Note 738098: Provided no other settings are made, a technical node called "unassigned nodes" is automatically added to hierarchies. All characteristic values that are not used in the hierarchy are attached to this node. If the characteristic has a lot of values, but only a fraction of these are in the hierarchy, this node has a lot of leaves. This may result in memory problems and longer runtimes. Its possible to switch off this feature in RSH1.
Navigation attributes: during query runtime the X,Y tables are joined with the corresponding dimension table; note that navigation attributes can be use in aggregates! Especially when the master data tables are very big please make sure that the data base statistcs are current.
Inventory Cubes and non-cumulative key figures: non-cumulative key figures are virtual, meaning they are calculated during query runtime and usually more records are created then would actually be posted in the infoprovider for a certain selection. See note 1548125 under point [V] for this very special feature.
Compression: compress cubes as far as possible - see note 590370 for an explanation of the technical implications.
DataStore Objects: In case the query is based on a DataStore Object (DSO) its recommended to switch on the DSO setting 'SID Generation upon Activation'. Otherwise the SID values are generated at query runtime which in general reduces the performance. In contrast to InfoCubes, the system does not automatically generate secondary indexes for DSO tables. This has to be done manually and is often required in order to avoid long running queries. Please see
http://help.sap.com/saphelp_nw73/helpdata/en/4a/547d8b6a8a1cd4e10000000a421937/frameset.htm
A query needs more time to finish as expected. It might happen that the runtime is even longer than the time limit for a work process leading to the dump 'time out'.
Other terms
performance, web template, Analyzer, workbook,time-out, BICS, MDX, BWA, BIA, SQL, TIME_OUT, slow
Reason and Prerequisites
This note should help you to carry out a perfomance analysis for a BW query from the OT (OLAP Technology) perspective. This can be done in the transaction RSRT which offers some very helpful features.
In case you have performance problems with specific frontend tools (BEx web templates, BEx Analyzer workbooks or reports created by SAP Business Objects tools such as Webi, Crystal, Dashboard or Analysis - all will be referred to as frontend end tools in this note) you first need to find out which queries are used and whether the embedded queries are slow as well.
If you need a very quick/condensed overview how to proceed please use the executive summary of point [I]. Further below you can find more eloborate instructions under points [II] and [III].
The query performance should initially be analyzed together with your consultant or application developer, please check all the points mentioned in the following guideline. In case the issue has to be processed by the BW Support then always include the results of your analysis like the 'Executive Summary' into the message and expect the support consultant to ask for this analysis.
SolutionIn case you have performance problems with specific frontend tools (BEx web templates, BEx Analyzer workbooks or reports created by SAP Business Objects tools such as Webi, Crystal, Dashboard or Analysis - all will be referred to as frontend end tools in this note) you first need to find out which queries are used and whether the embedded queries are slow as well.
If you need a very quick/condensed overview how to proceed please use the executive summary of point [I]. Further below you can find more eloborate instructions under points [II] and [III].
The query performance should initially be analyzed together with your consultant or application developer, please check all the points mentioned in the following guideline. In case the issue has to be processed by the BW Support then always include the results of your analysis like the 'Executive Summary' into the message and expect the support consultant to ask for this analysis.
CONTENTS
[I] Executive Summary
[II] Detailed Analysis
[II.A] Identify Query
[II.B] Run Query in RSRT
[II.C] Detailed Analysis in RSRT
[II.D] Analysis of SQL Statements
[II.E] BWA Access
[III] Optimizing Query Performance
[III.A] Query Design
[III.B] Data Modeling
- [I] Executive Summary
- 1. Identify the Query- point [II.A]
- a) narrow the frontend problem (BEx web templates, BEx Analyzer workbooks or reports created by SAP Business Objects tools such as Webi, Crystal, Dashboard or Analysis) down to the execution of a single query in RSRT
- b) assure that the same filters and the same navigational state is used in RSRT as in the frontend tools!
- c) analyze how many data records have to be read from the infoproviders and are expected to be displayed for this query
- 2. Analysis in RSRT - point [II.B-C]
- a) use the runtime statistics (execute&debug) to locate the processing area causing the long runtime and the amount of data (DBTRANS) which has to be processed by the OLAP engine
- b) if DBTRANS (tab Aggregation Level)is empty then the OLAP cache was used. For testing purposes its possible to switch off this feature (Execute&Debug->'do not use cache')
- c) check whether the number of transfered data records (field DBTRANS) is as expected. If not please review query definition regarding filters and exception aggregation ( point [III.A] )
- d) Event 9000: if the data base/BWA needs more time than expected please check(update) the data base indexes and statistics and assure that the data base is up-to-date, please proceed as illustrated in points [II.D/E]
- e) if most of the time isn't spent on the DB/BWA, the amount of data records transfered to OLAP and the complexity of the query is crucial - see point [III.A]. If both do not explain the long runtime please carry out a note search on the component BW-BEX-OT-OLAP* with the term 'performance'.
- 3. Check Query Definition - see point [III.A]
- a) in many cases there is no other way than reducing the amount of data and/or the complexity of the query definition. Always consider that the BEX query runtime was designed for on-screen readable reports. In transaction RSRT, the 'Technical Information' will already present yellow or red lights for features impactivng the performance. The following points may be relevant:
- too few filter restrictions, many infoobjects drilled down
- not visible drilldown characteristics as e.g. caused by exception aggregation, constant selection or currency and unit conversions
- access type 'Master Data'
- special key figures of type Non-Cumulative
- big/many Hierarchies (with time dependent structure) - see point [III.B]
- 4. General Performance Recommendations - point [III.B]
- a) keep things small and simple
- b) don't retrieve too much data from the providers ( point [III.A])
- c) keep the data base statistics and indexes up-to-date, check data base parameters - see point [II.D]
- d) use logical partitioning (semantic partitioning, multiprovhint) - see point [III.B]
- e) use physical partitioning (regarding calmonth/fiscper of E table) - see point [III.B]
- f) use aggregates or BWA and OLAP cache
- g) compress cubes - see note 590370
- h) additional indexes on masterdata tables (if many master data records exist, please see note 402469)
- [II] Detailed Analysis
- [II.A] Identify Query
In order to track down the query please run all queries used in the frontend report seperately in RSRT and assure that the same filter selections are used as in the frontend !
If the runtime problem only occurs for the template/workbook the following notes are helpful:
948158 Performance problems/measuring BI Java Web runtime
1083462 Statistics workbook for performance problems
and also
1025307 Composite note for BW 7.00 / BW 7.01 performance
In case its necessary to analyze a performance problem in a support message please attach the Statistics workbook/web file and refer to the event which needs more time as expected.
If the performance issue only occurs in a BO report, figure out what connection type is used to work with BW (MDX, BICS or RSDRI_DF).
If the runtime problem occurs for a query using the MDX interface, work with note 1381821 Memory and Performance Analysis for MDX
- [II.B] Run Query in RSRT
After you have narrowed the problem down to the execution of a single query in RSRT, there are still many influencing factors. Roughly we can devide them into two catagories which refer to the two main OT processing blocks during the query runtime:
OLAP Processor (or Analytic Engine) is the layer responsible for receiving and processing frontend requests. Depending on the navigation, the frontend sends information what characteristics are in the drilldown (rows and columns), what values have been filled into variables and what dynamic filters might have been set. This information is summarized (and merged with the fixed filters) into an OLAP request and handed over to the Data Manager. The Data Manager then retrieves the data from the respective InfoProvider, and hands it back to the OLAP Processor which then performs the calculations, currency translations etc defined in the query. See also note 1591837 where some more information is provided regarding the processing sequence
Data Manager (DM) is the layer responsible for translating the OLAP request for data retrieval from the different InfoProvider-types (e.g. Basis Cube, MultiProviders or Virtual Cubes). It also creates the necessary SQL statements which are finally processed by the data base
The first step is to find out in which area of query processing most of the time is spent. The most convenient way to do this is running the query in RSRT and having the Query Runtime Statistics displayed afterwards (see point [II.C]) It might also happen that the query virtually runs 'forever' and hence doesn't give you the possibility to check the runtime statistics. In such a case please monitor the corresponding process in SM50 and try to figure out where the query 'hangs':
-if you can see a long running SQL then please proceed with point [II.D]
-if the query processes ABAP coding its more difficult to locate the issue. You could start the debugger (SM50->menu->Program/Session->Program->Debugger) and take a look at the call stack. The processing blocks where most of the time is spent, can hopefully give you an idea of the area the performance issue is located. The name of the ABAP objects may also be used for a note search.
- [II.C] Detailed Analysis in RSRT
Please review note 1591837 where you can find more information about this important transaction RSRT (Query Monitor). As already mentioned, try first to get the runtime statistics displayed by clicking on the button 'Execute+Debug' and choosing the setting 'Display Statistics Data'. When you use this function you need to press 'Back(F3)' afterwards in order to get the statistcs displayed. In this note we don't explain all the Statistics Events but only focus on the most important ones. Please see the online help for a complete list:
http://help.sap.com/saphelp_autoid70/helpdata/EN/43/e39fd25ff502d2e10000000a1553f7/frameset.htm
Runtime Statistics
There are two tabs, the Frontend/Calculation Layer and the Aggreagtion Layer (and the tab BWA Access in case an BWA index was read). Pleas see note 1707239 for a detailed description how to use this feature.
Calculation Layer: In the column 'Duration' the runtime for each Event is given in seconds. Watch out for the ones with the highest numbers. The most important BEX OLAP Statistics Events are:
- 3010 OLAP: Query Gen.: This event measures the time that is needed to check the query definition and, if necessary, to generate the query
- 4500 Authorization Check Selection: This event measures the time taken to carry out the OLAP authorization check.
- 9000 Data Manager: This event measures the time in the data manager, you can find more detailed data under the tab Aggregation Layer.
- 9010 Total DBTRANS: Total Number of Transported Records
- 9011 Total DBSEL: Total Number of Read Records
- 3110 OLAP: Data Selection(OLAP_BACK):This event measures e.g. the time taken to sort the read data (from DM) according to structure element selections or restricted key figures. The calculation of virtual key figures is done here as well.
- 3200 OLAP: Data Transfer(RRK_DATA_GET):in this part of the coding many OLAP features are processed, e.g. exception aggregations are carried out, formulas are calculated, and the correct number of decimal places for the data cells is determined.
- 3900 OLAP: Read Texts: Texts are read to sort the data. This event measures the time taken to call the text read class
Aggregation Layer: It gives a list of all SQL/BIA accesses which were executed. In the column 'Viewed at' you can see how long it took, the other interesting columns are:
Records, Selected
Records, Transported
Basic Infoprovider
Aggregate
Table Type
In case the Datamanger is responsible for the long runtime this list allows you to break down the problem to single SQL/BIA statements. The columns 'Records, Selected' and 'Records, Transported' give an idea of which anmount of data had to be processed. Please see point [II.D] for further details. The field 'Aggregate' tells you which aggregate was used, if you see infoprov$X the BWA was used.
In case one of the other processing blocks is responsible for the perfomance issue the following general hints should be helpful:
- check the number of data records (DBTRANS) which were handed over to the OLAP Processor. A high number (e.g. more than 1 million records) has an impact on the events 3110, 3200, 3900.and can lead to a long processing time. Please see point [III.A].
- some of the Statistical Events like the Authorization Check point directly to a application component which you can use for a note search (e.g.use the key word performance on BW-BEX-OT-OLAP-AUT). If the application is not clear, just use BW-BEX-OT-OLAP*, the term 'performance' and specify the implementd support package.
- an important point is the usage of the OLAP Cache. In case the cache is used the query does not call the Data Manager and hence the corresponding statistics event should be empty. If its unclear why the OLAP cache was not taken please review note 822302.
- There is the button 'Performance Info' in RSRT which you can use in order to get a list of many performance relevant factors displayed. Thats a convenient and fast way to check many points in one go.
- [II.D] Analysis of SQL Statements
In this chapter its discussed how SQL statements can be analyzed. As a reference data base we use ORACLE, but most of the points can directly be applied to other databases as well. In some cases the procedures are not exactly the same but similar. Lets start with a to-do-list which often already helps to solve the issue (see also note 1705026):
Please
(*) ensure that the database software is up-do-date- see note 1175996
(*) check the database parameters - see note 1171650
(*) ensure that the CBO statistics are current and correct - e.g.run the RSRV check 'Database Indices of an InfoCube and Its Aggregates' and assure that the table DBSTATC does not contain BW tables with ACTIV=I (note 129252 - report SAP_DBSTATC_CLEANUP)
(*) ensure that all indexes are present and usable - run corresponding RSRV checks
(*) ensure that the f-fact table doesn't have too many partitions - see note 590370
In the transaction RSRT there is the possibility to have the SQL statement and the Execution Plan displayed (execute&debug' -> 'Dispaly SQL/BIA Query'&'Display Run Schedule'; in addition use the function 'no parallel processing'). Even when you are not familiar with the language SQL you can carry out some important checks there.
E.g. in the 'Where Clause' you can check whether the filter restrictions are as expected.
The Execution Plan offers a very helpful feature: please click on the tables in order to get more information to these objects displayed. One important field is called 'last statistics update'. In case the statistics are not up-to-date you can start the update directly from this dialog by clicking on the button 'Analyze' Please check this field for all tables used in the SQL.and then run the query again.(you can also use the refresh button and check whether the Execution Plan has changed).
In case of an SQL statement accessing fact tables of an infocube it might also make sense to test whether the usage of the star transformation (see also note 1247763) makes a difference. Please run the query in RSRT and activate the flag at 'Deactivate DB Optimizer Functions' (then the star transformation isn't used by ORACLE) If the execution time of the SQL statement turns out to be less, please proceed as above and search for propper ORACLE notes.
The following note provides more details to the star transformation and other performance relevant points:
Note 1013912 FAQ: Oracle BW performance
- [II.E] BWA Access
In case a BWA access takes longer than expected please review note 1318214. There you can find a long list of points which should be checked in order to optimize the access of the BWA indexes. Please also note that the BWA is not designed for transferring mass data to the BW system. So please check the field DBTRANS in order to get the relevant information regarding data volumn. Note 1018798.provides more information to this topic
- [III] Optimizing Query Performance
In the follwing we would like to address various factors which have to be taken into account when doing a performance analysis.
- [III.A] Query Design
A large data volume (transfered from the data manager to OLAP) is often responsible for a non-satisfying query performance. As mentioned in [II.C] please activate the OLAP statistics and take a look at the field DBTRANS. The following points normally contribute to a high number of transfered data records.
(*) many infoobjects drilled down
(*) not visible drilldown characteristics (see also note 1591837) - e.g. due do the usage of exception aggregation
(*) too few filter restrictions
(*)usage of 'calculation before aggregation' - note 1151957
(*)if non cumulative key figures are involved please see note 1548125 point [III.B]
Sometimes a query spends most of the time in the event 3200 OLAP: Data Transfer. In this part of the coding the OLAP Engine (among others) calculates the formulas and carries out exception aggregation (note 1151957). In transaction RSRT (button 'Technical Information') you can e.g. check how many single calculation steps ('formula components') have to be carried out for one data record (see also note 1475193). Please try to reduce the complexity of the query in case of performance problems in this area. In addition: its recommended to search for performance optimization notes for OLAP.
In the QueryDesigner there is the possibilty to activate the access type 'Master Data' ('Access Type for Result Values') for an infoobject. Then the query displays not only the values posted in the infoprovider but all from the corresponding master data table. This can lead to large data volumes and high values for the event '3200 OLAP: Data Transfer'. Its recommended to use this feature only if really necessary.
Please also check whether the feature 'zero suppresssion' (or a condition) is active and whether it removes many records from the result set (this may lead to the false impression that only a few records were processed by the OLAP engine)
- [III.B] Data Modeling
Dimensions: In general its better to have more small dimensions as only view very big one. As a rule of thumb a dimension table shouldn't be larger than 10% of the fact tables. The report SAP_INFOCUBE_DESIGNS gives a good overview over all cube tables on the system.
Partitioning (E table): partitioning is used to split the total dataset (fact table)into several, smaller, physically independent and redundancy-free units. This separation improves performance when you analyze data and only view partitions have to be read (e.g. you have defined partions using values of the infoobject 0calmonth (only 0calmoth and 0fisper are allowed ) and a certain query retrieves only data from the current month).
LineItem Dimensions: If you have only one characteristic in a dimension then its possible to define it as a lineitem dimesion. Then the dimension is only a view on the corresponding SID table and hence no DIMIDs have to be generated during loading of transactional data. In the query the SID table can be directly joinded (one join less) with the fact table which leads in genral to a better performance.
High Cardinality: This means that the dimension has a large number of data records. Different index types may be used depending on the data base. A general rule is that the number of dimension entries is at least 20% of the fact table entries. If you are unsure, do not select a dimension having high cardinality.
MultiProvider(Logical/Semantic Partitioning): For InfoCubes with an extremely high number of records in the fact table it can make sense to 'split' the cube into several smaller cubes. The transactional data are then partitioned logically according to an 'LP' characteristic (or even several characteristics). All these 'partcubes' are then used in a MultiProvider. Please see the following notes:
Note 728017 Multiprovider processing - exclusion of part providers
Note 911939 Optimization hint for logical MultiProvider partitioning
As with BW73x you can use the new feature 'Semantic Partitioning' which divides the InfoProvider automatically into several smaller, equally sized units:
http://help.sap.com/saphelp_nw73/helpdata/en/3f/559826fc0b4473942195298899653e/frameset.htm
Hierarchies:to achieve a good performance, hierarchiey structure has to fit into the shared buffer (Exp/Imp buffer) - see Note 156957
Note 738098: Provided no other settings are made, a technical node called "unassigned nodes" is automatically added to hierarchies. All characteristic values that are not used in the hierarchy are attached to this node. If the characteristic has a lot of values, but only a fraction of these are in the hierarchy, this node has a lot of leaves. This may result in memory problems and longer runtimes. Its possible to switch off this feature in RSH1.
Navigation attributes: during query runtime the X,Y tables are joined with the corresponding dimension table; note that navigation attributes can be use in aggregates! Especially when the master data tables are very big please make sure that the data base statistcs are current.
Inventory Cubes and non-cumulative key figures: non-cumulative key figures are virtual, meaning they are calculated during query runtime and usually more records are created then would actually be posted in the infoprovider for a certain selection. See note 1548125 under point [V] for this very special feature.
Compression: compress cubes as far as possible - see note 590370 for an explanation of the technical implications.
DataStore Objects: In case the query is based on a DataStore Object (DSO) its recommended to switch on the DSO setting 'SID Generation upon Activation'. Otherwise the SID values are generated at query runtime which in general reduces the performance. In contrast to InfoCubes, the system does not automatically generate secondary indexes for DSO tables. This has to be done manually and is often required in order to avoid long running queries. Please see
http://help.sap.com/saphelp_nw73/helpdata/en/4a/547d8b6a8a1cd4e10000000a421937/frameset.htm
Header Data
| Release Status: | Released for Customer |
| Released on: | 08.06.2012 11:33:23 |
| Master Language: | English |
| Priority: | Recommendations/additional info |
| Category: | Consulting |
| Primary Component: | BW-BEX-OT OLAP Technology |
| Secondary Components: | BW-BEX-OT-OLAP Analyzing Data |
Affected Releases
Release-Independent
Related Notes
1 comment:
Good Info :)
Post a Comment