Additional Blogs by SAP
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Performance Analysis and Optimization for BPC 10

Former Member
‎07-09-2013 7:58 AM
In this blog post I want to share some experiences I made in the context of report performance analysis and optimization for BPC 10.
First of all I had to figure out which techniques to use for performance measurements. There are several ways to measure and analyze response times like BPC internal tools or external tools like Fiddler which I want to address briefly in this blog post. Furthermore, I want to give some information about techniques like splitting up a report which for sure is a bit complicated but offers a great potential. Finally, I will sum up the best practices and I will try to give you an orientation where you can gain quick wins in performance topics.
The measurements were performed on a laptop with an Intel Core i5 processer, 8 GB memory and Microsoft Excel 2010 with the SAP EPM-Addin (SP12 patch 😎 and an Application Server with SAP BPC 10 (CPMBPC 800 SP 09).

How to measure

To get sufficient information about runtimes you have to distinguish between the Application Server and the (Excel-)Client with the EPM-Addin. In order to measure comparable values I defined the metrics shown in Figure 1.
Figure 1: Metrics
For the client times I decided to use the EPM Performance Trace (perfTrace.glf) with log level DEBUG because the level of detail is adjustable and I got all information I need. This information is visualized in Figure 3. Be careful with a higher log level because it will have a negative impact on performance and could lead to false conclusions for performance optimization. For further information regarding the EPM Performance Trace see http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-In+Logging. If you are familiar with Fiddler you can use it to measure the runtimes of the queries as well.
Figure 2: Screenshot of EPM Performance Trace
Figure 3: Information from EPM Performance Trace
For the server times I used the transaction UJSTAT which is part of SAP BPC. To activate these statistics you have to set the model parameter “BPC_STATISTICS” to ON via using the transaction code “UJ0_IMG_03” for the model you want to have statistics for. See Figure 5 for a visualization of information I used from these statistics. The Shared Query Engine (SQE) is the engine used to process the queries and decides whether to execute a SQL query or a MDX query.
Figure 4: Screenshot of the statistics report retrieved via transaction UJSTAT
Figure 5: Information from the statistics report

The calculations of the metrics are defined as follows:
  • Client preparation     = Start time of first query – start time of refresh process
  • Client rendering        = Duration of refresh – client preparation – duration of queries
  • Server preparation    = Query Engine C Runtime – Query Engine T Runtime
  • DB query                  = Query Engine T Runtime
  • Server processing     = SQE T Runtime – Query Engine C Runtime
  • Data transfer            = Duration of refresh – SQE T Runtime
Note that the metric DB query includes the creation of the query (MDX or SQL).
To get meaningful results I executed the refresh for each measurement ten times and calculated the arithmetic average.
Figure 6: Example visualization of a server measurement

Optimizing the server time

At the beginning I experienced constantly long runtimes (several seconds) for the server preparation time. Cube optimizations didn’t help to solve this problem but activating a caching mechanisms for hierarchies by setting the environment parameter ENABLE_SHARED_OBJECT according to note 1657612 (https://service.sap.com/sap/support/notes/1657612) led to much better performance.
During the performance optimization I tried also to split the report into MDX and RSDRI (SQL) parts to measure the impact on performance, especially the server time. See http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academy -> “Butterfly Report” for an example of a report with shared axis and Figure 7 for a visualization of a simple example with a lot of data. When splitting up the report you have to consider that RSDRI queries are only used when only base level members (no aggregations) are retrieved. Note that the “% Reached” column is a local member to calculate the percentage actually reached from the plan value. For Local Members see http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academy -> Local Members
Figure 7: MDX and RSDRI(SQL) parts of a report

This example report can be built in multiple ways. The simplest way is using one report causing one huge MDX query. An alternative way is to use Local Members to calculate the totals for Europe because all children of Europe are displayed below anyway. This would enable you to divide the report into one report with 2011- and 2012-Totals (MDX) and one huge report with the monthly data (only base level members -> SQL query) as shown in Figure 8. Note that all “Productgroup” rows have to be Local Members.
Figure 8: Splitting up the report

Based on this splitting I also implemented the 2012-Total columns as Local Members because these data are calculated with the monthly data display in the other columns. This reduces the MDX query further.

Optimizing the client time

While analyzing the client time it showed that formatting can have a huge impact on performance. Formatting is done via a separate formatting sheet where the formatting rules are defined. In general you can do formatting based on hierarchy levels or based on the member and its properties. Additionally you can use the excel functionality “conditional formatting”. To use this functionality in the formatting sheet see http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academy -> Conditional formatting. In my example I achieved the best performance by using only hierarchy level formatting in combination with conditional formatting.
Figure 9: Screenshot of the EPM Formatting Sheet

During the analysis of the client time I also experienced long runtimes caused by VBA coding. After some tests it showed that the EPM Add-in notices changes done via VBA coding and tries to interpret them. The simplest solution was to avoid this by deactivating the events while the execution of VBA coding. Furthermore I deactivated screen updating and automatic calculations during code execution to improve performance:




Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Key findings

After a lot of measurements and some optimizations I analyzed the results and summarized the conclusions in the following key findings. In my case I could reduce the overall runtime of a report by 80%.

Usage of buffer
A correct sizing of the buffer and the usage of the environment parameter ENABLE_SHARED_OBJECT is very import (more than factor 10 for server preparation time), especially when using a huge amount of master data records. The parameter enables some caching mechanisms. See note 1657612 (https://service.sap.com/sap/support/notes/1657612) for further information and guidance to activate the parameter.

MDX and RSDRI query
As already recommended for BPC 7.5 splitting the report into MDX and RSDRI(SQL) queries has an positive impact on performance. By using one MDX query (as small as possible) and one RSDRI query (as large as possible) the best performance improvements were achieved. RSDRI queries are much faster, but can only be used for base level members (without aggregation). For the examples mentioned above the best performance was achieved with a split into two reports and Europe rows and 2012-Total columns as Local Members. The DB query time was reduced from about 20 seconds to 2.3 seconds while the client rendering time doubled from about 1 second to nearly 2 seconds.
Figure 10: Measurements of split reports

Local Member
The usage of Local Members is not very expensive and can be used to calculate the aggregates (to achieve a RSDRI query).

Formatting
The formatting has a huge impact on performance as well. Simply turning off the formatting is the easiest way to estimate the impact. The excel functionality “conditional formatting” (see http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academy -> Conditional formatting) and the formatting based on member properties (functionality of the EPM formatting sheet) has a moderate impact on performance. The functionality “Dimension Member Formatting” (by ticking off the second box of the formatting sheet) should only be used with SP13 Patch 3 or higher and the impact on performance has to be evaluated carefully.
Figure 11: Measurements of client rendering times

VBA Coding
The usage of VBA coding can lead to very poor performance. The code itself has to be optimized and standard technics like deactivating automatic calculations and screen updating during execution of the VBA coding have to be applied:





Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Especially the command “EnableEvents = False” has a huge impact. If the events are not turned off, the EPM Add-in will recognize the changes in the report and will try to process them. Turning the events off is a performance improvement for the VBA runtime by factor 100. Don’t forget to reactivate the disabled features at the end of your coding:




Application.Calculation = xlCalculationAutomatic

ActiveSheet.UsedRange.Columns.EntireColumn.AutoFit

Application.EnableEvents = True

Application.ScreenUpdating = True

Useful resources to apply optimizations

Hopefully you got some useful information to analyze and improve your BPC. Here are some links which helped me a lot and hopefully will do so for you. I already mentioned some of them in the text.  I’m looking forward to your feedback!
8 Comments
Popular Blog Posts
Top kudoed authors
View all