How to model scenario in HANA

[former_member215819]

Hi All,

We have a scenario described as below:

Summary DSO

Cost center     Period/Yr      Amount     Order

C1                    001/2014     100               O1

Line Item DSO

Period/Yr     Order     Prev Order     Amount

001/2014     O1          O2               60

001/2014     O1          O3               40

001/2014     O2          O4               30

001/2014     O2          O5               30

001/2014     O3                             40

Our final requirement is as below:

Cost Center     Period/Yr     Order 1      Order 2      Order 3     Amount

C1                    001/2014     O1            O2               O4          30

C1                    001/2014     O1            O2               O5          30

C1                    001/2014     O1            O3                #           40

Need about 6 iterations of line item DSO to get 6 order level

Each of the above DSO's have about 100 Million records and we would need data for atleast 2 years in report

We were thinking to do this as part of on the fly reporting using calculation view with a SQLScript as below :

- create Attribute views joining to two DSO's and get the first iteration

- SP with sql on the Attr view to get basic data set

- then additional join to Line item DSO to get further iterations

- Planning to put the sql in Calc View

Concerns

1) Performance wise calculation view via sqlscript does not give good results

2) CE functions - unable to use them much since we would have to store intermediate results in tables. This causes out of memory dumps due to large volume

Please provide your thoughts or views on best to model such a scenario in HANA. Appreciate your help!

Regards

Vidya

[aadityanigam]

Hello Vidya,

I also have a similar solution see if this could help ...

Warm regards

aadi

[former_member184768]

Hi Vidya,

In my opinion, for such complex requirement, you should write a transformation and store the data in the required format in another DSO. If performance is the key concern for you, then memory consumption should not be that much issue.

Hence I'd suggest to persist the data in the required format and ensure that the performance is better / as expected.

Regards.

Ravi

[former_member215819]

Jody, krishna, Ravindra, Raj

Thanks for all your replies

Still looking into what can be done for best performance.

1) There is an existing flow in BW to map this requirement but only till 2 iterations. The data load takes really lot of time. For a delta update of 1Million records udpate to this DSO - it takes anywhere from 8 - 10 hrs.

Yes, the BW code can be fine tuned probably - but we would need to add 4 more iterations and thats why we thought of HANA.

2) Yes, we do need some amount & other KF calculations where aggregations by certain characteristics is required. Hence we cannot complete entire solution on the view level

3) We are thinking of doing calculation in HANA - persisting it in a table and then reading this in CV using sql Script  > expose to BW via Virtual provider and reporting from BW front end.

Reason is  - all Master data exists in BW itself and we are building the HANA model in same BW-HANA DB.

Will let you know what was the approach taken finally.

Thanks again !

Regards

Vidya

[Former Member]

I believe you'll need to aggregate the AMOUNT column on the report, right? If so, Attribute Views won't work. You'll get wrong results.

Also, if that table is very large, I would not recommend doing self joins. Performance will suffer.

Based on the example you provided, you have a parent-child hierarchy embedded in a fact table, but you only want records for the leaf nodes of your hierarchy.

Not sure if Data Services is part of your landscape, but assuming it is, this is what I would recommend:

1) Move the hierarchy into it's own table.

2) Maintain leaf nodes in your fact table for the join.

3) Create an Attribute View and model the parent-child hierarchy.

4) Join to fact table in an Analytic View.

5) Use any front-end tool that consumes via MDX, such as Analysis for Office, to flatten out the hierarchy on-the-fly.

A bit more clumsy is to flatten out the hierarchy yourself in a separate table, join on leaf nodes, and then you can model for any client without the need for MDX's hierarchy support.

Should help all of your performance issues.

(Not a BW guy so I just read "table" from your discussion - not sure how much flexibility you have in creating new target table structures to do what I recommended)

[former_member182302]

Hi Jody,

I think it must be a detailed report with out any "Aggregations" ( Assumption though )

Just mentioned an approach to see if my understanding of the requirement is correct. But agree with you on having a SELF-JOIN multiple times will hamper the performance (Though can be better than having nested queries) , To apply your approach, since this is a BW DSO, she should be able to apply transformations in BW itself and get the data in the required format.

But still would want to see the performance stats on those volumes of data using self joins ( to understand the power of HANA )

Love this place, Even when trying to contribute ,we will acquire knowledge on different approaches and views for a single problem.( Free consulting + Training )

I hope Vidya responds to this thread, after these many replies i couldn't see her response.

Regards,

Krishna Tangudu

[former_member182302]

Hi Vidya,

I think this requirement is purely based out of Joins and can be implemented using Attribute View.

SQL Equivalent:

select 
A."PERIOD",
B."COSTCENTER",
A."ORDER 1" AS "ORDER 1",
A."ORDER 2" AS "ORDER 2",
A."ORDER 3" AS "ORDER 3",
A."AMOUNT"
FROM
(
select 
A."PERIOD",
B."ORDER" AS "ORDER 1",
A."ORDER 1" AS "ORDER 2",
A."ORDER 2" AS "ORDER 3",
A."AMOUNT"
from 
(
select 
A."PERIOD",
A."ORDER" AS "ORDER 1",
A."PREVORDER" AS "ORDER 2",
A."AMOUNT"
from "BEST"."LineItemDSO" A
INNER  JOIN "BEST"."LineItemDSO" B
ON ((A."ORDER" = B."PREVORDER") AND (A."PERIOD" = B."PERIOD"))) A
LEFT OUTER JOIN "BEST"."LineItemDSO" B
ON ((A."ORDER 1" = B."PREVORDER") AND (A."PERIOD" = B."PERIOD"))) A
INNER JOIN "BEST"."SummaryDSO" B
ON ((A."ORDER 1" = B."ORDER") AND (A."PERIOD" = B."PERIOD"))

Attribute View:

Output:

So if we can achieve everything with Attribute view, why are you considering other models?

And also we can achieve this using UCV's as well

Also you might want to have a look on these 2 blogs as well: ( Adding to blog shared by Raj )

Do try in this way as you have considerable amount of data and let us know the performance stats.

Regards,

Krishna Tangudu

[rindia]

Hi Vidya,

I am unclear about how you derived your values in your final requirement.

Looks like a transpose concept. Could you please refer this link Table Transpose in SAP HANA Modeling.

If this is not the one, can you please elaborate with another example.

Regards

Raj