In our experience, most customers have planning data held on spreadsheets, with e.g. ProdLoc or ProdCust by row and a time dimension in each column, similar to the view below;
But! In order to send the above forecast data into SAP Integrated Business (IBP) via Cloud Platform Integration – Data Services (CPI-DS), there can only be a single row per time period, similar to the view below (note that the data is loaded in to a staging table prior to committing to the HANA database);
Obviously such manual reformatting of data every time a load is required for SAP IBP is a waste of planner time and effort.
The Solution
Olivehorse have developed a CPI-DS interface whereby the (as well as some preparatory steps in Excel as well) planners spreadsheet can be interfaced ‘as-is’, i.e. with multiple time dimension columns for each row.
Excel Preparation
The planner’s file needs some slight re-formatting to make it easier to perform the CPI-DS configuration/transformation. Field names are added as the top row and it also helps in CPI-DS if the number formats are consistent so in this instance a number format with no decimals was applied (but could equally have been decimal if needed).
CPI-DS
The next step is to then create the File Format in the CPI-DS FileStore – adding the field names allows CPI-DS to automatically format the structure as you need it. The alternative is to manually add as many time buckets contained in the Planner’s spreadsheet!
Remember also to set the options to tell CPI-DS the first row contains headers
File on Agent Server
The Planner’s spreadsheet needs to be in a location accessible to the DS Agent. There is normally a share created on the Agent Server that you can place files into and this should have been mapped in the DataStore for files.
Task & Data Flow
We now have a file format we can use as a Source for the required Dataflow. But how do we get from the weeks in column format in the input file to the one week per combination per row that IBP requires?
CPI-DS has a special transform called ‘Row Generation’, creating a developer defined number of rows filled with an integer ID you can then use to expand one row into many. Below is shown the overall data flow;
Row Generation Step
This simply creates a table with the amount of rows you need (in this case 78) with one field called D_ROW_ID.
Join Step
This step now brings together our Planner’s spreadsheet with the rows we have generated. The plan quantities are mapped into PLANQTY and we also use a PLANDATE field to hold the date of the start of the week.
We use advanced join and mapping techniques to transform the data from the multi-column format of the input file to the single row per combination/week needed for output to IBP.
Target Query
The final step in the CPI-DS dataflow where we map the interface fields to those in the IBP planning area.
Summary
I have left out the fully defined technical steps required to complete the end to end flow above. If you are an SAP Customer and would like to understand more about the Accelerators Olivehorse can offer your SAP IBP Project, please don’t hesitate to contact me on steve.rampton@olivehorse.com.
Steve Rampton
IBP Practice Lead - Olivehorse Consulting
