Child pages
  • Optimizing BEx Analyzer 7x Performance
Skip to end of metadata
Go to start of metadata
This page contains information about performance analysis and performance optimization in BEx Analyzer 7x.

 

Overview:

 

Paragraph
Description

1 - Initial Analysis

Find out if performance issue is only Frontend related and if your workbook is correctly designed.
2 - Check performance on a client with latest Frontend patchConfirm that your Frontend is up to date and that the Frontend installation is correct.

3 - Create the Statistics Workbook
4 - Evaluate the Statistics Workbook

With a Statistics Workbook you identify the time-consuming processes/events.
It is the prerequisite for any performance optimization. 

5 - Optimizations for time-consuming Frontend Statistics Events
5.1 - Optimizing event 'Load Workbook'
5.2 - Optimizing event 'Render Item'
5.3 - Optimizing event 'Not Assigned' / Complex Workbooks
5.4 - Switch Design/Analysis mode

Find detail information about performance optimizations for Frontend-related statistic events.

6 - Further Optimizations for Workbooks
6.1 - Optimizing Refreshing Time
6.2 - Using Excel 2010

Find further performance optimizations for BEx Analyzer workbooks. 
7 - Your AnalysisIn case issue remains after applying the optimizations then please answer our checklist questions.

 

 

Please read this first:

  • Please go through the tasks (points 1-4) and optimizations (points 5 & 6). If you have identified an optimization that matches your scenario please apply it.
  • In case you cannot find an optimization or an applied optimization does not match your expectation please answer the questions mentioned at the bottom of the page
  • Please ensure to answer all the question before returning the incident back to SAP.
  • Please also review following consulting note. It contains useful information which is also mentioned on this page but in different structure:
    1958613 Optimizing the BEx Analyzer Performance - Known Corrections and Best Practice Guidelines

 

1 - Initial Analysis

You are using the BEx Analyzer and you feel a not satisfying performance after opening a query or a workbook.
BE SURE the issue is exclusive with BEx Analyzer. 
For local workbooks, make sure the customer is using XLSM or XLS format. Other formats are not supported and will not work properly 
SAP Note 1498919  - Known Limitations of BExAnalyzer with Excel 2010
Test the query on transaction RSRT before proceeding on this wiki. Any performance issue on RSRT, this wiki will not help you.

1.1 - In case performance issue happens while opening of a query:

  1. First of all, you should execute the query with same selection in the backend and/or JAVA WEB to compare the performance. With the result of this quick test you can identify if there is a general query performance issue or if the performance issue is only BEx Analyzer related. If you observe a general query performance issue stop further reading this page. Collect all queries manually or use FM RRMX_WORKBOOK_QUERIES_GET
  2. Check if performance issue remains using the SAP Default Workbook. Please note that a default workbook (template) is always used in case of opening a query. If your own template is wrongly designed, then it can lead to performance problems. Please perform following steps: 
    1. Restart the BEx Analyzer, open the BEx Analyzer Global Setting and switch to TAB "Default Workbook". Check the value of "Current Default Workbook":
    2. If you see "SAP Default Workbook" then you are using the SAP Standard template. No further checks need to be performed.
    3. If you see a specific Workbook ID then press button "Use SAP Standard" to switch to SAP Default Workbook. Now check again the query performance using the "SAP Default Workbook".
  3. With the steps above you have confirmed that query performance issue is only BEx Analyzer related and is reproducible using the SAP Default Workbook. Please go on with point 2.

1.2 - In case performance issue happens while opening of a workbook:

  1. Identify all DataProviders and the assigned queries/query-views of the workbook with following steps:
    1. Start the BEx Analyzer and open the workbook.
    2. Open the Workbook Settings > switch to TAB "Data Providers".
  2. In case the workbook contains one DataProvider:
    1. Check the direct execution of the query which is used as DataProvider in the workbook. Please perform the steps mentioned under point 1.1. above.
  3. In case the workbook contains several DataProviders:  
    1. Go through all DataProviders and confirm that each DataProvider is used in the workbook. If you find an existing DataProvider which is not used in the workbook then delete it. With following steps you can perform this check:
      1. In Workbook Settings switch to TAB "Design Items". Here you can find all in workbook existing BEx Items.
      2. Go through all BEx Items and check the assigned DataProvider.
      3. The deletion of a not used DataProvider can be done in the Workbook Settings on TAB DataProviders.
    2. Make sure that your complex workbook is not based on the SAP Default Workbook. Please note the SAP Default Workbook is designed/optimized for only one DataProvider/sheet. 
      1. Regarding this please review wikipage Creation of 7x Workbooks with or without SAP Default Workbook.
      2. Please note that a workbook with several DataProviders/sheets which is based on SAP Default Workbook is not supported!
    3. For each in workbook used query check the direct execution as explained under point 1.1. above.
  4. With the steps above you have confirmed that the workbooks performance issue is only BEx Analyzer related, the query runtime outside BEx Analyzer is fine and in case of a complex workbook no SAP Default Workbook is used.

