SAP IBP Data Extraction via CPI-DS: How to extract key figures on different planning levels?
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 3rd blog focusing on reading data from different planning levels.
Say I’d like to extract following 2 Key figures:
|Key Figure||Base Planning Level|
|CONSENSUSDEMANDQTY – Consensus Demand Plan Qty||Product | Location | Customer |Week|
|ADJUSTEDTRANSPORT – Adjusted Transport Receipts||Product | Location | Ship-From Location | Week|
What happens if you disregard this topic?
Data spaghetti – planning data that is from different planning levels is dumped into Files or Target Structures.
If you look at this data at IBP Excel and compare it you’ll learn:
- The data has NULL values for attribute combinations it doesn’t fit into.
- For ADJUSTEDTRANSPORT the data is aggregated. I.e. it is a summed-up value of all sources if there are multiple source locations supplying location 6210.
- You will run into a data extraction issue if large data sets are read from IBP.
How to extract key figures on different planning levels? How to best do this step by step?
Start off in IBP Excel and keep in mind how you set up the excel sheet. Latest IBP Excel client guides you with tooltips and interactive checks what’s possible what not. E.g. if you select periodicity day and then move on to Key Figures section – you will not be able to select Consensus Demand Plan Qty Key Figure because it is on Planning level week.
Don’t forget to set UoM and Currency Filters.
Move on to Filter section and define a small set of Materials to get fast results in IBP Excel.
Look at your IBP Excel sheet and confirm that this is how you want to see it extracted via CPI-DS.
Check - Time Periodicity - Attributes needed - Key Figures needed - UoM and Currency Filters - Attribute Filters (e.g. Locations, Product Groups, …)
Now logon to CPI and design the data flow for it.
First create a CPI data store if that doesn’t exist yet. In case of a CSV File download it looks like this:
Use same attribute Names and data types as in the IBP planning area. Same attribute names allow auto-mapping in Data Flow design time in CPI.
Second create a new data flow and map Key Figures in transformation step.
Third define the filter using same attributes like in IBP Excel. Make sure you select time periods correctly.
Now for extracting the additional planning level(s) you may have a separate data flow or you can also model it as another step of same task. I’m using several steps in a task as long it is easy to keep an overview.
When executing this job you’ll see following in the CPI Monitor Log:
And here are the resulting records:
First define what Key Figures you are really interested on. Easiest way to discuss and define is to download all KFs of the planning area into an Excel file. This can be done in the Planning area APP.
Once you have identified the required Key Figures you can start grouping them by Planning level and create then for each packet a Data Source entry in CPI (in case of File download) or similar in connected source system.
If you don’t know the exact attribute type or field length check the IBP Master data app/ Planning area download.
- In a follow-up blog I will detail out what are the different attribute-based filter conditions?
I’m interested on your feedback, please let me know.