which one will give better performance in calculation view (Join or Union)

[former_member135528]

Hi floks,

I want to let you know that which one will give better performance in calculation view .

a) Calculation view with join

b) Calculation view union

I have large number of records in my transaction table and I want to built calculation view based my transaction table  so, In this scenario which one will give better performance.

could you please some one provide me some insight on this.

[former_member245927 · Accepted solution]

Hi Prasad,

As per Hana Best Practices

• Avoid using JOIN nodes in calculation view. Consider replacing them with UNION nodes (where possible). Alternately consider pushing these joins to Analytic views.
• While using Unions make sure there will be no null values in Measure columns otherwise Union Operation will chock.
• While Unioning measures in Union Node do manage mappings and supply 0 values for null measures columns.
• You can also refer the below link.
  

Thanks

Kala

[former_member135528 · Accepted solution]

Hi chandrakala and Vikram,

Thanks for you people for giving me such an insight and enlightenment's on my question.

Regards,

VaraPrasad.

[Former Member · Accepted solution]

Hi Chandrakala,

Could you please elaborate more on the bad performance of null measures in union? Is that still accurate as of today?

I ran some quick tests on one of my models and I could notice only a very small difference: a fixed gain of 0.1 s / 10 MB. By "fixed gain" I mean that it's not proportional to the size of my queries: 0.1 s is rather good for a 0.5 s query but not so much for a 20 s one... I am using HANA SPS 11.

Did you ever experience a significant performance improvement by replacing the default null measures in union node by constant zeros?

Thanks,

Sylvain

[former_member200930]

Hi Prasad,

As per the SAP recommendations, UNION is answer for your question. Please check enclosed image for the same.

- Vikram Divekar

[former_member208449]

See, UNION & JOIN are to be used for different purposes. JOIN is when you need to pull records from tables having a key field in common. UNION is where you get all the records from 2 or more tables having same number of fields.

According to what I understood, you would be joining the header and transaction tables(which is indeed MUST) and then doing a UNION based on your requirement.

This is really vague. Could you please elaborate on your scenario.