Skip to Content
Technical Articles
Author's profile photo Michael Mack

SAP IBP Data Extraction via CPI-DS: How to best filter data by time?

Hi Everyone,

We are establishing a new blog series around SAP Integrated Business Planning (IBP) 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 tip of the month 2, in case you missed the previous one find here the link.

When extracting data from SAP IBP time filters are effective to reduce the amount of data. However filtering is not that easy because the CPI query uses different period types than you can see in IBP. Here is how it works in context of unified planning SAPIBP1?

First we will focus on some basics about time periods, how they are handled in the Integration context and how to find out technical identifiers of time periods. Afterwards this blog will explain how to setup filters in CPI Jobs.

Introduction on Time periods

At beginning some basics about the time periods. In the IBP Planning area you can see what Time Profile and periodicities are available. In the example of using Unified Planning area it has following periodicities:

However in the CPI data flow it looks like this.

What is the link?

PERIODID’s are mapped to the time profile level by following logic. The most granular is ID 0 and most aggregated one is ID 1. From ID 1 in a descending order the levels are numbered. See below what it means in the example.

PArea Time Periodicity CPI Name
Day PERIODID0 (Lowest Time Profile Level)
Week (technical) PERIODID5
Week PERIODID4
Month PERIODID3
Quarter PERIODID2
Year PERIODID1 (Highest Time Profile Level)

How to find out technical identifiers of time periods?

Goto IBP Fiori Launchpad – open the Data Integration App. Click on Download Templates and select Time Profile of the Planning Area.

Open it in Excel and add Filters.

Result for ID of Q1-2019

 

How to filter time periods? What are typical scenarios? How to setup CPI jobs?

The following section outlines 5 different scenarios. Scenario 1 has detailed screenshot that apply for all 5 scenarios.

All scenario follow an example using Consensus Demand Key Figure that has for Customer 4711, Product SUPERPHONE a value of 365 in Week 1 of the year. Base Planning level is WKPRODCUST.

  1. Extract weekly bucketed data for current year
  2. Extract weekly bucketed data starting 2 years in the past to current year
  3. Extract daily bucketed data for current and next 2 month
  4. Extract daily bucketed data for Q1-2019
  5. Extract daily bucketed data for Q1-2019, Q2-2019 and Q3-2019

 

1. How to extract weekly bucketed data for current year

Lets have a simple data flow, one source – one target – one transformation step.

Step 1: Mapping of attributes and key figures in CPI transformation step

Input Output comments
PERIODID4 PERIODID Map attribute PERIODID4 to PERIODID to get data on weekly level.
TSTFR FROMDATE It is very important that TSTFR and/or TSTTO is part of transformation step. This is needed for the correct filter pushdown to the IBP Calculation Scenario.
TSTTO TODATE

Step 2 – filter definition for current year

Therefore you we will use periods relative to current period. This allows you to specify an interval relative to the current period. Only “>=” and “<=” operators are supported in conjunction with “AND”.

For example, as said above in this Planning Area Year is attribute PERIODID1.

(PERIODID1 >= 0 AND PERIODID1 <= 0)

This filter will extract key figure data for years 2019. The current year is PERIODID1 value 0 (in this example 2019).

To test the data flow run the Job.
It has just 1 record extracted as you can see in the screenshot below.

Review Results (in this case it is downloaded to a CSV file)

Compare this to exact same group by / filter criteria in IBP Excel or IBP ANALYTICS.

2. How to extract weekly bucketed data starting 2 years in the past to current year

Step 1: Mapping of attributes and key figures in CPI Transformation step

Input Output comments
PERIODID4 PERIODID Map attribute PERIODID4 to PERIODID to get data on weekly level.
TSTFR FROMDATE It is very important that TSTFR and/or TSTTO is part of transformation step. This is needed for the correct filter pushdown to the IBP Calculation Scenario.
TSTTO TODATE

Step 2 – filter definition

Therefore you will use periods relative to current period.

For example,

(PERIODID1 >= -2 AND PERIODID1 <= 0)

This filter will extract key figure data for years 2017, 2018, 2019. The current year is PERIODID1 value 0 (in this example 2019).

3. How to extract daily bucketed data for current and next 2 month?

Step 1: Mapping of attributes and key figures in CPI Transformation step

Input Output comments
PERIODID0 PERIODID Map attribute PERIODID0 to PERIODID to get data on daily level.
TSTFR FROMDATE It is very important that TSTFR and/or TSTTO is part of transformation step. This is needed for the correct filter pushdown to the IBP Calculation Scenario.
TSTTO TODATE

