Input Parameter with Key and Text in view help ( F4)
This blog would explain how we can use both Key and Text in view help ( f4 values) of HANA Calculation View
***Now this functionality has been added to SPS10, if you are at SPS10 or above, you no longer need this work around ***
Scenario :
Users want to filter data by Customer and see Customer Text in view help along with number . As the table has billions of rows, to improve report performance , we want to use Input Parameter in Projection Filter . Available types of Input Parameter in SPS9 cannot use Key and Text both directly in View Help or F4 values .
Our Data Model is Calculation View . Input Parameter behaves differently in Calculation View and Analytic View.
Calculation View : If the value of Input Parameter is blank , model would return no data.
Analytic View : If the value of Input Parameter is blank , model would return all the data. ( Provided Input Parameter is not Mandatory )
Solution :
Created an Attribute View on my Customer Table with Calculated Attribute . In test case both Customer Number and Text is from same table
You can join different attribute and text table to create the same based on your scenario .
Calculated Attribute KEY_N_TEXT : Concatenated Key and Text for Customer
Data From Attribute View :
I created a calculation view with Input Parameter type Column and use the above concatenated attribute in the reference column for view help.
Now we can restrict the data in Projection Filter with Input Parameter . However, this input parameter would give us value of Key and Text together while we want to restrict only by Key ( that is our column value ) . So, we can take first 6 character of the Calculated Attribute to get the Key.
But if user does not want to choose any customer ( to run for all customer), this model would not result any data as Input Parameter in Calculation View must be provided with data. To get around that, I used Match function.
So, over all filter expression would be like this :
“CUST” = leftstr ((‘$$CUST_WITH_TEXT$$’),6) OR match ( “CUST”, leftstr ((‘$$CUST_WITH_TEXT$$’) ,1) )
When a customer key and Text is selected, only first 6 character would be passed as filter condition .
I also set default value of the Input Variable as *All Customer* . So, if user wants to see data for all the customer, Match expression take the first character ( which is * ) and returns all the data .
Test 1 : User choose one customer From view help
Out Put :
Test 2 : User does not choose any Customer
Attribute View for View Help can also be created on your transaction data table to give you list of only those customer, for which you have values in transaction Data.
very well written Anindya, it helps a great deal, thanks a lot.
Thanks Amit
I liked this approach as I am trying to help a client solve a similar problem so I decided to do some further testing on this from a performance perspective.
Conclusions
- This is very performant and user friendly >= SPS9
- In SPS8, the performance is terrible
- This will not work for multiple value input parameters. This should be obvious, but from a usability perspective has a big impact. Not all columns can get away with just a single value for input.
In SPS8, the optimizer chooses to create a projection on the column for every row in the table and THEN only compare to your modified input parameter. What this means is that the entire table has a projection created, which is very expensive. See the vizplan here; over 250+ operators that were performing this work. This query took maybe 10 seconds, while a comparable direct input parameter took 50ms.
In >= SPS9, however it's a different story. The optimizer must realize that instead of creating projections on the column, it can be compared directly and the filter is pushed down to the table without additional overhead.
So moral of the story - good approach, but make sure you check how your instance is behaving and be aware of the limitation.
Happy HANA,
Justin
Dear Justin
Thanks for your detail analysis and feedback. By the time I started testing this, my development box got upgraded to SP9, so did not get a chance to test in SPS 8 .
I expected it to work for multi-values input parameter also, however after creating the model I found it is not being able to modify multi-value parameter the way I want it to .
User coming from BEx would certainly like to have text and Key together for lot of characteristics variables . I hope in future release SAP would address this issue.
Cheers
Anindya
Anindya,
Would this then filter at the projection level and not at the table? I am wondering if you would return all of the values initially from the table and then filter at that point? Is that the expectation here? If not, please tell me how I can apply the filter using an input parameter on the table so I can limit the initial select. The reason I am asking is if there are a lot of records, it seems like the initial select could be large.
Thanks,
Tim
Hi Tim
You should apply the filter ( via Input Parameter ) at projection level. This is the first level where you access your table data in a calculation view.
Let's say you select customer C20001, this is equivalent to
Select * from Base_Table where Customer Id = 'C20001' .
So, if your base table has total 50,000 records and only 1000 for Customer C20001 , it would pass only 1000 records to Projection Node.
You would be able to see this in PlanViz
Cheers
Anindya