The purpose of this page is to clarify the understanding of the system performance when using Analysis for Office (AO). On this page you will understand how to analyze the performance when using BW queries and views and how to improve the performance.
With Analysis for Office you are able to connect to BW and HANA and expect that the execution had a similar performance when executed the query or view on frontend and backend. For some cases, you are using the Analysis Office and you feel a not satisfying performance after opening a query or a workbook.
Below you will find tips to improve the performance on Analysis for Office side and to identify where is the performance room for improvement.
Before start is important to highlight that SAP recommend to use the latest patch that contains performance corrections. It is recommended to check the item 4 of the Analysis Office Installation and Update wiki page.
1. BW Server Tracing
The first and simple analysis to do is to compare BW and Analysis run time. You should execute the query with same selection in backend via RSRT2 (HTML mode or ABAP BICs mode) 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 Analysis Office related. If you find that the same performance can be reproduced via RSRT2, you can treat it as a backend improvement otherwise you can set the BW server trace to analyze the Analysis for Office execution.
In order to set the BW server trace, please review the Analysis for Office RSTT trace record wiki page. There you will see how to set the BW server trace and to execute the trace on backend.
Important: In case you see on the trace display a function module with a high value on the run time, is extremely recommended to apply the SAP note 2245034. On this note you have a PDF with all function modules that requires a specific BasXML configuration to improve the performance.
2. Workbook Profiling
For the scenario where the performance is related to a workbook, first you will need to identify all data providers and the assigned data sources of the workbook to execute on the backend and check the performance as described on item 1. BW Server Tracing. To do it you need to follow the steps described below:
1. Start the Analysis Office and open the workbook.
2. Open the Design panel, you can find data source name in "INFORMATION" section on "Information" tab of Design Panel.
3. Check the direct execution of the query which is used as DataProvider in the workbook.
After that initial analysis comparing Analysis runtime with backend (BW) runtime, you can set the workbook to record the client profiling.
In the Client Profiling Statistics dialog, the captured interactions are displayed as steps in a tree view and you can navigate to each interaction. You can see the overall processing time per step (in ms), the time the user spent in dialogs, the number of RFC calls per step and the time that was spent for RFC. In section Update UI, you can see if a navigation step has caused (unexpected) RFC calls.
At the bottom of the Client Profiling Statistics dialog, you find a summary containing the time that was used on client side for processing (in ms), the number of RFC calls (count) and the time that was used for that (in ms) and the time the user spent in dialogs (in ms).
Following you find the steps how to create AO statistics.
- Open the Analysis Office via Windows Start Menu.
- In the Analysis Office Settings dialog go to "Support" tab and activate the checkbox "Enable Client Profiling".
- Close the Analysis Office and restart it.
- Execute the sequence of steps which creates the performance problem
- Open "Message" dropdown box and choose "Show Client Profiling Statistics"
- Click "Export" button and save AO statistics file as .XML file.
For more details you on how to record the client profiling, please check Profiling information wiki page.
3. Query runtime statistics
Analysis supports the query runtime statistics of SAP NetWeaver BW. You can activate the query runtime statistics in the User Settings dialog box on the Support Settings tab with the Enable Workbook Profiling setting or with the file system setting Profiling in the Ao_app.config file.
Using the query runtime statistics, you can find out how much time the execution of certain user actions require in Analysis and in the BW analytic engine. On BW side, the table RSDDSTAT_OLAP will have step type 'AOE' for Analysis Office Excel and 'AOP' for Analysis Office Powerpoint. see note 1813352.
If the Analysis events should be written to the RSDDSTAT_OLAP view (transaction code SE16), which contains the data from the front end and calculation layer events, they need to be in table RSDDSTATEVENTS as well. For more information, see SAP note 1462547.
4. Performance in HANA scenario
In order to check the performance of a HANA scenario, it is possible to use a tool that captures the HTTP traffic.
The trace also helps to check the performance of specific HANA calls. The SAP note 2129389 contains the instructions to record the trace.
The file name should start with "AO_Trace" and can be opened in any text editor. The trace contains the calls to HANA and the time taken to retrieve the data in each of them. The calls look like the following for example:
[BICS HANA] get result set data runtime 794ms.
[BICS HANA] get members runtime 1065ms.
Related SAP Notes/KBAs
1466118 Analysis Office hardware & software requirments
1518359 SAP BusinessObjects Analysis Office Release schedule
1462547 Statistic events for Advanced Analysis Office
1813352 own statistic step types for Analysis tools (ABAP)
2245034 BICS: BasXML flags of BICS function modules
Link to this page: https://wiki.scn.sap.com/wiki/x/-wJQGg
SAP BusinessObjects Analysis for Office tutorial: http://scn.sap.com/docs/DOC-7679
Analysis for Office Community: https://www.sap.com/community/topic/businessobjects-analysis-ms-office.html
Analysis for Office Wiki page: http://wiki.scn.sap.com/wiki/x/kgxBGQ