HCI DS Data Flow conversion

[Former Member]

Hi mates,

I'm extracting data from a Hana Database and importing it in IBP.

The problem I'm having is that for the Keyfigures in the database are grouped by document and in IBP I need to imported them grouped by month.

So for example I have

PORT	DATE	REVENUE
PORT1	05-05-2016	10
PORT1	15-05-2016	11
PORT1	07-06-2016	13
PORT2	05-05-2016	15
PORT2	22-05-2016	17

So What I need to obtain is:

PORT	MONTH	REVENUE
PORT1	05	21
PORT1	06	13
PORT2	05	32

I'm trying to use global variables to append the values while I read the rows, but I wasn´t able to assign values. The "=" is only used for comparissions:

$GlobalSUM = $GlobalSUM + "TEST"."REVENUE"

Does anybody knows how to do this?

Another alternative is also welcome.

Thanks,

R.O.

[Alecsandra · Accepted solution]

Hi Robert,

I would extract the month from the date and use an aggregation query to group by month and sum by volume.

https://hcids.hana.ondemand.com/DSoD/help/index.html#8d164f078d59499d99736fcbe50ca8f4.html

Regards

Alecsandra

[Former Member · Accepted solution]

Hi Alecsandra,

Thanks for your reply.

I don't have the Group By tab in my transformation view.

I'm about to upgrade my agent (now it's on version 1.0.11.2430) and see if that fix this issue.

I'll update the post after it.

Thanks for your help.

R.O.

[Alecsandra · Accepted solution]

Hi Roberto,

You need to select Aggregation Query, instead of simple.

You can check out of the box templates delivered by SAP, more specifically SOP_KF_Actuals - Extract Actuals key figure data from SAP ECC and load to S&OP. You will see how an aggregation query is being used.

Regards

Alecsandra

[Former Member · Accepted solution]

Hi Alecsandra,

I don't have the aggregation query option:

Thanks,

R.O.

[Alecsandra · Accepted solution]

Yes, you have it. The 3rd one.

[Former Member · Accepted solution]

Ups...

Excellent!

This was really helpfull.

And after the Aggregation Query I always have to have a Query step, right?

Thanks a lot Alecsandra,

R.O.

[Alecsandra · Accepted solution]

Maybe an extra clarification why me printscreen looked a bit different than yours.

In my case, as I was reading from ECC, more query types were enabled: ABAP Query,ABAP Aggregation and ABAP Custom.  You can use them when you prefer to have the transformation executed on the ECC side, rather than handled by the agent.

A possible solution in order to group by month: you have to create an output column in the simple query and extract the month from the date using date function month(x). Then input the outcome of theis simple query into the aggregation query so that you can easily group by the month number.

Good luck

Alecsandra

[Former Member · Accepted solution]

Ok perfect.

I have one more doubt. In BW I have the information from 2013 till present. But what we need in IBP is just the last 6 months.

Based on this question from the IBP FAQ's:

"

Q: Multiple Tables: How efficient is HCI at performing runtime logic involving multiple tables?

A: When joining multiple tables, HCI will generate an ABAP program (for ERP) or a SQL statement (for databases) so that the processing of the joins is pushed to the source. So performance depends on your source system, not on HCI. If you would join files it's a bit more tricky because HCI-DS can't push down this and needs to do the join in memory on the Agent machine. So for files, HCI-DS recommends NOT to join large files.

"

So if in the Query I filter by date (in the Filter tab, using a function), this filter is made on the database SQL statement or it's made on HCI?

Thanks again,

Roberto Ostuni

[Alecsandra · Accepted solution]

If you use ABAP query then transformation will be done inside SAP application according with the filtering conditions you specify. The transformed data is sent to HCI.

With the simple query it will be handled on HCI side.

https://hcids.hana.ondemand.com/DSoD/help/index.html#be2b7a49306f4c868f8654e9bedee72b.html

[Former Member]

When I run this kind of maapings I get:

Parameter <($GlobalSUM = ($GlobalSUM + ("TEST"."REVENUE")))> of function <ifthenelse> must be a value expression, not a comparison,

                                                               which uses one of { =  >  <  <>  >=  <= }.