Modelling in HANA

[Former Member]

Hi ,

We are doing a modelling in HANA with 3-4 Calculation views that are combined into one final Calc view by joining it with attribute Views /Analytical views as explained below:

1. CA_View1(combination of Attribute view & Analytic View)

2. CA_View2(combination of Attribute view & Analytic View)

3. CA_View3(combination of Attribute view & Analytic View)

4. AT_View1

5. AN_View1

The above 5 Views are combined to form 1 Calc view with specific Join condition (CA_View1+CA_View2+CA_View3+AT_View1+AN_View1).

We need to access the final calc view in BO and need to create a dashboard on that.

Questions:

1. Will HANA support Complex modelling as explained above combination of different views into one?

2. Is it better to do modelling in BO layer by accessing simplified Views/tables from HANA?

3. If we do modelling in HANA , is der anyway to do kind of a integrity check that can be done in HANA to check the loops or any errors similar way what we do in BO?

4. If we create one calc view as explained above and access it in BO layer and do a integrity check , does it detect loops , how does the integrity check happens? beacuse the Calc view will be like a Columnar table/View for HANA.

Please suggest the best way to implement the above scenario.

Regards,

Nagaraj

[rama_shankar3]

Nagaraj:

Below are my thoughts: hope it is useful.

1. Will HANA support Complex modelling as explained above combination of different views into one?

     My thoughts: HANA will support such modelling but this may not work efficiently,  which beats the purpose of using HANA for modelling.

2. Is it better to do modelling in BO layer by accessing simplified Views/tables from HANA?

     My thoughts: Personally, I suggest that you do not put any heavy lifting in the BO layer since depending on your BO hardware you might create a bottleneck and beat the purpose of using HANA in the backend. I suggest that you model your data outside HANA to handle most of the complex logic - Remember HANA's power lies in the hardware (MPP - CPU utilization) and In-memory columnar storage. HANA is not the best modelling tool yet when compared to tools like SAP BW 7x. Infact, you should consider modelling in BW if your BW is runnning on HANA DB.

3. If we do modelling in HANA , is der anyway to do kind of a integrity check that can be done in HANA to check the loops or any errors similar way what we do in BO?

My thoughts: there are basic expecption handling where you will get blank or # data when a join is invalid but this may not be a good way to model a solution.

4. If we create one calc view as explained above and access it in BO layer and do a integrity check , does it detect loops , how does the integrity check happens? beacuse the Calc view will be like a Columnar table/View for HANA.

     My thoughts: refer to my response to Question#2 above.

Please suggest the best way to implement the above scenario:

     My thoughts: Options for you to consider: 1 - Try to request your source system owners who provided you based  data to handle most of the complexity or 2 - Use BW on HANA DB for modelling

Hope this helps.

Regards,

Rama

[Former Member]

Hi Nagaraj KS,

there are even more options to be considered:

You have considered an SQL View as basis for Your Query ?

Up to our Performance tests also an SQL View, Create View ..... is much faster

than Analytical or Calc. Views.

Nevertheless Codeing in SQL is more flexibel.

There is a Reasons to use Calc. Views:

- Sript Calculation View (SQL Script, CE Functions) , what means
an PLScript Interface to programm the

underlying Datamodel of a Calc. View.

Please check the Paper:

Building Advanced Data Models with SAP HANA , Werner Steyn.

There are main two Reasons to use Calc. or Analy Views:

-Restricted Key Figures. They are in SQL Views hard to code as Unions

-Interface to SAP BW as Transient InfoProviders

Best Martin

[former_member184768]

Hi Nagaraj,

Is there any reason why the first three calc views CA_View1, CA_View2 and CA_View3 are created. Are they expected to be used in some other model / universe. Why can't the Analytic views used in these be combined together in a single calc view.

Secondly are these need to be joined together or should be in Union. If they need to be joined, is there a possibility that you extend the underlying base tables and add the redundancy. That will reduce the need to join and improve the performance as you are aware that the join operation is more expensive in HANA.

I am not sure if BO tool will be able to perform the checks for HANA models as the HANA models form the basis of data model in BO and the checks are performed on the data model in BO.

I think you should try all the options and check the performance before making the final design.

I am sure this is quite a complex design you are looking for and I'll be glad to hear your experience on the same.

Regards,

Ravi

[Former Member]

Hi Nagaraj,

You try to put a complex logic and integrity checks into the query.

Integrity checks and loops should have be done in the data integration layer.

If  this is not possible, another approach is what is callled an architected datamart:

You put the data (via PLScript or other ETL)  with the integrity checks and

into a Table (Tables)  and set the Views on top of this.

Best Wishes
Martin

[Former Member]

Hi Nagaraj,

Your model sounds extremely complex! For every layer you add within your HANA model performance is impacted. Calculation views call the calculation engine which is slower than the join engine normally used with attribute views and the OLAP engine used with Analytical views.

Please keep in mind it is best practice to us the union rather than join method at the calculated level.

You mention you are using join and that to me doesnt sound quite right.

While having your model in HANA takes advantage of the in-memory and CE engine bringing the complexity of any calculations down to the database level you still have to watch out for performance impacts.

While I cant directly answer your questions as I do not know all the details behind what you are doing I would suggest doing a quick test in HANA first. EG Best is to run data outputs at each of the levels and get a feel for where you might be able to improve performance. Why are you creating calculation views which join attribute views to analytical views? Can you create your joins within the analytical view itself? EG join your attribute views to the data foundation of teh analytical view?

Bring logic and joins down a level will improve performance and instead of creating relationships between CA views you can create unions between analytical views. 

Test your scenario using a simpler model. See what the performance impact is one level at a time. Do you need all the joins?

As for loops Im not aware of a way to detect loops other than unit testing the build and cross referencing it to the tables in the source system and reviewing the output. perhaps another person can help to answer this for you.

I recommend determining if you can simplify the model as this sound very complicated to me and Im not sure it would be wise to build such a layered intense model in HANA.

Can you explain more about what you are trying to do?

Kind regards,

Danielle