Technical Articles
SAP IBP Data Extraction via CPI-DS: Are your Filters considered?
Hi Everyone,
We are establishing a new blog series around SAP Integrated Business Planning and Integration via SAP Cloud Platform Integration – Data Services (CPI-DS). You can imagine it is like the “tip of the month” where we will publish a series of posts on how to best integrate data from and to SAP IBP.
This is the 4th blog focusing on troubleshooting IBP Data Extraction. This blog will help you to figure out if attribute- or time-based filters are correctly passed to IBP for Extraction.
Why is that relevant?
There are various use cases for data extraction, being it to archive data in Data Warehouse Systems like SAP BW or to provide Forecasts, Safety or Target Stocks to SAP ERP or SAP S/4 HANA.
Filters play a key role to ensure right data is transferred. However there are quite often questions and challenges in applying the filters. That can result in performance and process failures. Here is how you can check if your filters are applied correctly.
How can you find out if your filter conditions are correctly passed over to IBP?
Let’s start with the CPI-DS Task Monitor. Therefore logon to your CPI-DS Server, select the data flow in question and click on “view history” button.
You’ll see the trace log. It is a pretty detailed log that can have more than 1 page, scroll through page by page and search for “Combined converted SQL statement”
In screenshot below you can see a SQL-like Statement (highlighted). This statement defines the Query that is sent to IBP for the extraction.
Let’s have a closer look at that statement. Therefore copy&paste the marked text to your favorite Text editor tool or Microsoft Word®. For easier understanding replace unnecessary blanks and line feeds. The key part of the Filter is that area highlighted in Red. This includes time- and attribute-based filters:
Now let’s compare the filter conditions in CPI-DS Task Definition to IBP Query. You can find the CPI-DS Filter in the data flow design of the CPI-DS Task. Click on the 1st Target Query step and select TAB Filter.
You can see the highlighted mapping of IBP Query to CPI-DS Filter in screenshot below.
As you can see the Unit of Measure, Currency, Product ID and Periods are filtered.
In this example the time period filter is maintained in CPI-DS Filter as a relative number. In the IBP Query we can see absolute period ids. Period IDs match the IBP Time Profile, you may want to download in the Data Integration App.
Watch out for:
- Missing filters in the IBP Query:
Are there filter attributes missing? - AND – OR Conditions
Is there are chance that they are always true?
If you need support, please raise an incident to component SCM-IBP-INT-HCI with your specific question.
I’m interested on your feedback, please let me know.
Kind regards,
Michael
Cool Stuff Michael,
Not enough technical blogs around IBP, it's always business related. I really like it !
Informative blog to troubleshoot.
It seems that when I use operator IN in filter for calcuated view of IBP, CPI-DS will ignore the in statement.
Yes, that is correct.
For e.g. instead of PRDID IN('X','Y') use (PRDID='X' OR PRDID='Y')
Hi Michael,
for sure IN operator doesn't work. What about LIKE one?
Also (using your example) when I need to have .. AND PRDID > X’ AND PRDID <’Y’ ... it gets converted to OR in the converted SQL.
Could you advise how to overcome this?
Br,
Kiril
hi Kiril,
Unfortunately this doesn't work either. Look at IBP Excel options to build a data view.
You cannot select in Excel selection products like this. You have to explicit mention full name.
You may raise an improvement request in the customer influence portal:
SAP Integrated Business Planning
kind regards
Michael Mack
Hi Michael,
very informative post that helped me to better understand the CPI-DS trace logs. Thank you!
I have one finding regarding relative filters on time periods: I wanted to select the current period only and found out that this filter statements works as desired:
PERIODID1>=0 and PERIODID1<=0
whereas this statement does not work:
PERIODID1=0
I think that is the designed behaviour, right?
Best regards, Matthias
hi Matthias,
Yes, that's the way it works.
see my other blog on time periods:
https://blogs.sap.com/2019/08/21/sap-ibp-data-extraction-via-cpi-ds-how-to-best-filter-data-by-time/
Kind regards
Michael Mack
Hello Michael, thank you for you blog
I have a question regarding the “Combined converted SQL statement”, I’m filtering different from zero AND not null values as:
But in the generated statement the query is different from zero OR null values
All the best
Unfortunately the IS NOT NULL is not pushed down and every <> is automatically replaced by <> OR IS NULL. What should work is < 0 OR > 0. But unfortunately it is only working for one field. A statement like INVENTORY < 0 OR INVENTORY > 0 OR SALESQTY < 0 OR SALESQTY > 0 is interpreted as (INVENTORY < 0 OR INVENTORY > 0) AND (SALESQTY < 0 OR SALESQTY > 0) in IBP. The reason behind is that IBP assumed the conditions are concatenated in a way as in select-options, where conditions for different fields always are concatenated by AND. This will be solved with the new interface for hyperscalers hopefully in future. There IS (NOT) NULL can be pushed down and the concatenation of several conditions is pushed down to IBP in the right way.
Hi Michael Mack
How would one best extract the initial positive record only of a transport receipt (sitting at PRDID, LOCID and LOCFR level) for integration into SAP ECC as purchase requisition.
Please see below screenshots of filters I have attempted but not been successful in getting the answer I need.
Transform 1 and 2 gives me records (no input data error)
Transform 1
Transform 2:
Transform 3 and 4 does not filter at all. Brings all transport receipts of the the planning horizon (what we don't want.)
Transform 3
Transform 4
Your assistance will be highly appreciated.
Thanks and Kind regards
Malebo
Is it possible to filter values based on the values present in a table? I only found individual values can be filtered. What if the values are dynamically taken?