If any one of you is experiencing any of the following symptoms while accessing SAP BW via the MDX interface such as –
– Long running SQL statements
– Strange looking SQL statements (‘Where used lists’)
– Filters not applied
– Too many dimensions requested
– Memory dumps- Time outs
Then one need to analyze the memory and performance problems occurring while accessing SAP BW via the MDX interface.
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
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
SAP Coding optimization: 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.
Some of the factors that impact performance and memory requirements in the OLAP Processing Layers
– 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 (Statistics: event 3100 ‘OLAP: Read Data’)
– 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)
– Complexity of the BEX queries definition (1 vs. 2 structures, cell definitions, and 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’)
– 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 row set (BW statistics: event 40012 ‘ MDX Axes’)