Query Performance and Memory Consumption

Introduction/Overview

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 following 'Guided Answer Tree' and four notes

As described in note 1879725, when you start analyzing memory problems, it is a best practice to start the following way:

Detailed Analysis

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 possibility 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 it is also 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:

Processing Blocks 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 Data Sets 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 the 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.

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 handle 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

Topics/Examples

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
• Examples
  1. Yellow Result Lines         Sample Query 1: result set contains 4 million cells, many yellow result lines
  2. Zero Suppression            Sample Query 2: with 2 structures and zero suppression active
  3. Simple Query                   Sample Query 3: simple query with 3.7 million cells in result set
  4. Exception Aggregation    Sample Query 4: with Exception Aggregation
  5. Ncum Key Figures           Sample Query 5: with non-cumulative key figure
  6. Different Hierarchies        Sample Query 6: with Different Hierarchies used on the same characteristic
  7. 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

Bex Analyzer

• 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

Performance Optimization

Query Performance

The main note in this area is

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 characteristics in the drilldown which we call CHARS_DRILL.

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 characteristic 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).

 Link to this page: https://wiki.scn.sap.com/wiki/x/n4JXFQ