Skip to Content

Use case

There is a common requirement in IBP implementations to integrate data available in daily buckets from an operational system like S4 into technical weeks in IBP for planning at a higher level.

There are different ways to transform and load data from Days into Technical Weeks. I’ve looked at the available options and none are as efficient as the one below, therefore I would like to share the syntax in this post with you.

Example

Assume an example where we have the Actuals at Product, Customer and Day level in S4 and we would like to load in IBP into a Key Figure defined at Technical Week level.

In this case I’m using CPI-DS (HCI-DS) to generate a ProductCustomer combination joined with 100 rows of Date fields starting with the date of today to create a simple example.

 

In the Join query I have created 2 additional Fields called CWN and Month where I have calculated the Calendar week number in year and respectively the Month number in year. As we can see in Calendar week 44 there is a month split where we would like to group data into separate technical weeks and load them to IBP.

To achieve this we can use the syntax below in the mapping of the Date field.

"Join"."Sysdate" - least( day_in_week("Join"."Sysdate"), day_in_month("Join"."Sysdate") ) + 1

This will default every daily entry to the first day of the technical week as we can see below.

When loading to IBP, based on the aggregation rules defined by business, now we could either Select distinct or SUM the values using an aggregation query, group by technical week and load the Key Figure to IBP at its base planning level.

Hope this helps,

Cosmin

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Nazeer Khan

    Hello Cosmin,

    Conversion of date to Technical Week, can we be achieved by single code like below.

    Assumption, I’m expecting the input date in yyyy.mm.dd format.

    ifthenelse(

    substr(Transform1.RequestDate,9,2) < substr(to_date(Transform1.RequestDate,’yyyy.mm.dd’)-(day_in_week(to_date(Transform1.RequestDate,’yyyy.mm.dd’))-1),9,2),

    to_date(substr(Transform1.RequestDate,1,4) ||’.’|| substr(Transform1.RequestDate,6,2)||’.01′,’yyyy.mm.dd’),

    to_date(Transform1.RequestDate,’yyyy.mm.dd’)-(day_in_week(to_date(Transform1.RequestDate,’yyyy.mm.dd’))-1))

    with regards,

    Nazeer

     

    (0) 
  2. Thomas Kretz

    Hi Cosmin,

    alternatively, you can aggregate the daily values to weeks in CPI-DS (HCI-DS) and have them disaggregated to technical weeks during import to IBP. To use this approach, you need to set the global variable $G_TIME_PROFILE_LEVEL to the time level of the loaded data (e.g. the weekly time level in this example). This is particularly relevant if you have customer specific period weights defined in IBP.

    Kind Regards,
    Thomas Kretz

    (0) 
    1. Cosmin Pop Post author

      Hi Thomas,

      Thanks, this sounds really good. I did not see this method being implemented yet so I see 2 drawbacks at the moment but please correct me if I’m wrong:

      1. Dissagregation in IBP is possible from Weeks to Days but not from Weeks to Technical Weeks. Not 100% sure since I work mainly in CPI but I’m told there is no direct relationship like this in the time profile.
      2. Second concern, even if Point 1 is possible I would still loose the exact distribution of daily values to technical weeks as provided by S4 while doing the agregation to calendar week before loading. This can be fixed afterwards indeed with period weights but the distribution most likely will not be as exact as aggregating to technical week before loading.

      Best regards,

      Cosmin Pop

      (0) 
      1. Thomas Kretz

        Hi Cosmin,

        Disaggregating key figure values from Weeks to Technical Weeks is possible in IBP and a frequently used modelling approach. You mainly need to add a technical week level in the time profile and upload period weight factors defining the length of the technical weeks. You may have a look at the time profiles of the sample planning areas SAPIBP1 or SAP6 where this approach is used. There are also two related chapters in the IBP documentation

        • Aggregation and Disaggregation Across Different Time Levels (Link) and
        • Configuring Aggregation and Disaggregation of Data Across Different Time Profile Levels (Link)

        Your second concern is of course correct. If you disaggregate key figure values from calendar weeks to technical weeks in IBP, you will always use the period weights defined in IBP as proportional factors. If the data you load deviates from this distribution it may make more sense to do the aggregation to technical weeks before loading them to IBP.

        Kind Regards,
        Thomas

        (1) 
        1. Cosmin Pop Post author

          Great, thanks for sharing. I will need to try it once.

          When I was looking for this info on forums I could only find bits and pieces.

          Now both pre- and post-load aggregation methods are described in one place.

          Best regards,

          Cosmin

          (0) 

Leave a Reply