2 - Check performance on a client with latest Frontend patch

For analyzing the performance issue you should use a BEx client which fulfills following requirements:

  RequirementAdditional information
1Latest Frontend patch is installedReview wikipage: Current BI ADDON for SAP GUI 760 to check it. For updating review wikipage Complete Installation SAP GUI and BEx Frontend.
2Frontend Installation Check result is greenRun the installation check as per note 1229206 to make sure that the Frontend installation is fine. For performing the check use the SAPBExC.xla file which is attached to note 1229206. To understand the result please review wikipage BEx Front-End Installation Check.
3Valid 'SAP SE' certificate is installedCheck the certificates with information provided on wikipage Certificates in BEx Analyzer and Workbooks. Check the start of BEx Analyzer using Tx RRMX and via Windows Start Menu. The application should be started immediately without a long delay.

 

3 - Create the Statistics Workbook

  • The steps before have confirmed that there is a performance issue which only occurs in BEx Analyzer using latest Frontend patch.
  • Before trying to improve BEx Analyzer performance, you need to know the processes for which the system is using up time. For this purpose, a Statistics Workbook needs to be created.
  • Following you find the steps how to create Statistics Workbook. For more details about that please review wikipage The Statistics Workbook
    1. Open the BEx Analyzer 7x via Windows Start Menu.
    2. In the BEx Analyzer Global Settings dialog go to tab Statistic and activate the checkbox Collect Statistics.
    3. Close the BEx Analyzer and restart it.
    4. Execute the sequence of steps which create the performance problem.
    5. Stop recording in the statistics dialog by removing the indicator. Choose "Display Statistic" to generate a Statistics Workbook.
    6. Save the Statistics Workbook.

4 - Evaluate the Statistics Workbook 

  • With steps above you have created a Statistics Workbook.
  • Identify the most time-consuming Events with a look at column 'Validity Period' which provides the event runtime in seconds.
  • Create an own TOP N list with the most time consuming 'Events'.
  • The wikipage The Statistics Workbook, you can find the statistics events explained in detail.
  • If you identify Frontend-related events as bottleneck please go on with paragraph 5 - Optimizations at this page to improve the BEx Analyzer performance.
  • If you identify OLAP-related events stop your Frontend analysis and try to optimize the OLAP events first. This part is not in focus of this wikipage.

5 - Optimizations for time-consuming Frontend Statistics Events: 

  • The recorded Statistics Workbook identifies time-consuming Frontend-related Events.
  • Please go through following Event-specific optimizations an apply the relevant optimization(s):

5.1 - Optimizing event 'Load Workbook'

5.1.1 - Workbook Compression

The Workbook Compression reduces the size of the workbook. It improves downloading time of workbooks from BW server to BEx client. It should be switched on for workbooks with large result sets and/or workbooks which contain a lot of metadata. You can use this functionality ...

  • workbook specific: Set the flag 'Use Compression When Saving Workbook' in the Workbook Settings on TAB general.
  • globally: Set RS_FRONTEND_INIT parameter ANA_USE_COMPRESSION = X (note 1662215).
5.1.2- Local Workbook Caching

Please note that this feature is not applicable for BW700.
You have a large workbook and observe that the initial download time of the workbook is very slow. With 'Front-End side Workbook Caching' you can improve next loading time.

Set RS_FRONTEND_INIT parameter ANA_CACHE_WORKBOOK = X (note 1392745).

