Skip to Content
Author's profile photo Muralikrishnan Ethiraj

How to ignore worksheets from Analysis Office processing and improve performance

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

Capture1.JPG

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

Capture2.JPG

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

Capture3.JPG

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

Capture4.JPG

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

Capture5.JPG

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.

Capture6.JPG

Hope this helps.

Regards,

Murali

Assigned tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      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.

      Author's profile photo Muralikrishnan E
      Muralikrishnan E
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      Thank you Murali

      Author's profile photo Lynn Peter
      Lynn Peter

      Very nice. Thank you!

      Author's profile photo Avinash D M
      Avinash D M

      Usefull information Murali,

      Thanks,

      Avinash

      Author's profile photo Former Member
      Former Member

      Hi Murali,

      in which version(s) is that available?

      Can't find it in 1.4.14 though...

      Thanks, Martin

      Author's profile photo Avinash D M
      Avinash D M

      Hi Martin,

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

      Thanks,

      Avinash

      Author's profile photo Muralikrishnan E
      Muralikrishnan E
      Blog Post Author

      Hi Martin,

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

      Rgds,

      Murali

      Author's profile photo Timur Zaynashev
      Timur Zaynashev

      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