Analysis for Office is an excellent tool for office productivity providing one interface on Excel and BI. With Analysis for Office you are able to connect to BW and HANA and expect that the execution had a similar performance when the query is executed or viewed on frontend and backend.

When huge data sources are processed or the system is heavily used, applications take a huge hit in performance and this is where the strategies below will help.

Some common performance tips for Analysis for Office are listed which you can use to analyse your worksheets and potentially increase the performance of Analysis for Office.

Common Refresh

It is recommended to choose Refresh all when multiple data sources are used in a workbook. This prevents data irregularity.

Sheet by Sheet Choice

The type of sheets Non-COF is not checked by the Analysis Add-In which will improve performance. This is helpful in case of analysis reports which does not involve calculations on it.

Styles

Large number of style set usage would lead to poor performance.

When inserting a new cross-tab in a workbook, the styles in the current default style set are used to format the cross-tab cells.

Check Profiling Statistics

Tracking performance of a workbook for every step that has been performed is made much easier. Statistics can be traced with respect to workbook as well as Client.

Log Severity to “Support”

Settings -> Support settings

The system writes the trace information to the log file until the file reaches the maximum size of 2048 KB. The log file can be PC-specific (per installation) or user-specific, depending on the folder path in the file system. In order to enable it,

The “Debugging Information” is the most detailed LOG file and will help SAP product support to analyze Analysis for Office incidents.

Message Handling

Use below option to record messages.

Backend (normally BW) and Analysis for Office are able to throw messages. The different type of messages- Information, Warning, Error and Critical.

Messages can be opened via “Help=>Show Messages”

Settings

Merge Prompts: Enable. Combines together if the same prompt is present in multiple data sources. Keeps prompt screen uncluttered.

Hope these tricks helped!

Got questions? Click here to get in touch.