How does it work?

  • In BEx Analyzer 7x you open a workbook from the backend server.
  • This transfers a copy of the workbook into temp folder C:\Users\<user>\AppData\Local\Temp\BW\Analyzer\Workbooks on the client PC.
  • If parameter ANA_CACHE_WORKBOOK is not set, then this file is deleted automatically after closing the BEx Analyzer 7x.
  • But if you have set the parameter ANA_CACHE_WORKBOOK then the file is not deleted, the copy still exists.
  • Now if you open the same workbook again then the timestamp of the 'workbook in backend' is checked.
    • If timestamp on the server is newer (the workbook on the server was changed), then the workbook is loaded again from backend and replaces the local copy. In this case, you will not recognize a faster loading time.
    • If timestamp on the server is identically (the workbook on the server was not changed), then the local copy is loaded. This should be faster.

Limitation:

  • Workbook caching does not work system specific. Only workbook description, workbook ID and timestamp are taken into account. So it can happen that a refresh of a cached/newer workbook (from system A)  to another system (system B) leads to problems.
5.1.3 - Optimized Storage

Workbooks in new Excel file format should use the Optimized Storage. You can use this functionality ...

  • workbook specific: Set the flag 'Use Optimized Storage' in the Workbook Settings on TAB general.
  • globally: Set RS_FRONTEND_INIT parameter ANA_USE_OPTIMIZE_STG = X (note 1260213).

Starting with FEP500 the Optimized Storage is used as the default for new workbooks, the parameter ANA_USE_OPTIMIZE_STG has no influence anymore. Already existing workbooks needs to be resaved with Excel2007 and FEP>=500 (note 1581104).

5.2 - Optimizing events 'Render Item' and 'Before Render'

5.2.1 - Repair Workbook at Execution

With using the test macro from note 1160093 you can identify how many shapes and names are used in the workbook. To remove all unused shapes and names please run the workbook in Repair Mode. Use following steps:

  • Start the BEx Analyzer,
  • in the Global Settings set the flag Repair Workbook During Execution.
  • Now open the affected workbook and
  • resave it.
5.2.2 - Suppress Hierarchy Shapes

You observed rendering consumes a lot of time, in the workbook a large query result as a hierarchy is displayed. According to note 1286653 you can improve the performance by suppressing the display of hierarchy shapes. To use this functionality open the 'Properties of Analysis Grid' dialog and change 'Display Hierarchy Icons' to '+/-'.

Info: You can set RS_FRONTEND_INIT parameter ANA_CTX_SENS_DBL_CLK with value "X" to enable context-sensitive double click functionality. Please review note 1345468.

Switch Off 'Apply Formatting'

For further reducing of Excel rendering/formatting time you can unselect the flag 'Apply Formatting' in the properties of the Grid-Item (Online Documentation: Analysis Grid).

5.2.3 - Reduce the Number of Styles

Sometimes the workbook contains a very high number of styles. This should be checked. There are Excel built-in styles, BEx styles, and custom styles. If you find custom styles which are not used then they should be deleted. Please execute following code in VB Editor (Alt+F11) to check the number of styles:

VBA code to count names, shapes, and styles:

Public Sub GetShapeAndNameCount()
  Dim count As Long  
  count = 0  
  For Each lsheet In ThisWorkbook.Sheets   
    count = count + lsheet.Shapes.count  
  Next  
  MsgBox "Names: " & ThisWorkbook.Names.count & vbNewLine & "Shapes: " & count & vbNewLine & "Styles: " & ThisWorkbook.Styles.count
End Sub

 

If the number of styles inside the workbook is very high (hundreds or thousands) and most of them are unnecessary you can delete the styles which you do not require. Furthermore, you need to update Excel in case Excel 2007 version is in use. Regarding this please review note  2039134 which explains this style issue more detailed.

  • If you would like to see all the existing styles and their format in one worksheet which can allow you to better identify the different styles and if you really need them, you can use the attached VBA macro List All Styles.txt.
  • If you are sure that the workbook does not contain user-defined styles you can use the attached VBA macro Delete Unused Styles.txt to remove all styles except 'Excel built in' styles and 'SAP delivered' styles. 
  • If your workbook contains user-defined styles you have to adjust macro Delete Unused Styles.txt, enlarging the list of styles to be skipped.
  • Attention! Take a backup of the original workbook before removing styles.

5.2.4 - Reduce the Number of Shapes

Please execute the code above to get the number of shapes.
To delete the not used shapes please use following steps:

  • Open workbook.
  • Navigate: Page Layout -> Selection Pane -> Show All (to see the shapes in the active sheet).
  • Open the VB Editor and run following code in 'ThisWorkbook'.
  • Save the workbook.


