A planner’s Excel sheet loaded to SAP IBP using CPI-DS

Posted by Steve Rampton on 06-Jul-2018 07:05:01

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;

ibp01

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);

ibp02-1

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).

ibp03

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!

ibp04

Remember also to set the options to tell CPI-DS the first row contains headers

ibp05

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;

ibp06

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.

ibp07 

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.

ibp08

Target Query

The final step in the CPI-DS dataflow where we map the interface fields to those in the IBP planning area.

ibp09

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


Ask us about any SAP IBP queries you have or about our range of IBP services

Read more on: SAP IBP, CPI-DS, Integration Services