Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Introduction/Overview

This page should help you to carry out a perfomance or memory consumption analysis for a BW query.It should be used in combination with the 4 notes

  • 1879725   Query Memory Consumption
  • 1681396   Query Performance
  • 1967896   Basic recipe for troubleshooting memory problems with Bex queries
  • 1968598   Mass data in Bex queries: Options and Workarounds

Best practise performance analysis has been summarized in the Guided answer (decision tree) linked in KBA: 2455142 

As described in note 1879725 , when you start analyzing memory problems it is bets 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.

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 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  and SAP Online Documentation):

Statistics Data for Query Runtime

column Duration

  • 9000 Data Manager : Basically time needed on data base
  • 3110 OLAP Data Selection :This event measures e.g. the time taken to sort the read data according to structure element selections or restricted key figures.
  • 3200 OLAP Data Transfer : In this part of the coding many OLAP features are processed, e.g. exception aggregations are carried out and formulas are calculated.
  • 3100 OLAP: Read Data : This event measures the time that is needed to group together the data requests or to read the OLAP cache.

column Counter

  • 9010 Total DBTRANS : Total Number of Transported Records to the OLAP engine
  • 3200 OLAP Data Transfer : Number of data cells which are transferred from the OLAP engine to the frontend, we call this number OLAP_FE_TRANS (and the internal table TAB_OLAP_TRANS)
  • 3100 OLAP: Read Data : The number of records of the cache entry (and number of records of SP ), we call it SP_REC

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:

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

General Remarks

  • 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 and 1040454 (.NET framework can hanlde approximately 750,000 data cells)
  • Bex Web Reports : see notes 1515139 and 1913710

Please follow the step-by-step instructions of note 1879725. The following examples with screenshots should help you to better understand the proceeding described in the page and note 1879725.

In case the issue has to be processed by the BW Support then please always include the results of your analysis and fill out the form Information for Suppport

Topics/Examples

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

1681396   Query Performance

 

 

 

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

 

 

  • No labels