Help needed with implementation of BI scenario in HANA

[former_member102099]

Hello Everyone,

We have a scenario in normal SAP BI which we want to implement in HANA and see if we achieve any improvement .

Scenario:

Currently we have a source S1 which gives sales against profit center e.g.

company code	Profit center	sales	month
C1	P1	1000	4

We have another table T1 where these same values are split along multiple profit centers and ratio are stored e.g.

Profit center	Company code	sales	month	split ratio
A1	C1	600	4	3
A2	C1	400	4	2

The report shows the values as . The values in the source S1 is split on profit center line based on T1.

company code	Profit center	sales	month	split ratio
C1	A1	600	4	3
C1	A2	400	4	2

Current solution which we have: We refresh all the records from S1 into the target multiple times a day which consumes lot of time and resources.

The amount of data in S1 is huge and historical records needs to be adjusted everytime any ratio changes in T1.It takes hours to load the data currently in BI

Requirement:

We need to replicate the same scenario in Hana based on projections etc. and understand how fast these refresh will be and how can we implement it.

Thank you for your suggestions/Solutions.

Please reply back if the question is not clear.

[SarhanP · Accepted solution]

Hi Ajeet,

There are many options or ways to improve your BI landscape by HANA.

1. ERP on anydb -> BW on HANA: your query results will improve 20-100 times upon scenario. However data extraction will only improve apprx 20% if you have standard extraction, but DSO activation will improve dramatically, thanks to coloumn store and HANA optimized re-design of fact and dim tables.Also you can replicate data table to table from ERP to BW with no latency by SLT

2. ERP on HANA -> BW on HANA : Addition  to all improvements in 1st option you have chance get real time data without replication, by using analitic and calculation views. Simply creating a remote cube based on HANA views.

Just make a quick search in googe "BW on HANA" or goto

Cheers,

sarhan

[Former Member]

what is the link between S1 and T1

how do you know A1 and A2 belong to P1?

P1 is profit center group and A1/A2 are profit center. So ideally, T1 should have entries like below

PG PC Ratio Month Sales

P1  A1  3       4          600

P1  A2  2       4          400

Please confirm that.

Regards,

Sandeep

[former_member102099]

Hi Sandeep,

Yes your understanding is correct.

[Former Member]

t1

cc pg sales month

c1 p1 1000  4

c1 p2  500  4

t2

pc pg sr

a1 p1  6

a2 p1  4

q1_t1 = ce_column_table("t1",["cc","pg","sales","month"]);

q2_t2 = ce_column_table("t2",["pc","pg","sr"]);

q3 = CE_LEFT_OUTER_JOIN(:q1_t1,:q_t2,["pg"]);

t3

pc pg sr cc sales month

a1 p1 6  c1  1000  4

a2 p1 4  c1  1000  4

    p2    c1      500 4

var_out = ce_projection(:q3,["pc","pg","sr","sales",CE_CALC('if(isnull("pc","sales","sales"*"sr"/100,decimal(5,2)))') as "new_Sales","month"]);

var_out

pc pg sr cc sales new_sales month

a1 p1 6  c1  1000  600             4

a2 p1 4  c1  1000  400             4

    p2     c1   500    500            4

Create CV with sql

Begin

q1_t1 = ce_column_table("t1",["cc","pg","sales","month"]);

q2_t2 = ce_column_table("t2",["pc","pg","sr"]);

q3 = CE_LEFT_OUTER_JOIN(:q1_t1,:q_t2,["pg"]);

var_out = ce_projection(:q3,["pc","pg","sr","sales",CE_CALC('if(isnull("pc","sales","sales"*"sr"/100,decimal(5,2)))') as "new_Sales","month"]);

end;

Regards,

Sandeep

[Former Member]

Any Updates?

[Former Member]

Hello Ajeet,

Greeting of the day,

Good question, my suggestion would be following:-

1) Get the HANA view which shows the Sales per profit center (should be standard) and then copy this and create a calcualtion view with your ratios

2) Or you can use the HANA live content and to get the  ratio, use another table and put the processing logic in the reporting tool of BO.

3)In case you are on S/4 system, the source S1 should be updated real time (need to have embedded BW) and then use this.

Hope this helps, personally i like the option 1,

Check Sales Analysis Fiori app and the back end view it is using

You should get your answer

Regards

Rishab