· 

Dynamic filter push down in SAP Datasphere

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.

Structure of loading table in SAP Datasphere
Structure of loading table in SAP Datasphere

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.

SAP Datasphere connection overview
SAP Datasphere connection overview
Push down works, see where statement
Push down works, see where statement
Result preview of remote table to see the amount of entries match
Result preview of remote table to see the amount of entries match

As you can see, the filter is pushed down when the condition is equal. If I use >= then the filter is not pushed down.

Push down failed
Push down failed

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
* indicates required

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

Comments: 0