Using Dynamic Filters in SAP HANA
With SAP HANA 1.0 SPS6(Rev 60), we can now leverage the concept of dynamic filters. There have been several requests for this type of functionality, since SAP does not recommend the use of dynamic SQL(EXEC statement) when developing SQLScript procedures. We now have a new statement in SQLScript called APPLY_FILTER. This statement accepts two parameters. The first parameter is the dataset in which you want to apply the filter. This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable. The second parameter is of course the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement. In the following example, I have a SQLScript procedure which simply reads data from the “Products” table and applies a filter which is passed as an input parameter to the procedure. The result set then shows the filtered dataset.
CREATE PROCEDURE get_products_by_filter(
IN im_filter_string VARCHAR(5000),
out ex_products “SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products” )
SQL SECURITY INVOKER
READS SQL DATA AS
Thanks for putting up an example and explaining dynamic filters on SPS 06.
Thanks for your information.
Can you please explain what is
Sure that is the name of a database table. "SAP_HANA_EPM_DEMO" is the schema. "sap.hana.democontent.epm.data" is the package in which the table is stored in the repository. "products" is the name of the table. This table was created using a .hdbtable file in the repository.
Thanks a lot for sharing on this new feature.
Can we use the APPLY_FILTER feature as part of Scripted Calculation views also.Or it is only limited to Procedures.
I would think that this syntax would work in a calc view as well, but I have not tried it.
Thanks Rich. This will help us to pass select-options values and to use them in the sqlscript as a filter .
A quick question : We are using ERP on HANA ( ECC6.06 , SP00 ) . Is it possible to upgrade our HANADB to SPS06 without upgrading the ERP to SPS02/03 ?
thanks for your sharing, but i have one confused question on that new feature.
as you know, in version previous SP06, hana donot recommend to use dynamic SQL statement for the consideration of performence. if you use "APPLY_FILTER" statement, we actually use dynamic SQL statement. what is the performence difference between previous dynamic sql expression (i mean "EXEC ..." statement) and this new feature.
APPLY_FILTER statement does not use dynamic SQL and no association with EXEC statement, so there should be no related performance issues when using APPLY_FILTER.
I have what seams like a simple queston. If I wanted to add a dynamic filter to a projection in a calculation view that was int(leftstr("BUDAT",4)) <= int(leftstr( now(), 4)) why would I get a type conversion error when activating the calculation view. BUDAT is a varchar(8) column in my analytic view. If I create a calculated column using the syntax from either side of the operator it parses. int(leftstr("BUDAT",4)) returns the numeric year and int(leftstr( now(), 4)) returns the current year.
All I am trying to do is restrict the data so that only records from the current year (or less) are included. I also want it to be dynamic so I don't have to edit the calc view every year. There is budget and forcat data in the AV for future dates, but I don't want it in this calc view.
Thanks for a very good example. I have refined it further and used in HANA Calc view with pasing a dynamic variable.
Thank you for your sharing new feature, I have 2 questions for this feature.
Q1, The filter dataset cannot be set analytic view, right? "The first parameter is the dataset in which you want to apply the filter. This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable."
Q2, whether we can use this feature at anywhere not only procedure? such as XS engine, BO tools and so on.
1) Yes, I think that is why I specifically left out analytic view from that list.
2) This is a SQLScript feature, so you can only use it within a SQLscript procedure. Of course you can call a SQLScript procedure(which is using this feature) from for example an xsjs service.
Very use full information which I have been trying as part of the requirement . As this feature is made available from SPS 6 , waiting for the upgrade to SPS 6 to explore more on this front.
Thank you Very Much ......
How do we apply filter for more than 1 field ?
You are simply providing a dynamic WHERE clause, so basically you put those additional fields into your filter string, separated by AND or OR.
Can this select be more dynamic? Like we able to specify the table name at run time?
I tried a lot understand your example but unable to grasp anything. I have read your above statements plenty of times and could able to figure out the hidden secret and could achieve what I want. Thanks Rich.
Thanks and regards,
Thank you for your beautiful post~.
I'm struggling with 'SELECT-OPTION" in ADMP side, and your post is very helpful to me.
The first parameter is for DataSet and it seems that only single table (in case of table..) is possible for the 1st parameter...
What if more than 2 table with joins... are used for the 1st parameter..?
Is it possible..? (I think I can make it work if I create an attribute view... but only tables are available.. unfortunately..)
Do you have any guideline for this case...?
Thank you in advance,
Hi Rich, thanks for the article. If I may one question.
I need to create dynamic filtering scenario where depending on the user ID that accesses the Calculation view ( from SAP Design Studio) I need to bring for him only certain set of values for lets say "Cost Centre Groupings" ( not totally restricting like in an analytical privileges, but just default filtering)
I have a mapping table with user ID to Cost Centre Groupings relationship. The issue is how to pass the current user/ session user parameter in the filter value section of the Input Parameter that I have created in my Calculation View?
I know that for example statement "currentdate()" in the filter section of the input parameter filter value, will give me the current date and I can filter on that, but is there a statement to get the value of the current user ?
Is there any reference guide where all these statements accepted by SAP HANA could be found ?
And the second question is is there any way where procedures could be linked to the input parameters in SAP HANA?
Is there any way we could write SQL logic somewhere within input parameter so we can deal with the complex scenarios?
Did you try CURRENT_USER?
Hi Rich, I tried it but it doesn't work the same way as now()
Any other suggestion would be appreciated
One question, considering the following statements:
VAR_WHERE := ' DOCNUM = ''000000001'' ';
VAR_A = SELECT * FROM VIEW;
VAR_FILTERED = APPLY_FILTER(:VAR_A,:VAR_WHERE);
How does HANA will work on this case ? It will fetch all register from VIEW and then apply the filter over the internal variable, or it will apply the filter directly on the select statement ?
In the end, the statements will be inlined and executed as...
SELECT * FROM ( SELECT * FROM VIEW ) WHERE DOCNUM = ‘000000001’
This solution may not have performance problems, but I would imagine the security concern would still be the same as with dynamic SQL. For instance, I could make this procedure return all records by concatenating an ' Or 1=1' snippet at the end of the parameter.
Yes, because we are passing the entire filter string as a parameter, you could just add anything to that string. But in other scenarios, we actually do provide a level of protection against SQL injection when using APPLY_FILTER.
Very nice ! Thanks !
Thanks for that explanation Rich. I was looking for a way to use apply_filter on a parameterized CDS view, which doesn't work. So I had to go about it the way Lucas wrote above and I had the same concerns about wasting resources.
Thanks for the great post.
Would like to know the internal implementation of APPLY_FILTER (including time complexity).
Can you Pl. suggest any resources for the same.
Its a good article.
From your example , if I use calc view instead of table and if the calc view takes mandatory input parameters, how can we pass those input parameters when using the apply_filter as I believe this apply_filter takes only 2 params.
Can you pl. clarify
APPLY_FILTER performance issue.
Hi Rich, I came to a scenario which is realy performance critical and APPLY_FILTER seem to work not as wished in the query plan:
I have to build a completely dynamic filter on a huge data set provided by a calc view. Therefore I use a scripted calc view. A user can select different combinations of selections through sapui5, which is captured and persisted in HANA - I can access these dynamic combination through a view.
My first approach was to join calc view with my selection view. The query has a bad performance, as you can immagine.
When I could use a dynamic projection, the performance would dramaticaly increase.
The problem with APPLY_FILTER is, that it behaves even worse than the join criteria.
The only solution left, to build a dynamic SQL statement would be EXECUTE IMMEDIATE, which needs to persist the resault set, which is not possible in the read only scripted calc view.
Thx a lot!