HCI: write back to file (pivoted design)

[Alecsandra]

Hi all,

This short post describes the necessary steps to get data from S&OP (via HCI) in a different format than the standard one.

In order to write data from S&OP to on-premise file, HCI reads data from calculation scenarios. As they are special constructs in Hana db, data will come out with a fixed format:

So each row is corresponding to one Period ID, while the requirement we had was to display data on columns.

Data flow overview:

1. Transform1

Extract the required fields from calculation context using a simple query. Then, in the same query, you would need to add the same number of output columns like in your template file.

e.g: to display KF values for the next 5 periods you have to create 5 output column

Each of this columns will have as mapping:

Period1_KFvalue (current bucket): ifthenelse(SAPSOP.TSTTO >= $G_PERIOD_START AND SAPSOP.TSTFR<= $G_PERIOD_START, SAPSOP.KF, SAPSOP.PERIODID*0)

Period2_KFvalue (current bucket +1): ifthenelse(SAPSOP.TSTTO >= add_months ( to_date ('01' || to_char($G_PERIOD_START, 'mmyyyy'), 'ddmmyyyy'),1) AND SAPSOP.TSTFR<= add_months ( to_date ('01' || to_char($G_PERIOD_START, 'mmyyyy'), 'ddmmyyyy'),1), SAPSOP.KF, SAPSOP.PERIODID*0)

......................

Period(N+1)_KFvalue (current bucket +n): ifthenelse(SAPSOP.TSTTO >= add_months ( to_date ('01' || to_char($G_PERIOD_START, 'mmyyyy'), 'ddmmyyyy'),n) AND SAPSOP.TSTFR<= add_months ( to_date ('01' || to_char($G_PERIOD_START, 'mmyyyy'), 'ddmmyyyy'),n), SAPSOP.KF, SAPSOP.PERIODID*0)

where: $G_PERIOD_START = sysdate();

  2.  Transform 2

Next, you need an aggregation query where you group by the attributes and sum by Period_KFvalue.

3. Target Query

Drag and drop to map input fields to the output.

Review the data flow, run the task, (wait a little) and you have successfully written SOP data to an on-premise file!

Cheers,

Alecsandra