Optimizing BI 7.0 BEx Analyzer Performance

Former Member · ‎08-04-2008

General Information and Scope

This posting contains tips for optimizing the performance of BI 7.0 Analyzer workbooks based on the 7.10 GUI. There is a separate section for each tip, so that you can easily skip to the tips that pertain to your environment.

The following covers the scope of this blog posting:

This posting only covers the BI 7.0 BEx Analyzer based on the 7.10 GUI, but many of the tips also apply the 6.40 GUI.
This posting only covers Excel 2007, but many of the tips are also valid for previous versions of Excel.
This posting only covers optimizing performance of the Excel Analyzer workbook itself. It does not address optimizing performance of BEx queries or the data models of the underlying infoproviders.

Test Setup for Performance Results:

BI 7.0 BEx Analyzer based on the 7.10 GUI with patch 601 installed
Backend BI 7.0 System at SPS15 (ABAP SP17)
2GHz Intel CPU (single core)
2GB RAM

Here are the tips. I hope that some prove useful.

Ensure you Meet the Minimum CPU Requirements and Have Enough Memory

OSS Note 893348 details the minimum CPU and memory requirements for the SAP GUI and the BI 7.0 BEx Analyzer. We did extensive testing of various CPU and memory configurations and found that the CPU that you had did not matter very much as long as it was above the minimum requirement. However, we found that the memory on machine had a big impact on performance. For example, a navigation that took 30 seconds on a machine with 512MB took only 10 seconds on a machine with 1GB of memory. Therefore, make sure that your end users have enough memory on their machines when using the new BI 7.0 BEx Analyzer. We determined that 1GB of memory was the minimum needed by our users.

We are using the following memory configurations for our end users:

General Users - Minimum of 1 GB
Power Users - Minimum of 1.5 GB
Developers - Minimum of 2 GB

Ensure .Net Framework 2.0 SP1 and Available Hotfixes are Installed

Our end users had the .Net Framework 2.0 installed on their computers as part of our corporate image. However, neither support pack 1 nor any hotfixes to the .Net Framework were part of our corporate image. We noticed degrading performance through the day due to memory leaks and noticed that the BEx Analyzer memory consumption was very high. We tracked these issues down to fixes for the .Net Framework that were included in SP1 and additional hotfixes for the Microsoft .Net Framework. After installing these, our memory leaks disappeared and the amount of memory that BEx Analyzer consumed reduced by 50MB - 90MB per open workbook. Both OSS Note 1013201 and 1013139 reference needing to update the .Net Framework with the latest patches, but I was not aware of the big performance gains that you get by doing this. Make sure the support pack and hotfixes are installed on your end users' computers.

Use BI 7.10 GUI Patch Version 600 or Greater

During the development of our workbooks we have gone through a number of frontend patch versions. We started with patch 400 and had numerous performance issues. We continued to experience these issues until we applied patch version 600. Patch 600 contained a number of memory, cpu, and network optimizations that improve performance considerably. I discuss some of these optimizations in more detail below. We are currently using patch version 601. I highly recommend using patch 600 or higher in any production environment. OSS Note 1148328 details frontend patch 600 and OSS Note 1148329 details frontend patch 601.

Implement OSS Note 1150242 to Improve Performance and Reduce Memory Consumption

We noticed issues with high memory and CPU usage when the results were being rendered in the results grid within our BEx Analyzer workbooks. OSS Note 1150242 provided a fix to this issue. This particular performance improvement was pretty impressive. We had some workbooks that were taking 2 minutes to render the results and they were improved to 15 seconds. We had other workbooks that were taking 10 seconds to render the results and these improved to 3 seconds. This fix has made the BI 7.0 BEx Analyzer a much more usable tool. Note that this fix requires that you are on at least frontend patch 600 and that you set the parameter ANA_USE_SIDGRID to a value of X in the BW backend system via transaction RS_FRONTEND_INIT. We could not go live in production with the tools without this fix in place.

Implement OSS Note 1179647 to Improve Network Performance

We noticed issues with the network performance related to BEx Analyzer workbooks. The network issues were apparent when the workbook was first opened or when there was a large resultset. There were a large number of bytes that had to be transferred across the network and this data transfer was taking a long time. OSS Note 1179647 provided a fix to this issue. This fix reduced the amount of data that needed to be transferred and greatly improved the time it took to transfer the workbook and data from the backend BW system to the client computer. We had workbooks that took 15 seconds to download to the client that only took 5 seconds after the fix was in place. This fix was especially beneficial for our remote offices, because network bottlenecks are always more pronounced for them. Note that this fix requires that you are on at least frontend patch 600 and that you set the parameter ANA_USE_TABLE to a value of X in the BW backend system via transaction RS_FRONTEND_INIT. This is a fix that I definitely recommend putting in place. It has really improved things for us.

Consider Using Workbook Compression

It is possible to compress a workbook in the new BI 7.0. This reduces the size of the workbook. While this does not directly make the workbook faster, it does improve the time that it takes to download the workbook from the BW server down to the client computer, since fewer bytes need to be transferred. The best use cases for this are workbooks with large resultsets, workbooks that contain lots of metadata, or offline workbooks. Note that you need the J# runtime to both compress a workbook and to open the compressed workbook. OSS Note 1101143 mentions this. The J# runtime is an additional requirement for the BI 7.0 frontend that is not listed in either OSS Note 893348 or OSS Note 1013201.

Repair Workbooks with Degrading Performance

We noticed issues where a workbook was slower and slower over time. We determined that the underlying issue was that there were numerous unused shape objects in the workbook that were a result of a hierarchy. These shapes were not cleaned up correctly and just collected over time, which was slowing down the workbook more and more. OSS Note 1160093 addresses this issue. The solution is to activate the repair option for the workbook when it is opened and then save the repaired workbook. This fixed our issues with degrading performance of workbooks over time.

Review the Central OSS Note on BEx Analyzer Performance

There are a number of factors that can affect BEx Analyzer Performance that I did not cover in this blog posting. Please, review the central OSS Note on BEx Analyzer performance for further tips on optimizing the performance of BEx Analyzer workbooks. This OSS Note is 1101143.