This page should help you to carry out a performance or memory consumption analysis for a BW query.It should be used in combination with the 4 notes
- Best practise Guided answer BW BEx performance analysis linked in KBA: 2455142
- 1879725 BW Query related Memory Dumps like TSV_TNEW_OCCURS_NO_ROLL_MEMORY
- 1681396 Query Performance
- 1967896 Basic recipe for troubleshooting memory problems with Bex queries
- 1968598 Mass data in Bex queries: Options and Workarounds
As described in note 1879725, when you start analyzing memory problems, it is a best practice to start the following way:
First Important Steps when Analyzing Memory Issues
- Check whether the issue can be replicated in RSRT. This simplifies the analysis (complexity of the frontend is excluded) and gives you the possibility to use all the useful features of transaction RSRT
- Reduce the data volume (e.g. in steps by 10%) by setting filter values up to the point where the dump no longer occurs.
- Once the query can be executed successfully you can use the function 'Display Statistics data' in RSRT. This should give you already an impression of what might cause the high memory consumption. This downsized query can be used for all further checks. The memory consumption of the original query can be estimated by multiplying the memory consumption of the downsized query by the corresponding factor.
- A BW query uses extended and heap memory. Check how many GB memory can at most be allocated: Memory Parameters.
We focus on the part of the query runtime which is processed in the so called OLAP Processor (plus DataManager). In case you face an issue when using a specific frontend tool then please proceed as described in the notes 1681396 and 1879725 to get the affected query (plus navigation state). The perfect tool for doing such an analysis is the transaction RSRT which offers some very helpful features (note 1591837) as the possibilty to have the OLAP runtime statistics displayed. This statistics data is essential for a performance/memory analysis, in this context the most important statistc events/numbers are (see also notes 1681396 and 1707239, OLAP: Query Runtime Statistics and SAP Online Documentation):
Another important number is the number of cells displayed in the query. We call it FE_CELLS (it is basically the number of rows times the number of columns (key figures) in the query result). When you want to estimate the memory consumption of a query its als important to calculate the size of an internal table which is also used by the OLAP cache, it is called 'Memory Table SP' . The following rough overview of the BW Architecture should help to get an idea what all these events and numbers mean:
Conditions&Zero Suppression: all 'Operations on the Result Set' are processed here
Data Transfer: many OLAP features are processed here, e.g. exception aggregations are carried out and formulas are calculated
Data Selection: data is sorted according to structure element selections or restricted key figures, virtual key figures are calculated, currency conversion is carried out
DM Data Set: data set transferred from data base to the Data Manger
SP Data Set: internal table which is used to interact (read/write) with the OLAP cache; number of records is SP_REC
FE Data Set: data set transferred from the OLAP engine to the 'frontend'
Query Result Set: final query result
The processing block 'Condition&Zero Suppression' (please see Operations on the Result Set) depends on the frontend used. E.g. if the query is executed with th BEx Analyzer the processing is done in ABAP on the application server (as in RSRT). If the query is run in the BEx Java runtime, in some cases the processing is done on the Java server (see also BEx Web 7.X Safety Belt and Zero Suppression.
Estimation of Memory Consumption
As described in note 1879725, in many cases it makes sense to reduce for testing purposes the data volume by setting additional filters (in order to be able to make use of the OLAP runtime statistics, see point 3.1).
The estimation of the memory consumption of a query is in general very complex. Hence it is necessary to work with a very simplified model. It should help to get an idea of the magnitude and of what causes the unexpected high memory consumption. In the following we assume that the memory consumption of the OLAP engine is approximately the sum of what the internal table SP and the table TAB_OLAP_TRANS allocate (these are normally the two most important contributions when the query is 'big'). In many cases, this corresponds roughly to what the query needs when executed in RSRT with 'Query Display' = HTML . In RSRT under 'Technical Information' you can find the following parameters which we need for estimating the memory consumption:
- Columns in Memory Table SP ( C_SP )
- Value Field Groups ( VF_G )
Furthermore we need the number of charcteristics in the drilldown which we call CHARS_DRILL.
OLAP Memory Consumption in Bytes = 'Memory Consumtion of table SP' plus 'Memory Consumption of table T_OLAP_TRANS' =
( VF_G *24 + C_SP *8 + CHARS_DRILL *7) * DBTRANS + 96 * OLAP_FE_TRANS
If the query uses the OLAP cache you can use SP_REC instead of DBTRANS . This estimation only refers to the memory consumption of the OLAP engine (see picture above) on the backend/application server. It is only a lower limit and e.g. it does not take into account
- Processing steps after the OLAP Engine
- the texts of charcteristic values and attributes
- processsing of the feature 'zero suppression'
- generation of cells with NULL
- display of final result set
- Within OLAP Engine
- yellow result lines in SP
- internal table which defines the drilldown
The point when the OLAP Engine transferres the internal table T_OLAP_TRANS to the 'frontend' is important since from now on, the processing and memory consumption depends on the frontend used (or if RSRT is used). Our estimation formula refers to this point and there is also a default breakpoint in RSRT where you can you stop in the debugger and check the memory consumption of the query at this stage (please see How to check current Memory Consumption) .
Remarks to the Frontends
- If the query is executed in the Bex Analyzer it may happen that the total memory consumption on the backend is up to a factor of 3 bigger than the 'OLAP Memory Consumption'. The Analyzer (.net framework) has (only) to be able to handle the final number of cells of the query result, hence FE_CELLS .
- If the query is executed in the Java web runtime the memory allocated on the backend is approximately equal to 'OLAP Memory Consumption'. Bex Java web reports carry out the 'Operations on the Result Set' in the Java layer and hence the internal OLAP_FE_TRANS is passed on to the Java runtime. Hence the number OLAP_FE_TRANS is relevant when you try to estimate the memory consumption on the Java server.
In case the memory resources are sufficient on the backend to handle the query, it still may happen that the result set is too big for the frontend:
- Bex Analyzer : see notes 1860872 , 1040454 (.NET framework can hanlde approximately 750,000 data cells) and SCN Wiki Optimizing BEx Analyzer 7x Performance
- Bex Web Reports : see notes 1515139 and 1913710
- AO : SCN Wiki Performance in AO
- Design Studio: 1931691 Performance hints for Design Studio/Lumira applications
BW on HANA
If the database is HANA, OLAP features like e.g. exception aggregation and currency conversion can be 'pushed down' to the HANA database. This normally decreases the amount of data significantly which has to be transferred from the database to the OLAP Engine and hence may improve the performance of a query. See note 2400004 'Checking Pushdown of Exception Aggregations'
Performance relevant Design/Modeling Recommendations:
- 2118286 COPR/HCPR/ISET: High Query Memory Consumption
- 2271658 Design Considerations for Composite Provider
- 2103032 Long runtime for query on CompositeProvider
- 2267702 HCPR & Navigation Attributes
- 2185212 ADSO: Recommendations and restrictions regarding reporting
The following examples with screenshots should help you to better understand the proceeding described in the page and note 1879725.
- Memory Parameters
- How to check current Memory Consumption
- Yellow Result Lines Sample Query 1: result set contains 4 million cells, many yellow result lines
- Zero Suppression Sample Query 2: with 2 structures and zero suppression active
- Simple Query Sample Query 3: simple query with 3.7 million cells in result set
- Exception Aggregation Sample Query 4: with Exception Aggregation
- Ncum Key Figures Sample Query 5: with non-cumulative key figure
- Different Hierarchies Sample Query 6: with Different Hierarchies used on the same characteritic
- Input ready query built on aggregation level (or plan data query) only supports FEMS0, thus FEMSn data is read with convex hull and later filtered in OLAP BACK (WRITE)
- Information for Support
SAP Consulting Notes
- 1681396 Query Performance
- 1879725 Query Memory Consumption
- 2069001 BICS: Global switch for result set operations
- 2415346 Troubleshooting Query Performance/Memory issues in BW
- 1860872 Report not executable in BEx Analyzer 7x / Client out of Memory
- 1040454 Front-end memory requirement of the BEx Analyzer
- 1906900 Don't design huge reports in BW system
BEx Web Reports
- 1515139 Frequent WEB AS JAVA out of memory crashes and/or core dumps due to large BI data access queries
- 1913710 BI Java Memory Improvements for NW 7.30
AO Reports and WAN Scenarios
- 1733726 - Performance optimization in WAN scenarios with BICS
SAP Online Documentation
The main note in this area is
1681396 Query Performance
Link to this page: https://wiki.scn.sap.com/wiki/x/n4JXFQ