As we have seen in our previous blog on “How to optimize AO reports performance, by adjusting the BEX query”
The below section explains further optimization techniques to achieve better performance
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 while viewing the AO report , small amount of data is processed at a given point of time in the Backend system and could lead to “more round trips” triggered between the front and back end(to process the complete set). Resulting in increases response time
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
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).
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
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
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
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.
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.
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.
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.