Troubleshooting BI 7.0 BEx Analyzer Performance

General Information and Scope

This posting is the second in my BI 7.0 BEx Analyzer Blog Series. Click here to view the blog series. I apologize for the long wait for this installment in the series. I have been busy with a rollout of the BI 7.0 tools company wide. I'll blog about this in a future posting. And now to get on to this posting. This posting contains tips/tools for troubleshooting performance issues with BI 7.0 BEx Analyzer workbooks based on the 7.10 GUI. There is a separate section for each tip/tool, 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/tools also apply the 6.40 GUI.
• This posting only covers Excel 2007, but many of the tips/tools are also valid for previous versions of Excel.
• This posting only covers troubleshooting performance issues with the Excel Analyzer workbook itself. It does not address troubleshooting performance issues of BEx queries or the data models of the underlying infoproviders.

Test Setup for Troubleshooting Tips/Tools:

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

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

Tool: Netweaver Check Workstation Wizard

There are times when performance issues are caused by an issue with the installation of the netweaver frontend software. These types of performance issues are often difficult to uncover. It is always good to begin performance issue troubleshooting by running the Netweaver Installation Check Tool to verify that there are not any installation issues that could end up causing performance issues.

The following explains how to run the Netweaver Check Workstation Wizard:

1. Click the Start Menu >> Then click Run
2. Type in NwCheckWorkstation.exe and click OK
   
3. Click Check on the next screen
   
4. It will take a few minutes to complete the check and you will see a progress bar
5. When the check completes it should say that there are no issues as the screenshot below shows. You can click Finish.
   
6. If there were issues, then these will need to be resolved or the frontend re-installed for the BEx tools to function successfully.

Tool: BEx Installation Check

Once you have verified that the SAP Netweaver frontend software is installed correctly by running the Netweaver Workstation Check Wizard, then you should verify that the BEx tools are installed correctly. You do this by running the BEx Installation Check tool. This will help to identify any installation issues that might be contributing to any performance issues that you might be experiencing. The BEx Installation Check can help to avoid time consuming troubleshooting of a performance issue that is simply related to a BEx installation issue.

The following explains how to run the BEx Installation Check:

1. Click the Start Menu >> Then click Run . . .
2. Enter “C:\Program Files\Common Files\SAP Shared\BW\SAPBExCheck.xla” into the Open input box and then click OK.
   
3. This will bring up an Excel Spreadsheet containing the BEx Installation Check tool. Click the Start button to begin the BEx installation check.
   
4. There should not be any red signed Files found. If there are, then there is an issue with the BEx tools installation and the BI 7.0 BEx tools should be re-installed. Note that you might also want to look at any blue or yellow files, but these generally do not indicate an installation issue.
5. Save the workbook if there are issues, so that you have the results for later reference. These issues will need to be fixed (generally repairing/re-installing BEx).
6. Close Excel

Tool: SAP Note Search

Another tool that I use is the SAP Note Search. You can get to the SAP Note Search tool via the following URL (you will need an SAP Service Marketplace logon):

https://service.sap.com/notes

I typically enter "BW-BEX-ET-WB*" in the Application Area field. The * ensures that the areas underneath this application area are also searched. I then configure the "View / Sorting" to sort by date in descending order, so that the most recent SAP Notes appear first in the results list. I then type the word "performance" as the search term and click the search button. I then scroll through the result list to see if my particular performance issue appears in the list. This can save a lot of time attempting to research a performance issue if there is already a known fix for the issue.

Tool: Shape & Name Count Macro

Office 2007 performance can suffer if there are a lot of shapes or names in the workbook. I have seen degraded performance if either the number of shapes or number of names in the workbook exceeds 250. The most common reason for a large number of shapes in a BEx workbook is due to the use of hierarchies within the BEx results. Each node has an expand/collapse node. If you are experiencing a performance issue with a BEx Excel workbook and want to determine if the number of shapes or names in the workbook is the reason for the slow workbook, you can include the below macro in the workbook and execute the macro. It will give you the total count of shapes and names in the workbook. This performance issue and macro are discussed in SAP Note 1160093.

