Resolve Performance Issue with HANA Filter Variables (used as prompts)

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. This performance issue might be faced during consuming HANA information view with large amount of data.

Versions:

Analysis for Excel: 1.4.5.2837

SAP HANA: Rev 70 SPS 07

SAP HANA Studio: 1.0.7000

Usually in SAP HANA we create variables to filter the data in HANA information view at runtime. 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 click 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 as I found in analysis logs). 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.

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) instead of creating 10 attributes views for plant description (or related data). 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. Please do suggest if you believe it can be fixed. Please share knowledge to gain knowledge.