Resolve Performance Issue with HANA Filter Variables (used as prompts) in Analysis for Excel

In this blog, I will discuss the performance issue which I experienced while selecting input variables/filters (prompts) for HANA information view consumed directly in SAP Analysis for Excel 1.4 SP5. This performance issue might be faced during consuming HANA information view with large amount of data.

Usually in SAP HANA we create variables to filter the data in HANA information view at runtime or design time. For example, in following window we have a variable for "Plant".



Let's look at the filter variable created in SAP HANA for attribute "Plant". This variable will display values from column "Plant" from same information view. When user click on browse button (as shown in above image) then it display these values.




When user clicks on browse button to select value for "Plant" variable then it generates a SQL statement. Let's look at the SQL statement captured from Analysis for excel log file. I ran that query in SAP HANA Studio to capture the execution time.


You can see that query took nearly 5 secs. Now, that means when user click on browse button then they have to wait for 5 secs before they can select value. Later, they have to wait another 5 secs once they selected the value (it runs same query with where clause). So in total they wait 10 secs before they can go to next variable (if needed). Please think if there is variable with more than 100 distinct values. It was not acceptable for us.

We can overcome this issue with little sacrifice. We can query another view which doesn't have so much data or created specifically for "Plant" related data. In our setup we have another package for master data and shared views which we use in other information views. For example, we may need plant name in 10 information views so we can use single view in 10 information views (specifically for star join in calculation view as dimension or in analytic view as attribute view). This also reduces the number of column views created for similar information in number of projects.

So I changed the information view for plant variable



Let's take a look at SQL statement after we changed the information view in "value/Table for value" section


The execution time has changed to 33 ms. This is huge difference. However, we talked about little sacrifice above. When using other view for values in variables it doesn't display the text (label column) with key. It only displays the key. For example: Plant Name is not used in query and therefore not displayed in result of second SQL statement. This could be a bug or feature that we should wait for in upcoming HANA SPS.

Update on 2nd November, 2014: This issue is now resolved in SAP Business Objects Analysis for Office 1.4 SP6 and up. 

Comments

  1. Angad, thank you for sharing.

    It seems your update on 2nd november is a bit optimistic.
    I expirience the same "key only" issue in value help table based on attribute view with AO 1.4.7 and 1.4.9 and HANA SPS82.

    Could you please share the version of HANA system you use? Or any reference to SAP doc with the bugfix description?

    TIA

    ReplyDelete
  2. Hi Axion,

    I am sorry for late reply. I was away.

    We had HANA SPS8 REV 80 at that time. The 2nd November update is purely based on my testing. I upgraded the Analysis for Office to SP6 and the issue was resolved.

    However, I quickly went through release notes of SP6 (AO) and found this relevant note 1978144

    Cheers
    Angad

    ReplyDelete

Post a Comment

Popular posts from this blog

SAP HANA Input Parameter - Derived from Table - Use Expression in Filter Value

SLT Replication Issue Due To Timeout Reading Logging Tables