Step 2 – filter definition

Therefore you will use again periods relative to current period. However now you’ll use a different attribute: PERIODID3 (month).

For example, month is attribute PERIODID3.

(PERIODID3>= 0 AND PERIODID3<= 2)

This filter will extract key figure data for years 19-Aug, 19-Sep, 19-Oct. The current month is PERIODID3 value 0 (in this example 19-Aug).

4. How to extract daily bucketed data for Q1-2019

Step 1: Mapping of attributes and key figures in CPI Transformation step

Input Output comments
PERIODID0 PERIODID Map attribute PERIODID0 to PERIODID to get data on daily level.
TSTFR FROMDATE It is very important that TSTFR and/or TSTTO is part of transformation step. This is needed for the correct filter pushdown to the IBP Calculation Scenario.
TSTTO TODATE

Step 2 – filter definition

Therefore you will use specific period Id of the periods. See here how to figure that out.

This option is used when you know the period id of the periods for which key figure data is to be extracted. Only “equals” operator, i.e. “=”, is supported in conjunction with “OR”. This means if you need to extract data for several periods, then you have to specify each one of them using the “equals” operator.

For example, PERIODID2 is quarter.

(PERIODID2 = 400020)

This filter will extract key figure data for Q1 2019.

5. How to extract daily bucketed data for Q1-2019, Q2-2019 and Q3-2019

Step 1: Mapping of attributes and key figures in CPI Transformation step

Input Output comments
PERIODID0 PERIODID Map attribute PERIODID0 to PERIODID to get data on daily level.
TSTFR FROMDATE It is very important that TSTFR and/or TSTTO is part of transformation step. This is needed for the correct filter pushdown to the IBP Calculation Scenario.
TSTTO TODATE

Step 2 – filter definition

For example, PERIODID2 is quarter.

(PERIODID2 = 400020 OR PERIODID2 = 400021 OR PERIODID2 = 400022)

This filter will extract key figure data for Q1 2019, Q2-2019 and Q3-2019

 

What’s next?

  • In a follow-up blog we will detail out how to handle key figures on different planning levels

 

I’m interested on your feedback, please let me know.

Kind regards,
Michael

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Cosmin Pop
      Cosmin Pop

      Thanks for sharing Michael. I like how the blog series is written and explained. Looking forward to reading more. I started using PeriodIDs to filter recently instead of on TSTFR and it’s a lot easier and much more performant although I still use the other approach in some use cases.

      If you have any experience with writing back to S4, I would be interested the read a blog post on what is the most performant method for the writeback (odata, soap, bw target). I have used all 3 and find BW to be the quickest so far.

      Cheers,

      Cosmin Pop

      Author's profile photo Reinhard Sudmeier
      Reinhard Sudmeier

      Hi Cosmin,

      I don't know the performance of BW targets, but direct RFC calls, which are supported now are much faster than oData and SOAP, especially if you use TABLES parameters or maybe fast serialization. TABLES parameters are marked as obsolete, but with classic serialization they are much faster than IMPORTING TYPE <table type>. IMPORTING LIKE <row type> is not supported by CPI-DS at all.

      Best regards, Reinhard

      Author's profile photo Kiril Todorov
      Kiril Todorov

      Dear Reinhard,

      Could you position File and DB options in this ranking (oData, SOAP, etc.)? What would be the best choice for C2C integration (both directions)?

      Best regards,

      Kiril

      Author's profile photo Bhanu GK Tiruveedula
      Bhanu GK Tiruveedula

      Thanks Michael for the detailed explanation on how to filter using PERIODIDs.

      Waiting for your next blogs ?

      Author's profile photo Sandesh Kurumella
      Sandesh Kurumella

      Great post Michael. What would be your idea to apply filters dynamically? What I mean is - Sometimes I may need to filter on PERIODID3 and sometimes PERIODID4. As CPI-DS allows to apply filter only on one attribute, it would be tricky to define it dynamically.

      Author's profile photo Reinhard Sudmeier
      Reinhard Sudmeier

      How about defining filters for both and for the one you don't want to filter on define a value that does not really filter anything?

      PERIODID3 >= -500 AND PERIODID4 >= 0

      or the other way round

      PERIODID3 >= -4 AND PERIODID4 >= -500

      I'm not sure if this works, but could be worth a try.

      If you want to calculate both values from one single input parameter you could use the preload script for that.

      Author's profile photo Rahul Kumar
      Rahul Kumar

      Thanks for sharing this!! Michael Mack , I am not able to find the next post after this one, Can you please reply me the link for that. Thanks!!