on 05-23-2016 12:23 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
95 | |
9 | |
8 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.