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.