Performance tuning of SAP Analysis for Office reports - Part 1

[former_member186922]

Background:

With many SAP customers moving their traditional BW landscape toward BW/4HANA.

Its becoming a need to move the traditional reports developed on top of "BEX Analyser" to "Analysis for Office - AO". (as Bex Analyser is not supported with BW/4HANA => Detailed_link ).

With this the user base on "Analysis for Office" is continuously increasing and thus its very important to tune the AO reports , in order to achieve the best performance.

 

Performance Tuning Recommendations:

Below are the list of performance tuning options available

Recommendation 1:

Designing AO reports which is in inline with BEX Queries

AO reports holds snapshots of the of the last viewed saved state.  i.e. while viewing the AO reports, it tries to reach this snapshot.

However if there are hierarchies expanded or collapsed, then it would not hold the final state, but instead holds the steps performed to reach the final state.   i.e. Time to view the AO report, is proportional to time involved to perform each of these additional steps

Thus its very important to understand how the end user would like to view their reports and design the generic BEX query accordingly.

Example 1 -

1. If the AO report is collapsed by default as seen below


2. With the following Expansion level in the Bex query.


3. Then while viewing the AO report and recording its traces via transaction RSRT , the following calls are observed

 

Example 2 -

If the same AO report is expanded (by two levels) from its base collapsed version and then saved it(as seen below)

1.  Base view:


2. Base view, followed by expanding the node to level 2


3. Continuing this expansion to level 3 and then saved the report.


4. Then each expand would lead to an extra call in the back end , as observed in the captured RSRT trace (while viewing this new report)

These extra highlighted calls, could lead to increased time in viewing the report and could lead to poor performance.

Example 3 -

These extra calls could be avoided, by Pre-defining the expansion level(from level 1 to level 3) in the backend Bex query as follows

1. Increase the Bex query "expansion level" from 1 to 3


2. Once done, a new AO workbook needs to be created (by importing the updated Bex query), whose landing page are expanded by default.


3. Now while viewing this report, a corresponding RSRT trace is captured. Which shows that, the extra calls are not appearing any more(due to expanded queries in the back end).


4. Thus we could achieve better performance, while generating the same output as shown in example 2.

 

NOTE:

The sample examples that was taken for reference in this example, had very less sample data. The factor of performance difference between Example 2 and Example 3 could increase, with the amount of data sets retrieved.

 

Further recommendations are available in my followup PART 2 section below:

https://blogs.sap.com/2019/09/18/performance-tuning-of-sap-analysis-for-office-reports-part-2/