VBA code to delete no used shapes:

Public Sub DeleteUnusedShapes()   
  Dim count As Long   
  count = 0   
  count = GetShapeAndNameCount()   
  MsgBox "The workbook contains " & count & " shapes"   
  For Each lSheet In ThisWorkbook.Sheets     
    If Not (lSheet.Name = "Query_I" Or lSheet.Name = "Query_II") Then         
      For Each lShape In lSheet.Shapes             
        If Left(lShape.Name, 3) = "BEx" Then lShape.Delete         
      Next     
    End If   
    Next   
    count = GetShapeAndNameCount()   
    MsgBox "After Repair the workbook contains " & count & " shapes" 
End Sub

Public Function GetShapeAndNameCount() As Integer   
  Dim count As Long   
  count = 0   
  For Each lSheet In ThisWorkbook.Sheets       
    count = count + lSheet.Shapes.count   
  Next   
  GetShapeAndNameCount = count 
End Function

  

5.2.5 - Excel Rendering Engines

Please note that performance of MS Excel rendering engines is different:

  • Rendering engine of Excel 2010 is slower compared to Excel 2003 but performs better Excel 2007.
  • Rendering engine of Excel 365 is slightly slower compared to Excel 2010 and Excel 2007.

None of these situation affects the performance of Business Explorer. The difference lies on milliseconds to 1 second.

 

5.3 - Optimizing event 'Not Assigned' / Complex Workbooks

5.3.1 - Parameter ANA_USE_TABLE

The RS_FRONTEND_INIT parameter ANA_USE_TABLE (note 1179647) can have a significant influence on the performance in case of workbooks that contain many queries with huge resultsets assigned to different Data Providers, many BEx Items, etc. When BEx Analyzer sends such complex definitions to the backend system through an XML, a bad performance might occur. In this case, ANA_USE_TABLE parameter can be used to reduce the network load and improve the performance. 

5.3.2 - Parameters ANA_USE_SIDGRIDMASS / ANA_USE_SIDGRID / ANA_USE_SIDGRIDWBUF  

Activate following parameters for using large and complex workbooks. The parameters optimize the frontend/backend communication time in case of the high amount of data.
Please make sure that mentioned corrections are active in the system.

  • Parameter ANA_USE_SIDGRIDMASS will be only one single RFC call for all data providers in a workbook (note 1798689). This parameter is not applicable for BW700! 
        • This parameter increases the performance BUT affects the Safety Belt and could lead to out of memory in the case you have several queries inside the workbook.
        • Recommended testing with and without this parameter. 
  • Parameter ANA_USE_SIDGRID will improve performance and memory in the BEx Analyzer (note 1150242).
  • Parameter ANA_USE_SIDGRIDWBUF (note 1352375).  Always recommended.
5.3.3 - Parameters ANA_SERIALIZE_PARTS
  • The RS_FRONTEND_INIT parameter ANA_SERIALIZE_PARTS = X helps in case of having:
    • the significant number of attributes (see note 1972854),
    • the huge number of hierarchies (see note 1930090) and
    • the significant number of hidden structure members in the query (see note 2041337).   
  • This can be seen in the BEx Analyzer Performance Trace tool under "Serialization Navigation State".
  • Make sure that the corrections are active in the system and set the RS_FRONTEND_INIT parameter with help of Tx RS_FRONTEND_INIT.

Please note with active parameter ANA_SERIALIZE_PARTS the sort option "sort according to attribute" is only applied in case the display of sort attribute is switched on in the query.


5.4 - Parameter ANA_DESIGNMODE_OPT 

The DesignMode Optimization Functionality performs the following

  • Improve the switch between Design Mode and Analysis Mode. 
  • You can set RS_FRONTEND_INIT parameter ANA_DESIGNMODE_OPT with value "X" to enable the functionality.

6 - Further Optimizations for Workbooks: 

6.1 - Optimizing Refreshing Time

6.1.1 - Single Data Provider Refresh

You can refresh individual Data Provider if more than one Data Provider is used in the workbook. Using can leads to a significant reduction of refreshing time and general improvement of BEx Analyzer performance. For details please review wikipage Single Data Provider Refresh

6.1.2 - Do Not Run Macros on Refresh

The event 'Exit Macro' consumes too much time. With activation of option 'Do Not Run Macros on Refresh' in the Workbook Setting on TAB Exists you can switch off the Macro execution. 