GetShapeAndNameCount Macro (Include Information between *** lines)

******************************************
Public Sub GetShapeAndNameCount()

Dim count As Long
count = 0
For Each lsheet In ThisWorkbook.Sheets
count = count + lsheet.Shapes.count
Next
MsgBox ThisWorkbook.Names.count & " names" & vbNewLine & count & " shapes"

End Sub
******************************************

Tool: Windows Task Manager

Sometimes the BEx Analyzer is slow because there are not enough resources on the client workstation. The Windows Task Manager is a good tool for checking for available resources and determining what is currently happening from a resource usage standpoint. You access the task manager by pressing the alt, ctrl, and delete keys at the same time and clicking the task manager button. Clicking on the Processes tab will show what processes are using the most resources at the current moment. It may be that an antivirus program is consuming 100% of the CPU and that is the reason that BEx is slow. It can be useful to click the CPU or Mem Usage column headings to sort the processes by greatest to least usage. You will not see an entry specifically for BEx, since BEx runs as an addin to Excel. The BEx resource usage will be reflected in the Excel process as indicated in the following screenshot.

The performance tab can be useful to watch how much additional CPU or memory is consumed while you perform an action in BEx. You can open the task manager and click on the performance tab. Then perform the action in BEx that is causing the performance issue. Then go back to task manager and watch to see if the CPU and/or memory increase and for how long. This can be useful for getting closer to the underlying performance issue or for determining what part of the computer is lacking the necessary power to meet the demands of the BEx application. See the following screenshot for the locations on the performance tab in which to look within the Task Manager tool.

Tool: Statistics Workbook

The built in capability to create a Statistics Workbook in the BI 7.0 BEx Analyzer is probably the biggest time saver for troubleshooting BEx Analyzer workbook performance issues. The Statistics Workbook displays all the statistics events that occurred for the frontend session along with a breakdown of the time spent on each event, the number of times the event was called, and metrics like bytes or blocks transferred. The Statistics Workbook includes BI statistics information like OLAP time and Cells Transferred, but also includes statistics on the rendering of the various design items in the workbook. This is a great performance troubleshooting tool. Below I will demonstrate how to create a Statistics Workbook, discuss some general things to note about the Statistics Workbook, and give a few examples of how Statistics Workbooks have benefitted me in my troubleshooting efforts.

To Create a Statistics Workbook

1. Open the BI 7.0 BEx Analyzer
2. Log on to the backend SAP BW system.
3. Click on the Global Settings icon in the BEx Analyzer toolbar on the Add-Ins tab within Excel
   
4. Verify that the Statistics collection is turned on by checking on the statistics tab. If it is not, turn on statistics collection.
   
5. Perform the actions within the BEx Analyzer for which you want to collect statistics.
6. Click on the Global Settings icon in the BEx Analyzer toolbar on the Add-Ins tab within Excel
   
7. Click on the Statistics Tab and then Click on Display Statistics to create the statistics workbook. Once the workbook is created, click ok to close the dialog window.
   
8. Click on the Filter button in the statistics workbook, right click on the object name characteristic, and choose to drilldown within the rows. This will add extremely useful additional detail to the statistics workbook.
   
9. You should not see the finished results of your statistics workbook.
   
10. Save the statistics workbook to your local computer by using the Excel save option, so that you can refer back to the workbook as a baseline for any workbook changes that you make attempting to improve performance.

General Information on Statistics Workbooks:

• I generally ignore and sometimes filter out the process dialog and wait time events from the statistics workbook, since these generally are not areas that I am troubleshooting performance problems in and they are a large portion of the overall time recorded at times.
• If I suspect a performance issue due to the amount of data being passed, then I generally look at the Bytes Transferred and Number Excel Cells events to compare the amount of data being sent across the network.
• If I suspect a performance issue due to complex analysis authorizations, then I generally look at the authorization related events. This is especially true if user exit variables are used within the analysis authorizations.
• If I suspect a performance issue with the rendering of design items, then I look at the times recorded for the Render Item event and check to see which design item listed in the object name column is consuming the most time.
• SAP Help Documentation has good descriptions of what the various statistics events represent. Here is a link: Statistics Events Descriptions.

