Skip to Content
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.

image

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.

image

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.

Referenced OSS Notes

Note 893348 – Hardware and Software requirement for NW2004s in SAPGUI 6.40

Note 1013201 – Hardware and Software requirement for BI Standalone frontend

Note 1013139 – Pre-requisite for BI 7.x Frontend

Note 1148328 – NW 7.0 BI add-on front end Support Package 18 & 7.X SP 600

Note 1148329 – NW7.0 BI Add-On front-end Support Package 1801 & 7.X SP 601

Note 1101143 – Collective note: BEx Analyzer performance

Note 1160093 – Prog error/performance problems when using large workbooks

Note 1150242 – Improving performance/memory in the BEx Analyzer

Note 1179647 – Performance: Network load in BEx Analyzer

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

    1. Mark Zuchowski Post author
      You must have the BI 7.0 Statistics infoproviders installed/activated in order to be able to display the statistics workbook. See OSS Note 965386 for details on activating the technical content. OSS Note 934848 also contains a lot of good information on issues to avoid and performance optimizations of the BI Statistics technical content.

      If you have the BI 7.0 Statistics technical content configured properly in your backend sytem, then it may be the way that you are attempting to access the statistics workbook. Per OSS Note 1083462 you need to set the collect statistics checkbox and then exit out of BEx Analyzer. Then when you restart BEx Analyzer the statistics will be collected and you should be able to click the Display Statistics button.

      I have not tested creating a statistics workbook with the 6.40 GUI, but I have created statistics workbooks using the 7.10 GUI with patches 400 through 601.

      Note: I will be covering the creation/usage/interpretation of a BEx Analyzer statistics workbook in my next blog on the BI 7.0 BEx Analyzer.

      (0) 
  1. Krishna Kishore Chivukula
    Hello Mark,
    Its a nice blog with detailed level of all patch levels. In the Note 1150242( Improving performance/memory in the BEx Analyzer ) its mentioning ” You use the BEx Analyzer 2004s with a patch level of at least 1800″, Does this mean BW back end patch level SAP_BW to 18 ? or some other else ?

    Coming to performance with BI7-Bex and MS office 2007, options like Unchecking Apply formatting option and unchecking display of hierarchy icons option(users can still expand hier using – context menu – expand to level –> )on TABLE GRID ITEM in design mode will improve performance to an extent.Pause automatic refresh, is another performance improvement technicque delivered in BI7-Bex. Finally  its better to use the Excel functions like Vlook ups etc which are delivered in 2007 excel rather than using same old formulae techniques in 2003 Excel.

    Regards
      KK

    (0) 
    1. Mark Zuchowski Post author
      When Note 1150242 mentions a patch level of at least 1800 for BEx Analyzer 2004s, this is talking about a frontend patch level and not the backend patch level.  Specifically, this is talking about frontend patch (FEP) 1800 for the 6.40 GUI.  This is equivalent to frontend patch 600 for the 7.10 GUI.

      OSS Note 1150242 also has an advanced correction that must be applied to your backend BW system if your backend BW system is below SPS16 (ABAP SP18).  You must apply the backend corrections in addition to having the appropriate frontend patch level and setting the RS_FRONTEND_INIT parameter ANA_USE_SIDGRID to a value of X.

      (0) 
  2. Hello Mark,

    very nice blog! I was desperatly looking for something like that!

    Just a question: any experience with .Net Framework 3.0?

    Thanks,
    Rodolphe

    (0) 
  3. Veerabhadra Rao Pinnamaneni
    Thanks for excellent blog. I wanted to implement one of your suggestion by applying OSS Note 1179647 for enhancing the Net Work Performance, but unfortunately unable to locate the OSS note in the system or in the OSS notes Portal. If you have a copy can you please mail it to me (pvrao@sbcglobal.net) or give me the link so that I can access the OSS note.  
    (0) 
  4. Dries Paesmans
    Hi Marc,

    thanks for the update on performance. We also have significant performance issues. After a lot of testing, we found that it is related not to the number of records selected, but the number of records displayed in the query. The more records displayed, the longer it takes (almost lineair relationship). Wen hiding the data with a filter, we experienced a huge increase in performance.

    -> do you know whether OSS note 1150242 solves this or did you have similar experiences. Reason why we did not implement note 1150242 yet is, because it requires patch 6 for GUI 7.1. Each new SAP GUI patch is tested by us thorougly and patch 6 (in combination with vista) gave huge problems …

    regards

    Dries

    (0) 

Leave a Reply