How to ignore worksheets from Analysis Office processing and improve performance

[hai_murali_here]

Hello Everyone,

Here I would like to showcase, how to ignore worksheets from Analysis Office processing and improve the initial loading performance.

Consider the scenario of AO workbook, which is quite slow in opening and has lot of worksheets with excel formulas and excel functionalities.

In such cases, lot of time is spent by AO, analysing all the worksheets in order to find out, if there are SAP formulas or data related to datasource.

If we mark all the worksheets, which do not contain any AO functionality as “Non-COF”, we can improve the loading time of workbook. This can be done under the “Components” tab of Display panel.

Let’s consider the AO workbook which has two worksheets, wherein first worksheet contains the Data source(from BW or HANA) and the second worksheet just refers the first worksheet and uses some excel formulas and functionalities to get the expected results.

Sheet1 -> Contains the Data source

Sheet3 -> Contains the excel formulas and functionalities

Please note that, by default, when the data source is added to any worksheet, it will be treated as Analysis Sheet Type. In this case, Sheet1 is having a data source from BW, and hence it’s marked as Analysis Type

Let’s say “Sheet3” is not marked as “Non-COF Worksheet”, and marked as “Analysis”(which is default).

Open the AO workbook and refresh it. Check the AO Logs by opening the .glf file.

You can see that the data presented in Sheet3 is taken into consideration by AO, for processing.

Let’s look into the other scenario, say “Sheet3” is marked as “Non-COF Worksheet”.

Open the AO workbook and refresh it. Check the AO Logs. You can see that AO skips that worksheet and hence time spent for this checking would be cut-off, and the loading performance would be improved.

Hope this helps.

Regards,

Murali

[Former Member]

Good one muralikrishnan.e!

I wanted to know how did you switch on the Trace for AO? I get only a log .glf file for every time i open a workbook.

[hai_murali_here]

Hi Utkarsha,

Please change the Log Severity to "Support" under Settings->Support Settings tab.

For more information about AO logging, you can refer SAP Note 2129389 - Analysis 2.x log / Trace Files

Rgds,

Murali

[former_member198454]

Very nice. Thank you!

[avinashd_m]

Usefull information Murali,

Thanks,

Avinash

[Former Member]

Thank you Murali

[Former Member]

Hi Murali,

in which version(s) is that available?

Can't find it in 1.4.14 though...

Thanks, Martin

[avinashd_m]

Hi Martin,

In preliminary version of the AO 2.3 , i am able to find this option.

Thanks,

Avinash

[hai_murali_here]

Hi Martin,

You would find this option from AO 2.x versions.

Rgds,

Murali

[former_member345579]

That is pretty informative. I have similar case, but with EPM plug-in, not with AfO plug-in. Do you know how to make Worksheet Type = "Non-COF" for EPM plug-in by default?

AutoActiveEPMSheet parameter = "false" in the FPMXLClient.dll.config file is not working. All new Excel workbooks are started as "EPM Workbook" by default. It decreases performance a lot.

Many thanks in advance.

Regards,
Tim