6.2 - Using Excel 2010

Make sure that at least Microsoft Office 2010 SP1 and MS Hotfix http://support.microsoft.com/kb/2687606 are installed. Without this hotfix, you will recognize a significant performance degradation compared to the older Excel releases especially if the workbook contains many formulas. This information can be found in note 1762660

7 - Your analysis

  • You can copy/paste following part in your OSS incident.
  • Please answer ALL following questions:
01. Occurs the performance problem using latest Frontend patch?
    YES/NO (SAPGUI 750 or 760 Frontend is mandatory!)

02. Installation Check file (SAP Note 1229206) is attached?
    YES/NO

03. Certicates are valid?
    YES/NO
    BEx Analyzer 7x starts fast?
    YES/NO

04. The Statistics Workbook is attached?
    YES/NO

05. Which actions were done while recording the statistics?
    (Nav.) Step Counter 
    1 opening query/workbook
    2 ...
    3 ...
    
06. Where do you recognize a performance problem?
    Please describe in detail. 
    > ...

07. Which Statistics events were identified as most time consuming?
    event A: ...
    event B: ... 

08. Which time you are expecting to execute the workbook?
    > ...

09. Which object is affected / was used for recording Statistics?    
    if query: technical name of the query = ...
    if workbook: workbook ID =  ...

10. If variables are used, please create a global / cross user variable variant which was used for recording the Statistics.
    technical name of variant = ...

11. Used variable selection is attached as screenshot?
    YES/NO

12. Which optimizations were performed? Please share your findings / details.
    5.1 YES/NO
    5.2 YES/NO
    5.3 YES/NO

13. How many Excel elements (see point 5.2.3) contains the workbook?
    Number of Names  = ...
    Number of Shapes = ...
    Number of Styles = ...
14. Are the service connections R/3, BW RFC, BW GUI and SAPNI opened?
    These connections are needed for further analysis by SAP.
    YES/NO

15. Is a valid user available in the SAP Secure Area?
    YES/NO

16. Record a RSTT trace while reproducing the issue and provide the Trace ID).
    > Start Tx RSTT on BW side first, afterwards connect with BEx Analyzer on BW System.
    RSTT Trace ID = ...
 
17. Execute the query on transaction RSRT - Display Mode HTML or LIST. Do you notice any performance issue? 
	YES/NO
 
18. Please, take a screenshot of the content of tables RSADMIN and RSFRONTENDINIT and attach into the incident

 


Link to this page:  http://wiki.sdn.sap.com/wiki/x/v42xDw
KBA Bridge: 2518355 - Optimizing BEx Analyzer 7x Performance - Wikipage


 

 

 

 

 

 

 

 

 

 

 

 

 

 

ANA_USE_COMPRESSION 
ANA_CACHE_WORKBOOK
ANA_USE_OPTIMIZE_STG
ANA_CTX_SENS_DBL_CLK
ANA_USE_TABLE
ANA_USE_SIDGRIDMASS **** Recommended for specific cases.
ANA_USE_SIDGRID
ANA_USE_SIDGRIDWBUF
ANA_DESIGNMODE_OPT

 

 

 

 

Performance Analysis
---------------------------
SYSTEM
***************************************************************
ABAP (): SAPKW7
***************************************************************
SAPGUI Version/Patch:
BEx Analyzer FEP:
  
Workbook ID:
Workbook Format:
Workbook Definition
* Dataproviders:
* Grid Items:
* Structure: ; Members:
* Unused names and shapes:
- Names:
- Shapes::
- After repairing the workbook, Global Settings -> Repair workbook
While lrunning (it is enough when you refresh 1 data provider):
- Names:
- Shapes:

* Ue Compression when saving the workbook =
Single Data Provider Refresh =
User Optimize Storage (new formats) =
* RS_FRONTEND_INIT parameters (table: rsfrontendinit):
ANA_USE_COMPRESSION
ANA_CACHE_WORKBOOK
ANA_USE_OPTIMIZE_STG
ANA_CTX_SENS_DBL_CLK
ANA_USE_TABLE
ANA_USE_SIDGRIDMASS
ANA_USE_SIDGRID
ANA_USE_SIDGRIDWBUF
ANA_DESIGNMODE_OPT
 
  • Time after repairing the workbook and setting the flag "Do Not Run Exit"
  • No labels