Performance tuning of SAP Analysis for Office reports – Part 2

Continue……….

As we have seen in our previous blog on “How to optimize AO reports performance, by adjusting the BEX query”

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

The below section explains further optimization techniques to achieve better performance

Recommendation 2:

Provide an optimal value for the “ResultSetSizeLimit” parameter in AO 

Limiting the amount of Data sets processed across different layers, plays a major role in controlling the health of the system.

AO provides a feature, of controlling the maximum number of crosstab cells that are loaded from the server for one data source, using the parameter “ResultSetSizeLimit” . If this parameter is set to -1, the setting uses the values defined in the BW system.

• If “ResultSetSizeLimit” is set to a very LARGE value                                                              Then while viewing the AO report , large amount of data is processed at a given point of time    in the Backend system and could lead to “Page allocation failure   TSV_TNEW_PAGE_ALLOC_FAILED error” (i.e. after reaching the Work-process heap size)

• If “ResultSetSizeLimit” is set to a very SMALL value                                                           Then could fail the AO to load, Resulting in failure.

Thus its recommended to understand the business case and provide the right value for the  “ResultSetSizeLimit” parameter

i.e. by performing the following steps

Open the Analysis for Office workbook -> Go to file -> Analysis -> Customize Analysis -> Technical Configuration -> Search for ResultSetSizeLimit -> set the appropriate value

Recommendation 3:

Saving AO report with Data   

Saving AO report with data, would help in improving the AO report execution time(along with following recommendation 4 & 5).

Recommendation 4:

Enabling Caching  

To be done along with Recommendation 3

Caching increases the performance of the AO reports, by saving certain amount of data locally.

Which could be enabled using the parameter “IsCachingDocuments” at two places

Caching Locally in Analysis for office

i.e. by performing the following steps

Open the Analysis for Office workbook -> Go to file -> Analysis -> Customize Analysis ->  Technical Configuration -> Search for “IsCachingDocuments” -> Enable it

Caching in the BI Platform

To enable caching for documents stored on a BI platform, the following prerequisites have to be fulfilled

Go to Central Management Console -> Applications  -> Analysis Office Runtime -> Properties -> Caching

Note: Supported with BI platform with release 4.2 SP3 or higher

Recommendation 5:

Delta Update

To be done along with Recommendation 4

AO report could be either updated by reloading the complete data of a data source or by using the delta data.

This feature is available using the parameter “UseDataSourceDeltaUpdate”.

Performance of viewing a report could be increased, by setting this parameter to true. Which then loads the delta data of the data source and thus reduces the traffic .

Delta Update to be enabled as follows:

Open the Analysis for Office -> Go to file -> Analysis -> Customize Analysis -> Technical Configuration -> Search for “UseDataSourceDeltaUpdate” -> set it to true

If you change the parameter value to False, the complete data of the data source is reloaded to Analysis

Recommendation 6:

RfcBundling

Performance problems could occur, while AO is communicating in scenarios having “wide area network (WAN)”, and could lead to increased loading time of the application.

This could be avoided by enabling “RfcBundling” as follows

Open the Analysis for Office workbook -> Go to file -> Analysis -> Customize Analysis -> Technical Configuration -> Search for RfcBundling -> enable it

Recommendation 7:

Defining sheet type as Non-COF

Loading time of the AO report could be improved by defining the Sheet Type to “Non-CFO Worksheet”(for those sheets, which does not have AO functionality added).

As “Non-CFO Worksheets” are ignored from Analysis Office processing, which thus improves the initial loading time of the AO report.

Recommendation 8:

Reduce the number of Styles sets used

Styles are used to format the crosstab cells.

It is observed that, if the AO report has been developed using large number of style sets, then this would consume certain amount of time in preparing the AO report(i.e. certain calls to Excel get very slow.).

Thus its advisable to keep the style set to a minimal level , while developing the AO report.

Recommendation 9:

Reduce the number of VB scripts / Macro’s used

Macro’s and VB scripts could also lead to poor performance in loading to AO reports. Thus its advisable to keep these things only if required.

Recommendation 10:

Mass Conversion of Bex Analyzer workbooks to AO reports

Please refer to the below link, if Customers wants to MASS CONVERT the large amount of “Bex Analyzer workbooks” into AO reports.

https://blogs.sap.com/2017/07/12/automated-mass-migrationconvertion-of-bex-workbook-to-ao-2.x/