SAP IBP Data Extraction via CPI-DS: Are your Filters considered?
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.