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 saved

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.

Tip Doctor, Insider Learning Network.

The following tip is taken from the BusinessObjects Expert article “Interested in Dashboard Design? A Methodical Approach to Creating an Xcelsius Dashboard” by Sandesh Darne, published in May 2011.

The success of any dashboard design depends on understanding what business executives and managers expect from it for measurement and monitoring of business elements. Often, requirements are articulated as follows: “I would like to see a competitive sales comparison dashboard” or “I need to know the demand in the last quarter. What is the quarterly growth in sales? What is the product market share?”

The challenge is to transform these requirements into dashboard functionalities that provide answers in an at-a-glance screen. More elaboration on requirements provides a better description of the dashboard. You can achieve this by:

• Understanding the executive’s mental model of a dashboard 
• Carrying out a detailed analysis of current business reporting systems and gaps
• Studying the manual information analysis process

Carry out the requirements to a level that describes dashboard functionality in terms of necessary business alerts, comparisons of facts, drilldowns of figures based on specific measures, and filters on business element categories. Find out how users would interact with the dashboard.

With these guidelines, you can prepare the dashboard design sheet with a list of all the functionalities, possible visuals involved, and the user interactions that operate those functionalities. By reading the guidelines, executives should get a better picture of the functions of the end product. Figure 1 contains an example dashboard design sheet that lists the functionality of a competitive comparison dashboard.

 
Figure 1: Sample dashboard description

Sandesh's complete process for designing dashboards can be found in his original article at BusinessObjects Expert.

The Tip Doctor, Insider Learning Network.

Ned Falk, Senior Education Consultant, SAP America, shows how your SAP NetWeaver BW projects can benefit using ABAP in his article “20 Uses for ABAP on SAP NetWeaver BW Projects,” which was posted to the SAP Professional Journal Web site in February 2010. Here are four of those uses.

1) Perform Open Hub transformations:

SAP NetWeaver BW supplies the Open Hub service to meet requirements for getting information to third-party applications from SAP NetWeaver BW. The data types, field names, and field lengths are often not the same in SAP NetWeaver BW as they are in the external application, or additional value manipulation is necessary. For this, an ABAPer needs to code the custom logic for the manipulation in a Business Add-In (BAdI). BAdIs, based on ABAP OO, help you accomplish custom enhancements in a controlled way.

2) Build authorization values:

ABAP implemented through variables filled by a user exit can be used to dynamically build authorization values when the user logs in. This is sometimes an easy way to maintain many allowed values for a cost center range for a specific user.

3) Process chains:

The process chain is a scheduling tool for various tasks in the data warehouse. In some cases, you can use ABAP code to schedule a job on the source system or run a small ABAP program on SAP ERP to raise an event on SAP NetWeaver BW. This event could then be used to start a process chain. A business case for this could involve a transaction or ABAP code run on SAP ERP to indicate that the Financial Accounting (FI) system is done with the month-close process. This ABAP program calls SAP NetWeaver BW and raises an event. When this event is raised, the process chain called “FI Monthly Process” starts.

4) Virtual InfoCube with services:

Virtual InfoCubes allow for the direct read of source data in real time. You do not have to load the data into an InfoCube — it is loaded into memory directly from the source when the report is run using standard SAP NetWeaver BW query and reporting tools. The three types of virtual InfoCubes are SAP remote InfoCubes, general remote InfoCubes, and virtual InfoCubes with services. SAP remote InfoCubes use SAP ERP or SAP CRM DataSources (and their associated extractors) to read the data in real time, while general remote InfoCubes access data that is normally purchased (e.g., Nielson). Virtual InfoCubes with services are used if the source is a custom table or group of tables. In this case, custom ABAP function modules (services) can be written to properly present the data from these tables to the SAP NetWeaver BW query tools, making your table look like an SAP NetWeaver BW InfoCube. This provides real-time data, not replicated data, from your application to the user.