- SAP Managed Tags:
Problem description
When integrating data via Remote Table in SAP Datasphere, filtering is limited. The limitations depend on the complexity of the SQL the DPAgent is able to push down to the source. An often seen requirement is to add a filter where the data should be filtered dynamically. This Blog shows a workaround to achieve a dynamic filtering.
For this use case, we use the ABAP Table MSEG which is not delta enabled. The MSEG table contains information about materials whenever a material movement occurs. We want to determine all materials that have a "Best-Before date" which is exactly 200 days in the future. For that, we want to add a dynamic filter on the"Best-Before date" field, which is called "VFDAT". It is being loaded into Datasphere as a String(8) in the format YYYYMMDD.
Inside the remote table itself we can only define a hard filter on a specific date. What we want instead is a dynamic filter so the integrated data changes as time goes on.
1st approach
The easiest approach is to build a view on top of the unpersisted remote table. The SQL can look like this:
select "MANDT","MBLNR","MJAHR","VFDAT" from QS4_MSEG where "VFDAT" = ADD_DAYS(CURRENT_DATE, 200)
The result set consists of 3 records. But when looking at the Remote Query of this View, the WHERE clause is not being sent to the source. The we get 3 records in the end but there are 34,516 records moving between the source system and Datasphere. So Datasphere queries the entire dataset and then filters afterwards to show the correct result because it is unable to push the WHERE clause to the source, as it is too complex.
When working with huge amounts of data, we want to limit the amount of data moving between the systems.
For that, we need to find a way to push the WHERE clause to the source.
2nd approach
We can do so by declaring variables in an SQL Script view. The SQL Script can look like this:
DECLARE myDate DATE = ADD_DAYS(CURRENT_DATE, 200);
DECLARE myDateString NVARCHAR(8) = TO_NVARCHAR(myDate, 'YYYYMMDD');
return select "MANDT","MBLNR","MJAHR","VFDAT" from QS4_MSEG where "VFDAT" = myDateString;
Instead of trying to push the complex WHERE clause, this script resolves the WHERE clause first when declaring the variable. The result set consist of the same 3 records as before, but when looking at the remote query, we can see that there WHERE clause is being pushed. It cannot visualise the WHERE clause in the remote query, so a "?" is being displayed but we can see in that only 3 records are being pulled.
Conclusion
It is apparent that the query from approach 2 is faster and leaner than the query from approach 1. It is possible that the data volume requires the leanness and pace of approach 2. This seems like a valid workaround for all use cases that depend on a dynamic filtering when integrating data via remote table.
8 Comments