- SAP Managed Tags:
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.
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.
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.
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
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.
Lets have a simple data flow, one source - one target - one transformation step.
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.
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.
Therefore you will use periods relative to current period.
For example,
This filter will extract key figure data for years 2017, 2018, 2019. The current year is PERIODID1 value 0 (in this example 2019).
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.
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).
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.
This filter will extract key figure data for Q1 2019.
For example, PERIODID2 is quarter.
This filter will extract key figure data for Q1 2019, Q2-2019 and Q3-2019
What’s next?
I’m interested on your feedback, please let me know.
Kind regards,
Michael
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.
- Extract weekly bucketed data for current year
- Extract weekly bucketed data starting 2 years in the past to current year
- Extract daily bucketed data for current and next 2 month
- Extract daily bucketed data for Q1-2019
- 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
7 Comments
| User | Count |
|---|---|
| 8 | |
| 7 | |
| 5 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
