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.
Hey Angad
Sorry, I don't really get what your point is here.
Executing some likely complex calculation view logic to only receive the plant name attribute is not as quick as simply selecting it from the base table?
Not surprising, I'd say.
Also the part of the "sacrifice" is not clear to me. What's the problem here?
I don't see how a new HANA feature could fix that the data modelling here doesn't allow to retrieve the data you are interested in.
- Lars
I understand the first part about what you are saying regarding performance, we had a similar issue.
Essentially what is happening is that to populate the variable's list of values (LOV) in the client tool, it was issuing a SELECT DISTINCT <column> from the model. When based on a large fact table, it can be very slow. So to avoid this problem, we can "point" the variable at the dimension itself, which is likely to be much smaller. For example, instead of querying the COPA model, we can just query the PLANT dimension to present a list of values to the user.
I can't comment on the "sacrifice" part as I didn't have any issues there, or would need to look more closely.
Angad - what I would say here is that you need to be VERY careful to recognize what version of AAO you are running. With AAO 1.4 SP00, the workaround you show is not possible. Even if you manually "repoint" the variable, the client tool still issues a DISTINCT against the column in the model. We had an OSS message open where it was discovered that a client tool upgrade to AAO 1.4.5.2837 (1.4 SP05) was required. After that, the configuration present in the variable was able to be leveraged (repointed).
So, long story short, the version on AAO makes a big difference in the context of your blog.
Regards,
Justin
And to add, the same issue was happening in BO Explorer 4.1SP2. And when those InfoSpaces are executed, even though a default variable value may be defined, that SELECT DISTINCT is executed EVERY time. This can slow things down in a big way, with no value add.
Regards,
Justin
HI Justin,
Thanks for your comments. You have provided really good explanation for first part and has enhanced the motive of my blog. I understand I should have mentioned the AO version. I will edit the blog soon.
Regards
Angad
Hi Angad,
If my understanding is correct, you are mentioning the following:
--> If you use any-other view other than the "current view" in the model to display the LOV's. The Text is not getting enabled even though had used a "Label Column" in your attribute view ( In your case Plant ) right?
Version am using : 1.4.0.2528
even am facing that error as mentioned below. Is that what you are referring to the bug?
Regards,
Krishna Tangudu
Hi Krishna,
You are absolutely right for second part. It seems that we have been trying similar things. 🙂
I will edit the blog soon to reflect AO version.
Best Regards
Angad