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. Axion10 December 2014 at 01:12

    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. Angad Singh5 January 2015 at 13:37

    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

Image
Recently, I have experienced something while modeling HANA information view that I want to share with everyone.  Case:  Dynamic Filter for projection with current Fiscal Year and Fiscal Period.  Method: There are different parameter types that we can use to do this. I decided to use following "Derived from table" type as shown below:  Challenge: When filtering column then it displays list of values from the same HANA information view and we can select the date. Now, as the requirement is current Fiscal Year and Period so how do we filter the column to dynamically select current date in "Filter Value" section? Solution: Like everyone, I expected it to understand the expression. So I tried following and it worked. I used the expression  currentdate() I was aware that I could use expression in the "Default Value" section (as explained in SAP HANA Developer Guide) but didn't expect it to work in "Filter Value".
Read more

SLT Replication Issue Due To Timeout Reading Logging Tables

In our company we use SAP LT (Landscape Transformation) replication server to replicate tables from SAP ECC system to SAP HANA in real time. SLT creates trigger for each table that we want to replicate and whenever there is change it stores the changes in the logging table. Later on, these tables are used to update replicated tables in SAP HANA.    This system is really useful when you really need real time replication to SAP HANA. However, it also causes performance issue if proper monitoring is not being performed. Just recently, we faced an issue where we were trying to replicate a large table "VBFA" to SAP HANA in production environment. Every time table replicate status changes to "ERROR" after the initial load. It started to fail when ever SLT tries to read change/delta stored in logging table. This tables replication worked fine in DEV and QA environment.   After troubleshooting and taking help from SAP support we found that in the program generated
Read more

SAP HANA Content Security Roles Setup

Image
A few months ago I was given a task to implement content security in SAP HANA. The main purpose for this task was to provide Business user access to information models created in SAP HANA. For example Finance user should only view finance package and can access information models in that package via BI tools such as Analysis for excel. So, after a research and few discussions with various people (one of them was from SAP Melbourne) I came up with following security model. Let's assume that content is maintained in following structure: So based on each type of privilege I created the roles as shown below: System Privilege Roles These roles are mainly needed for System admin tasks (technical role) X_HNS = S for System Privilege role Role Name Privilege Type Assigned Privileges X_HNS_USERADMIN This role can create users, change their passwo
Read more