Dynamic source filtering is a big issue in SAP Datasphere. There are several approaches that may work for one, but not for another.
The discussion started again on LinkedIn after I read a post about dynamic filtering that was set with a fixed filter. Wanda then showed us a solution he uses to filter data. But Christopher pointed out that the filter is not pushed down to the source when you use a DP agent, e.g., the ABAP connection.
I had in mind that there was a blog post about how to push a filter down, even on an ABAP connection. So I tried Wanda's idea and combined it with the other knowledge. This blog post will show you how it works.
If you already know s-note 2567999, you know that you can filter data with a stored procedure. And as of June, you can run stored procedures directly in task chains. This is a really nice feature. But back to the other solution.
First, we need a local table to store the load parameter for this approach. The loading table is just to set the parameter easily and not always in the coding.
It is quite simple. 3 columns, one for the application, one for the parameter and one for the value. It may be different on your approach, but keep in mind to adjust the coding as well.
On the local table, we now need a SQL script view with the logic. The coding is simple, as Wanda described in his LinkedIn post.
return select case when APP_NAME = 'APP_1' AND PARAM_NAME = 'TEST' then case when PARAM_VALUE is null then add_days(to_date(now()), -1) else to_date(PARAM_VALUE) end else to_date(now()) END as PARAM_VALUE from "0LT_LOADING_PARAMETER" where APP_NAME = :I_APP_NAME and PARAM_NAME = :I_PARAM_NAME;
We have one return parameter (PARAM_VALUE) and two input parameters (I_APP_NAME and I_PARAM_NAME). That was it. Now the magic needs to be done in a new SQL script view. Based on this post. I tried to combine the logic from Wanda with this approach to push the filter down on a SAP BW Connection. The logic in this example is quite simple.
DECLARE myDate DATE; DECLARE myDateString NVARCHAR(8); select PARAM_VALUE into myDate from "1SV_FUNCTION_PARAMETER"(I_APP_NAME=>'APP_1',I_PARAM_NAME=>'TEST'); myDateString = TO_NVARCHAR(myDate, 'YYYYMMDD'); return select "RECORDMODE","ORT","ARTIKEL","DATE1","QUANTITY" from "ZDWC003" where "DATE1" = myDateString;
What we do is get the date from the function parameter (see above) and convert it to the correct format for the connection date (in this case, YYYYMMDD). Then we display the result.
As you can see, the filter is pushed down when the condition is equal. If I use >= then the filter is not pushed down.
Conclusion
You can see it in the WHERE clause. I hope this helps you a bit and gives you an option how to solve your problem with dynamic filtering. I hope we get a good solution directly from SAP because this is not a small problem. The same should work for replication flows.
The filter push down is necessary for old SAP systems and also for non-SAP systems. We will see what comes in 2025.
author.
Hi,
I am Tobias, I write this blog since 2014, you can find me on twitter, facebook and youtube. I work as a Solution Expert Enterprise Data Warehouse. In 2016, I wrote the first edition of Analysis Office - The Comprehensive Guide. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
Subscribe
- In my newsletter, you get informed about new topics
- You learn how to use Analysis Office
- You get tips and tricks about SAP BI topics
- You get the first 3 chapters of my e-book Analysis Office - The Comprehensive Guide for free
You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?
Then you have to take a look into Analysis Office - The Comprehensive Guide. Either as a video course or as an e-book.
Write a comment