Tips on troubleshooting performance bottlenecks in BPC: Managing MDX statements
This tip is from Chris Houlder’s presentation at the upcoming BPC Bootcamp in Singapore next week, May 22-24, “Building and Troubleshooting a Scalable, High-Performing BPC Landscape.”
This session includes checklist of performance bottlenecks on BPC, including he usual offenders -- active concurrent users in the system, hardware limitations, size of master data, and data volume in cubes -- plus overly complex areas such as:
Complexity of hierarchies
- Levels within a hierarchy
- The more the levels, the longer it will take to roll up as rollups are done on the fly
- Number of alternate hierarchy:
- Common example: Entity dimension, where businesses would require one grouping for Legal Reporting and the others for Segmental Reporting
Complexity of report or input form design
- Reports expanding on multiple data region sets across columns (time and category) at parent level
- Input templates with embedded macros (expand, send, refresh, and expand again)
Complexity of default logic for write-back
- Running script logic by default would trigger logic each time data was being sa
ved
Complexity of any script logic and the formula for calculations
- Nested logics performing complex business computations
- Dimension formulas
- MDX
Included in the how-tos in Chris Houlder's session is the following tip on reducing aggregates and troubleshooting MDX statements and settings.
Troubleshooting Performance Bottlenecks: Review MDX & Aggregates
- T-code UJSTAT, here you will be able to find the generated MDX statement used when executing a statement from the BPC frontend
- Look for multiple top hierarchy node (PARENTH1) selections
- Selecting a top hierarchy node will select all members that are grouped within
- Thus, selecting on multiple top hierarchies for differing dimensions will select a vast quantity of data (which in turn will be aggregated on the OLAP)
Debugging the Generated MDX Statement
- The SAP NetWeaver version of BPC supports aggregation (aggregates), however this is only useful if:
- There are no open requests
- The aggregate is rolled up
- For input schedules this is not a solution, but for reporting it could be used
- Using T-code MDXTEST and setting the “Debug Flags” as shown:

Identifying Aggregates
- Compare the requested aggregates found and build aggregates based on these selections
- In our example, runtime came down from 120 to 5 seconds
For more information on next week's BPC Bootcamp event in Singapore, May 22-24, including speakers and session details, visit the seminar website.
For additional tips and resources year-round on BPC, visit the SAP BusinessObjects Group here on Insider Learning Network, or post your questions on our Forum page.