Performance Analysis and Optimization for BPC 10
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 8) 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:
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.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = FalseEspecially 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 = TrueUseful 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!
- Set Environment parameter ENABLE_SHARED_OBJECT
- SAP Note 1657612: https://service.sap.com/sap/support/notes/1657612
- Splitting the report:
- Local Members are useful for calculations of aggregations (instead of MDX queries)
- Use and optimize VBA code
- Use conditional formatting
- EPM Logs:
Sascha-
Good work here. What are the sizes of your F and E tables of cube? What did the application server look like from a hardware perspective?
Thanks,
Mark
Hi Mark,
sorry for the late reply. The application server had one cpu with 4 cores and 8 GB memory. I mainly loaded master data and didn't loaded much transactional data. Since it's several months ago I can't provide exact numbers, but the f table had about 100k records just to fill the used reports completely with data. E table should have had the same size because there was nothing to compress (Lite Optimize wasn't useful for this small amount of data).
Hope that helps.
Best regards,
Sascha
Hi Sascha,
Firstly, thanks for great work, here! Especially turning off calculations has a huge huge impact on performance.
Maybe you can help me with this issue:
We are facing very strange system behavior, when turning off EVENTS.
We are 4 developers, 3 of us can run this code and sheet is refreshing:
Public EPM_function As New FPMXLClient.EPMAddInAutomation
sub xx
Application.EnableEvents = False
Application.ScreenUpdating = False
EPM_function.RefreshActiveSheet
end sub
But for one of us sheet is not refreshing when events are turned off.
We run excel from Server so all settings should be the same, can you imagine any reason of this different behavior?
Maybe some user settings or something?
many thanks in advance,
BR Tomas
Hi Tomas,
Thanks for your comment, but I'm afraid I haven't heard about this issue before and can't help you much there. I only can suggest to debug the VBA coding (see MSDN: Trace Code Execution). So maybe you can narrow down the root cause. Furthermore using other functions (standard VBA refresh function?) could also help. Good luck!
Maybe the colleagues from support have better proposals.
Best regards,
Sascha
After 2 issues with different behavior of Excel (VBA - events ) for different users.
We have finally discovered following setting:
Setting that caused issue, in our case:
Proper set up, in our case:
It is essential to have the same setting for developers and for users, when using "enable events".
Excelent content! Thank you very much, very useful
HI,
Use ful info and very nicely explaned. Thank you
Hi Sascha Tom Ulbrich
Well documented....very useful
Few more things can be considered like
1) Dimension formulae,
2) EPM functions - EPMCopyRange, EPMRetriveData,etc
3) Light / full optimization
4) Force Symmetric Refresh for Large Asymmetric Axis option.
Shrikant