Examples Uses of Statistics Workbooks:

• Validate Workbook Compression Setting: I wanted to validate that the number of bytes transferred for the workbook was reduced. I created a workbook without the compression option and created a baseline statistics workbook. I then set the workbook compression in the workbook settings and created a second statistics workbook. I verified that the workbook compression did reduce the bytes transferred for the workbook and slightly reduced the overall processing time.
• Check Impact of Optimized Storage Setting: Frontend Support Package 800 for the BI 7.0 frontend contains a new workbook setting called Optimized Storage to help with performance with the Excel 2007 file format. I wanted to see what the performance impact of this setting was. I created a workbook without this setting set and then created a baseline statistics workbook. I then set the optimized storage setting and then created a second statistics workbook. I found out that the bytes transferred for the workbook increased slightly, but the overall time of the workbook did not change much when opened from the BW server. I also verified that there were no other unexpected side effects to other events. The setting did have a great impact on Excel 2007 workbooks opened from my local computer or a network share.
• Troubleshoot Memory Performance Issue: I ran into an issue with a large amount of memory being consumed when opening some workbooks, so I created a statistics workbook to see how many bytes and rows were being transferred. I noticed the numbers were quite large. I searched for SAP Notes regarding this issue and found Note 1150242. I made the changes mentioned in this Note and created a second statistics workbook. This cut the bytes transferred anywhere from 50% of the original to 10% of the original bytes transferred, which greatly reduced memory consumption and greatly improved the performance of the workbooks.

Tool: RS Trace Tool (RSTT)

SAP provides a trace tool for BEx reporting. That trace tool is the RS Trace Tool or transaction RSTT. This tool allows you to record all the actions from a BEx frontend session along with their timings and gives you the ability to replay the actions. This tool can be very useful identifying what steps within a BEx process are taking the longest. When you find which program is taking the most time, you can also search for SAP Notes for that particular program to see if there are any performance related notes. I have included the steps for recording and viewing an RSTT trace below.

Follow the following steps to record and then view an RSTT trace:

1. Logon to the SAP BW system via the SAP GUI
2. Execute Transaction RSTT
3. Click on the Trace Tool Menu in the left navigation, then click on User Activation, then enter your user id, and then click the " Activate" button. This will activate your user id for RSTT tracing.
   
4. You will notice a row added to the Trace User table. This indicates that the tracing is active for that user.
   
5. Next log on to the BEx Analyzer and perform the actions that you want to trace.
6. When you have completed your actions in the BEx Analyzer, go back to the RSTT transaction and click the " Deactivate" button to deactivate tracing for your user id.
   
7. Click on Traces in the left hand navigation, click on the newest trace, which should be at the top of the trace history list, and then click the Display button. This will display your trace results.
   
8. The results of your trace are now visible. The sequence of programs that were called is listed. The runtime for each step is listed. This can be useful for identifying the biggest contributors to overall time. Note: You can also highlight a row and click the Parameters button to see the values passed to the program for that step to get a better idea of what is going on.
   

Tool: BEx Analyzer Trace File

Sometimes it is useful to look at the BEx Analyzer trace file when you encounter a performance issue in order to better understand what is happening or to view the stack trace for an error that occurred. The following steps detail how to record and view a BEx Analyzer trace file.

The first thing that you need to do is make sure that tracing is enabled with the BEx Analyzer. Verify that tracing is enabled by performing the following steps.

1. Click on the Global Settings icon in the BEx Analyzer toolbar on the Add-Ins tab within Excel
   
2. Click on the Trace tab and then verify that the collect statistics checkbox is checked. If the checkbox is not checked, then check it. Click OK to close the dialog window.
   

Now perform the actions within the BEx Analyzer that you would like to trace. Once you have completed your actions, perform the following steps to view the trace file that was recorded.

1. Click on the Global Settings icon in the BEx Analyzer toolbar on the Add-Ins tab within Excel.
   
2. Click on the Trace tab and then click the Display Statistics button.
   
