HCI DS - Data loading for Forecast / Future period

[Former Member]

Hi,

How do we load data for future periods using HCI?

E.G.

We get data for KF1 @ Prod Loc Level for Current Month

P1 L1 100 2015-08-01 00:00:00

How do we populate this data for next 12 months.

I need 11 more records.

I don't want to create 11 new dataflows where I keep changing the month.

I was going through some of the predefined templates of Inventory / Demand data where I do see we get variable value for future period in preload script.

But how do we use it in postload script.

Thanks,

Purav

[Alecsandra · Accepted solution]

Hi Purav,

You could use add_months(original_date, months_to_add). It will add the given number of months to your date.

For example, original_date is $G_LOAD_DATE and months_to_add takes a value from 0 to 11.

add_months($G_LOAD_DATE, 0) will return 2015.08.06

add_months($G_LOAD_DATE, 1) will return 2015.09.06

.........

add_month(add_months($G_LOAD_DATE, 11) will return 2016.07.06

You would need a place to store the months_to_add. If your data flow is reading from a file you could have a separate file with one column and the values you need.

Regards,

Alecsandra

[Former Member · Accepted solution]

Hi

Data flow is reading data from ECC or APO.

I didn't get what do you mean by place to store.

Do I need to create extra column in dataflow transform? If yes then not sure how will this work. Add months will be part of preload script right??

Thanks,

Purav

[Alecsandra · Accepted solution]

Hi Purav,

In my case, beside the source file I was using another one where I had a column with values from 0 to 23. Both files were input in the target query (join without condition).

KEYFIGUREDATE mapping syntax: add_months($G_LOAD_DATE, n) where n was the column from the second file. So for each row of the initial file another 23 were generated for the coming months.

As you are reading from APO, you would need these value in a different table that you could join with yours.

Regards

Alecsandra

[Former Member · Accepted solution]

Hi,

I don't see a buying from customer for creating additional column or Z table only for this.

Problem is I can lookup values in FF for APO Dataflow but cannot join.

As of now, a solution suggested may work as follows.

Create APO DataFlow as per requirement.

The generated ABAP file / prog which we load in APO system for transporting to QA & PRD, can be enhanced to give multiple record.

While using this prog to extract data from QA environment, I think we should be able to get multiple records using ABAP logic.

Any thoughts / comments on this?

Thanks,

Purav

[Alecsandra · Accepted solution]

Hi Purav,

I think I know how to solve this with a nice workaround that just came into my mind...if you would like to try it out.

In your dataflow add a new intermediate query <Transform> but instead of dragging any field in it, just create one output column <Counter>. Map the <Counter> with gen_row_number().

Add this <Transform> as input in the target query so that you have a no condition join with your transform. Add a filter <Counter> < 12. Then map the KEYFIGUREDATE with add_months($G_LOAD_DATE, Counter).

Regards,

Alecsandra

[Former Member · Accepted solution]

Couple of Solution for above problem:

1) As mentioned by Alecsandra, get and additional column.

     a) If data source is using FF, then create additional file with numbers from 0 to N(Forecast period)

      b) If data source is SAP system (APO / BI), we have 2 options.

          i)  Create Z table with 1 column and specify 0 to N

          ii)  Use 0CalMonth or 0CALDAY object to get 1 to 12 or 1 to 31 as a unique entry by developing split logic to get month / date. This is not the cleanest way / good design, but this also works if we dont have to develop any new Z table.

2) Enhance ABAP prog which is transported from D to Q to P for each ABAP DF.

Add logic of generating records for future. Strong documentation should be kept with project owner / stakeholder so that in case of enhancement in ABAP DF during support activities, prog is always appended with a prog which enables generating records for future months.

Thanks,

Purav