Welcome!
By Felipe Lamounier, state of Minas Gerais, Brazil – powered by 🙂My Easy B.I.
📑 Index:
Symptom
Other Terms
Reason and Prerequisites
Solution
A Introduction
A1 Are the system parameters set correctly?
A2 Did you apply already available SAP coding optimizations?
A3 Is too much data requested by your MDX statement?
B MDX – OLAP Processing Layers
B1 Data manager
B2 OLAP Processor (Analytic engine)
B3 MDX processor
C Common issues and solutions
SAP Business Objects WebIntelligence (WebI): Query stripping
SAP Business Objects WebIntelligence: Filters on green detail objects (MDX properties)
SAP Business Objects WebIntelligence: reports without a key figure/measure
All frontend tools: ‘Use of structure elements’ (KIDSEL flag)
All frontend tools: Member rowset/ MDX axis construction
All frontend tools: Cardinality and DSO
All frontend tools: BEx 7.0 query default filters are not applied
All frontend tools: Calculated measures and members
All frontend tools: 1 million cells restriction
All frontend tools: Conditions, Zero suppression and other Limitations
D Helpful transactions and analysis tools
MDXTEST
RSRT: Technical Information
RSTT: Play a trace
RSRT & MDXTEST & RSTT: Execute&Debug flags relevant for performance analysis
BW statistics: most important events
Bonus
🔭 See also:
Symptom:
You want to analyze memory and performance problems occuring while accessing SAP BW via the MDX interface and need some information.
You are experiencing any of the following symptoms:
- Long running SQL statements
- Strange looking SQL statements (‘Where used lists’)
- Filters not applied
- Too many dimensions requested
- Memory dumps
- Time outs
In SAP Business Objects WebIntelligence (WebI):
MDDataSetBW.GetCellData. No more storage space available for extending an internal table.. (WIS 10901)
Other Terms:
memory, performance, EXPORT_TOO_MUCH_DATA, MESSAGE_TYPE_X, STORAGE_PARAMETERS_WRONG_SET, STACK_STATE_NO_ROLL_MEMORY, SYSTEM_NO_ROLL, TSV_TNEW_BLOCKS_NO_ROLL_MEMORY, TSV_TNEW_OCCURS_NO_ROLL_MEMORY, TSV_TABH_POOL_NO_ROLL_MEMORY, TSV_TNEW_PAGE_ALLOC_FAILED
Reason and Prerequisites:
This note is giving an introduction in section A, an overview of MDX OLAP processing in section B and common problems and solutions in section C. Section D contains some analysis transactions.
Solution:
A Introduction:
Memory dumps indicate that too much data is requested for the current system parameter settings. Many performance problems are also caused by this. The following questions should be asked:
A1 Are the system parameters set correctly?
When experiencing general memory dumps and performance problems with MDX and BEx queries, a system parameter review is recommended.
For memory problems: check for SAP notes on component BC-CST-MM (sample note 146289).
For database problems: check for SAP notes on the components BC-DB* depending on your used database. Check if database statistics are up to date and indices are ok for cube and master data tables (sample note 1013912 FAQ note for Oracle and BW).
A2 Did you apply already available SAP coding optimizations?
The processing of MDX statements is constantly optimized by SAP development. Always check if the SAP notes listed in the collective performance note 1142664 are already applied to your system.
A3 Is too much data requested by your MDX statement?
MDX statements do request certain navigation states and are not always identical with the initial navigation state of the BEx query. There is no easy formula to calculate the memory requirement of a certain statement. For a better understanding the section B will explain some of the processing steps, highlight coding bits that would be visible in the call stack of ST22 dumps or while checking SM50 for long running statements to understand different factors impacting the amount of data requested. Additionally, some key indicators of your query design are mentioned. Section C is a collection of typical problems surfacing and will be updated like an FAQ list. Section D will highlight some helpful transactions and tools to find information described in Section B and C.
B MDX – OLAP Processing Layers
The MDX processor composes some requests to the analytic engine (OLAP processor) which then creates the requests to the data manager according to the query definition. The data manager then composes the SQL statements specific for the used provider and database vendor. After the data is returned from the database it is merged and enriched with the BEx query specific calculations. This result is transferred to the MDX processor to handle this data according to the MDX standard and the used functions.
3 steps are important, for each step indicators for large structures and memory intensive points are mentioned that should be considered in the design process as well as code indicators for relating the short dump information in memory dumps or SM50 (Programs starting with GP* are so called generated programs and will have a different name after GP in your system).
B1 Data manager:
Task:
Data has to be retrieved from the database, for that several sub queries are run against the part providers and have to be sorted into internal containers which should provide data for later calculations.
Factors that impact performance and memory requirements:
- Number of free characteristics and key figures (RSRT -Technical Information)
- Number of OLAP containers: you find in the RSRT Technical Info the field ‘columns in the memory SP’ to be very big, then the query definition is quite complex. Some samples for that is the usage of 2 structures with many elements, exception aggregation or non-cumulative key figures. This will enlarge the object with the raw data retrieved from the database a lot. (RSRT Technical Information)
- Number of data records requested from the database (BW statistics: event 3100 ‘OLAP: Read Data’)
Coding indicators/typical error call stack:
FORM SELECT_4 GP4GLA2NI8YTYGGCZRAIJ0H6ICW
FORM SELECT_2 GP4GLA2NI8YTYGGCZRAIJ0H6ICW
FORM SELECT_1 GP4GLA2NI8YTYGGCZRAIJ0H6ICW
FORM SELECT_0 GP4GLA2NI8YTYGGCZRAIJ0H6ICW
FORM OLAP_BACK SAPSRRK0
FORM FILL_SP_1 SAPSRRK0
B2 OLAP Processor (Analytic engine)
Task:
Once the raw data is returned from the database (or cache, BWA, aggregates respectively), hierarchy and result row aggregations have to be made. The more hierarchies are active or the more different hierarchy levels or infoobjects are requested, the more objects have to be kept in memory. On top of these aggregated objects BEx query calculations can be performed.
Factors that impact performance and memory requirements:
- Number of structure elements requested (RSRT -Technical Information)
- Number of characteristics requested in the drilldown (MDX statement: what characteristics are actively used within the MDX statement in CROSSJOIN(), DESCENDANTS() or other expressions)
- Hierarchy and hierarchy levels requested
- Complexity of the BEX query definition ( 1 vs. 2 structures, cell definitions, exception aggregations# – yellow lights in RSRT – Technical Information highlight potential performance problems).
- Number of results cells requested by the BEx structures (BW statistics: event 3200 ‘OLAP: Data Transfer’)
Coding indicators/typical error call stack:
FORM SX_TO_BDATA SAPLRRK0
FORM SETXX_FUELLEN_02 SAPLRRK0
FORM SETXX_FUELLEN_0 SAPLRRK0
FORM SETXX_FUELLEN SAPLRRK0
FUNCTION RRK_DATA_GET SAPLRRK0
B3 MDX processor:
Task:
This OLAP query result has to be translated by the MDX interface according to the MDX standard and the requesting MDX statement, special functions like calculated members need to be calculated, some special sorting and determination of the axis and cell data is necessary and has to be enhanced by requested properties (e.g MEMBER_CAPTION, attributes etc). The larger the dataset determined in the 2 steps before and the more properties get requested by the MDX the more likely a memory dump or a long runtime will occur while doing these enhancements.
Factors that impact performance and memory requirements:
- Complexity of the MDX functions used in the statements
- Calculated members and sets
- Number of dimensions requested in a CROSSJOIN
- Number of properties (texts, keys and attributes) requested
- Number of records in the MDX member rowset (BW statistics: event 40012 ‘ MDX Axes’)
Coding indicators/typical error call stack:
CREATE_TUPLES_BY_REQUEST
CALCULATE_MEMBER_2
CL_RSR_MDX_AXIS=>GET_DATA (e_t_mndtry_prptys, e_t_opt_prptys_vals)
CL_RSDM_READ_MASTER_DATA======CP
CL_RSDM_READ_MASTER_DATA=>_FILL_TXT_N
CL_RSR_MDX_CROSSJOIN_MULTI=>EMPTY
CL_RSR_MDX_CROSSJOIN_MULIT=>NON_EMPTY
C Common issues and solutions:
The following section describes some settings that impact the amount of data requested as well as certain performance intensive settings.
SAP Business Objects WebIntelligence (WebI): Query stripping:
For Web Intelligence a common problem is that too many dimensions are requested from the MDX interface. Usually, you will see MDX statements containing all dimensions (characteristics) used in the Webi query even so they are not displayed in the Webi report in CROSSJOIN expressions in the MDX statement. This will lead to memory dumps in all 3 processing layers.
Up to BOE XI3.1 SP3 the BOE server would request the data for the whole microcube of the WebI query and thus request all dimensions used in the WebI query from BW at once. Query stripping now gives you the opportunity to signal that WebI should only request the displayed dimensions. More details can be found here:
http://wiki.sdn.sap.com/wiki/display/BOBJ/Optimize+query+with+Query+Stripping+in+Web+Intelligence
SAP Business Objects WebIntelligence: Filters on green detail objects (MDX properties):
The green detail objects are in standard generated out of texts and display attributes in Bex queries (technical expressions are for example MEMBER_CAPTION, MEMBER_NAME, [2<INFOOBJECT>], [2<ATTRIBUT>]). In the BEx query designer no filters can be set to these objects and the processing layers mentioned above cannot handle filters on these objects. In WebI you can set filters on the green objects. For the processing of these filters, BO has to take a detour and request a list of the master data (display attributes and texts) via the BAPI_MDPROVIDER_GET_MEMBERS. The list returned to BO is then generated into an MDX statement which will then contain a long list of single values. If you experience long runtimes with such a definition, turn it into a navigation attribute and define the filter on the blue dimension belonging to this navigation attribute. This filter can then be translated directly into the MDX statement.
SAP Business Objects WebIntelligence: reports without a key figure/measure:
The MDX standard knows 2 modes: EMPTY and NON EMPTY. Look into the MDX statement if it contains the NON EMPTY statement, otherwise it is in EMPTY mode. NON EMPTY means only the posted values in the dimension tables are requested. The EMPTY mode leads to reading the master data tables and building a Cartesian product. This is in most cases not intended and you should include at least one measure in your WebI report.
All frontend tools: ‘Use of structure elements’ (KIDSEL flag):
There is documentation on this flag in help.sap.com: http://help.sap.com/saphelp_nw70/helpdata/en/57/b10022e849774f9961aa179e8763b6/content.htm
Bex query structures can be very complex and multiply quickly in case 2 structures are used. In the BEx standard all elements of a structure are processed. MDX statements often only ask for only a few of the structure elements. Setting flag ‘Use of structure elements’ in RSRT in the query properties signals the OLAP processor that only the requested structure elements are to be processed. This flag impacts especially the amount of data that needs to be processed in the steps ‘Data Manager’ and ‘OLAP Processor’. (Caution: if your query uses the feature ‘constant selection’, the KIDSEL flag cannot be evaluated – see note 944815).
All frontend tools: Member rowset/ MDX axis construction:
You experience long runtimes on SID and dimension tables that you do not find when comparing with the processing of your BEx query. Problems occur mostly in the MDX processor layer.
Note 1530858 contains a description of this phenomenon and some hints to overcome this. If many properties (texts and attributes) and navigational attributes are used in the MDX statement, consider creating secondary indices as described in note 402469
All frontend tools: Cardinality and DSO:
The MDX standard requests to provide the cardinality of used dimensions to the frontend tools. For multidimensional part providers like InfoCubes, the number of records in the dimension tables can be counted. For tabular providers like DSO or master data tables, no dimension tables exist, so the whole table (A-table can be very large) needs to be scanned. You will find SQL statements looking like ‘Where used’ lists with ‘Count’ expressions in SM50 or in the SQL trace.
Find more detailed information and consider the RSADMIN parameters mentioned in the notes: 971164 and 1224318.
All frontend tools: BEx 7.0 query default filters are not applied:
In the Bex query designer 7.0, characteristic restrictions are defined on the left hand side. On the right hand side initial filter values (so called default values) are defined. These default values will be ignored by the MDX interface. Move all filters (especially authorization and customer exit variables) needed to the left hand side into the fix characteristic restrictions.
All frontend tools: Calculated measures and members:
Using calculated members, calculated measures or set expressions in your MDX statement leads to additional calculations being performed in the MDX processor layer. Check very carefully, how much data records are requested that these calculated members need to be calculated for.
For SAP Business Objects universes: Check the requests that are modeled into the universe dimensions with own MDX expressions. Functions like YTD(), PERIODTODATE() etc often require more data to be read from the database. Sometimes it is useful to model certain calculations already in the BEx query.
All frontend tools: 1 million cells restriction:
You can encounter the 1 million cells limit at 3 different points:
1) Requesting more than 1 million cells in the result set (e. g. for 10 columns and 100.000 data records this limit would be reached)
2) Requesting more than 1 million tuples in the axis row set (e.g. a statement in EMPTY mode requesting dimension customer (50.000) and the time dimension month (24 month) would hit the limit for the number of rows even without retrieving the cell data)
3) Requesting more than a million records of a master data table via the BAPI_MDPROVIDER_GET_MEMBERS (requesting a list of document numbers from a line-item dimension without sending a filter should be avoided)
With note 1232751 new interface functions are provided, that disable the 1 million cell limit for the result set in point 1. Only when these functions are available in the BW system (as of SAPKW70103) and only if the frontend tool (e.g. SAP BusinessObjects 3.1 Integration Kit greater then Fixpack 1.5) calls these functions the 1 million cells limit is gone. But always consider that the MDX interface has been designed for reporting purposes and not mass data extraction.
All frontend tools: Conditions, Zero suppression and other Limitations:
Certain BEx query features are ignored by the MDX interface. Check the general notes for restrictions: 820925 and 323779. Disable all unsupported features before comparing a BEx query result with an MDX statement.
D Helpful transactions and analysis tools
MDXTEST:
See note 1272044 for an introduction to this test transaction.
RSRT: Technical Information:
Get an understanding of the complexity of your BEx query. Yellow and red lights indicate features that can potentially cause performance problems already on BEX query level. Determine the number of free characteristics, key figures, structure elements etc used in your query. Pay attention to the number of ‘columns in the SP’ (this describes the internal container structure in the OLAP processor) and check the impact of removing features like exception aggregation, constant selection on this number. The higher this number, the higher are the memory requirements for the same amount of data records requested. More details for this transaction can be found in the online help: http://help.sap.com/saphelp_nw70ehp1/helpdata/en/a0/2a183d30805c59e10000000a114084/frameset.htm
RSTT: Play a trace:
In some cases purely playing the MDX statement in MDXTEST does not replay the steps the frontend tool has performed to retrieve the relevant metadata. Record an RSTT trace to see all steps performed. More details for this transaction can be found in the online help: http://help.sap.com/saphelp_nw70ehp1/helpdata/en/43/5f3c23c7656b7de10000000a422035/frameset.htm
RSRT & MDXTEST & RSTT: Execute&Debug flags relevant for performance analysis:
In the test transactions RSRT (via button ‘Execute&Debug’), MDXTEST (via menu entry MDX Command ‘ Set Debug flags) and RSTT (Trace collection –> Option ‘With Debug’) certain analysis functions can be used. These are the most important flags for analyzing performance problems in the MDX environment:
- Category ‘Others’ provides the very important option ‘Display statistics’ and to create on the fly BW statistics for this query or MDX statement to see more clearly where the problems are.
- Category ‘Aggs’ provides options to check if and which aggregates are used.
- Category ‘BIA server’ provides options to check your statement with or without BWA.
- Category ‘Data Manager’ provides options to display the SQL statements of your query, to check processing of your multiprovider.
- Category ‘Others’ provides the options to run query or statement without using the OLAP cache.
BW statistics: most important events:
Displaying the statistics will help to identify the number of records to be transferred in the different layers. Most important events are for checking the layers mentioned above:
- event 3100 ‘OLAP: Read Data’
- event 3200 ‘OLAP: Data Transfer’
- event 40012 ‘ MDX Axes’
More information on the BW statistics events can be found in the online help: http://help.sap.com/saphelp_nw70ehp1/helpdata/en/43/e39fd25ff502d2e10000000a1553f7/frameset.htm
Bonus:
Query Performance and Memory Consumption: https://wiki.scn.sap.com/wiki/display/BI/Query+Performance+and+Memory+Consumption
SAP BW How to check current Memory Consumption: https://wiki.scn.sap.com/wiki/display/BI/How+to+check+current+Memory+Consumption
✅ Conclusion:
Keywords: What is the difference between Memory Dumps TSV_TNEW_PAGE_ALLOC_FAILED and SYSTEM_NO_ROLL; TSV_TNEW_PAGE_ALLOC_FAILED vs SYSTEM_NO_ROLL;
Did you like the content? Want to get more tips? Subscribe ↗ for free!
Follow on social media:
🙂My Easy B.I. 
3 comentários em “🏇 [SAP BW] Memory and Performance Analysis for MDX (SAP Note 1381821)”