3. The results of the trace will be displayed in a text file. Note that there is some performance information in the example trace file below. It gives timings for how long the decompression of a compressed workbook takes.
   

Notes:

• You have to close Excel and the BEx Analyzer and reopen the BEx Analyzer in order to record a new separate trace file. Otherwise, the results get appended to the previous trace file.
• Sometimes an error occurs while you are in the BEx Analyzer and you are unable to display the trace file from within the BEx Analyzer, because Excel closes due to an error. In this case you can view the trace file by going to the following location on your computer: "%temp%\bw\analyzer\traces". You can type this location in the Start Menu >> Run . . . and then click OK to get to this folder location. You might want to sort the files by date to see the most recent trace file.

Tool: RRMXP and Application Log

Sometimes you experience performance issues in the opening of the BEx Analyzer workbook and you are not opening the workbook from the BEx Analyzer GUI. This might be the case if you are using the RRMX or RRMXP transactions from within the SAP GUI or if you are executing a link from KM in the SAP Portal that opens the BEx Analyzer workbook. Enabling the recording of application log information during the opening of the BEx Analyzer workbook might give you additional insight into why it is taking awhile for the workbook to open. Below I have included how to enable the recording of the application log information as well as how to view the application log information.

Steps to Enable Application Logging and View the Log Information:

1. Log on to the SAP BW system via the SAP GUI.
2. Click on the System Menu then User Profile, and then Own Data.
   
   
3. Click on the Parameters Tab. Then enter the parameter RSAH_APPL_LOG with a value of X, hit enter, and click the Save icon. This will enable the application logging.
   
   
4. Go to transaction RRMXP. Enter either the workbook or the query (to be opened in the default workbook) that you would like to test. Use the F4 help dialog windows to locate the objects. Then click the green check mark to execute the transaction. This will call up the BEx Analyzer and load the workbook or query that you selected. This may take awhile, since logs are being collected.
   
   
5. Once the workbook has finished loading, go back to the SAP GUI and go to transaction SLG1 in order to view the application log. Enter BW_XLWB for the object and LAUNCH_EXCEL for the subobject and then click the execute button.
   
   
6. This will return a list of application logs to view. Locate the just recorded application log and double-click the entry. This will cause the results of the application log to appear below. View the results of the log to get an idea of the order of events that occur to open the BEx Analyzer workbook.
   
   
7. Make sure you go back to your user profile and blank out the parameter value that you set, so that application logging is again disabled.

Tool: SAP Customer Message

There are times when you will not be able to figure out the cause of a performance issue that you are experiencing or you have identified the performance issue as a bug in the BEx Analyzer and will need to submit a customer message to SAP in order to get SAP technical support to help in resolving the performance issue. The requirements for submitting a BEx Analyzer customer message are detailed in SAP Note 948651. Review that SAP Note before creating the customer message. The following URL will get you to the location to submit the customer message (you will need an SAP Service Marketplace logon):

https://service.sap.com/message

Referenced SAP Notes

Note 1101143 - Collective note: BEx Analyzer performance

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

Note 1083462 - Statistics workbook for performance problems

Note 1013202 - Installation check tool for BI 7.x Standalone frontend

Note 1229206 - Front end check tool SAPBExC.xla (BW 3.x and BI 7.x)

Note 899572 - Trace tool: Analyzing BEx, OLAP and planning processes

Note 1152929 - Log enhancements for RRMX

Note 1091188 - Trace file in NetWeaver 7.0 BEx Analyzer

Note 948651 - Customer Message requirements for BEx Analyzer

Note 1141798 - Analysis of RRMX/RSAH_Launch_Excel problems

Note 1150242 - Improving performance/memory in the BEx Analyzer

Mark Zuchowski is a BI Frontend Architect working at General Mills, Inc. He has over 5 years experience with Enterprise Data Warehouse Architecture, but his main area of interest is Enterprise Reporting, Query, and Analysis. He is experienced with the BEx Analyzer, BI-Java, and Business Objects reporting tools. He is currently working on the implementation of the architecture, security, processes, and procedures related to the new BI tools.