Table joins

[Former Member]

Hello,

We read that it is possible to have joins on HANA level or in the Business Objects level. What are the advantages\disadvantages of having the joins in BO and not on HANA? . Does running a query above analytic view it use all the star schema joins or only the one used by the query? Is there a different in the way the query does the joins when running a query above analytic view and calculation view?

Thanks,

Amir

[Former Member · Accepted solution]

At 'BO layer' I'm assuming you're referring to universe/semantic layer? If so, SQL is generated and passed to HANA, so the join is still executed in HANA. However, you can generally expect much better performance by modeling joins (and all other aspects of your data such as calculations) in an Analytic View (or Attribute View / Calc View depending on your use case).

Hope you're not talking about doing joins at actual client-level (i.e. like you can do in Crystal Reports).

On a previous project we had very complex reporting requirements with a high demand for autonomous system design - another one of the tradeoffs - so we compromised with some joins modeled in analytic views, and others in a universe. We sacrificed some performance for increased reusability - i.e. more autonomous ad-hoc report building in WebI on these universes.

[Former Member · Accepted solution]

Hi Judy,
What I meant by "BO Layer" is to do the joins on the universe level.
We obviously prefer to do all the joins on the DB level and not in IDT but I understood that if we want to join tables with "between" SQL (for example join between an info object and its Q table) it is better to do it in IDT and not in HANA model. So I wonder what are the disadvantages\advantages to do the joins in IDT or in HANA? 

Thanks,
Amir

[Former Member · Accepted solution]

1) It's Jody, not Judy.

2) Joins in IDT just generate SQL that gets sent to HANA (or any other DB) so the actual join is always done at DB level.

3) You can generally expect joins to execute faster when modeled in Attribute/Analytic Views as compared to SQL. That's the advantage of join in HANA.

4) If you have two fact tables with shared dimensions, say SALES and PROJECTIONS which share dimensions STORE and PRODUCT, you have a few options. You could create two analytic views, and then a Calc View that merges data via UNION node. OR you could do all modeling at universe level. Modeling joins at universe level, creating contexts, etc - generally leads to more maintainable solution / autonomous system for business users. Doing all joins/union in HANA leads to better performance but less autonomy for end-users wanting ad-hoc reporting.

Hope that helps - lots of exceptions and details to the above, but that's the general situation. The challenge is to find the right balance of trade-offs.

[Former Member]

HI Amir,

• Performing joins in analytical view will be faster than joins in your idt because analytic view is designed to perform joins as it directly calls Olap engine present in sap hana database which is fully optimized to perform joins .
• Apart from this hana databse will give you power of in memory computing which will definetely help you to perform joins between two datasets with good performance, where in BO you will not have all such leverages in terms of performance.
• One more advantage in creating analytic view is ...whenever you execute query on analytic view it first creates its optimized execution plan ..means the best way of querying on view ..to get data from it...

Due to all above factors i will always go for joins at my analytic view....:)

Thnkss

Kulwinder

[Former Member]

Amir ,

By taking all the advantages of IMC we are processing all calculations & Manipulations in Database layer simply we are moving required data to application layer. This way you are going get huge performance & avoiding bottlenecks.

In traditional databases calculations use to takes place in application layer which was bottle necks in the past.

Regards,

Mahesh

[rindia]

Hi Amir,

It is better to have joins in HANA modeling among multiple tables or Attribute views, and create Analytic or Calculation view.

By doing this you can eliminate joining  tables in building universe and can use HANA view in Information Design Tool (IDT) or can use directly HANA views in Explorer or any other BO BI tool.

Regards

Raj