HANA Join Cardinality - Impact?

[Former Member]

Hi,

I was wondering what the impact on the cardinality settings on Attribute and Analytical views are. I am aware of the theory behind cardinalities 1:1, :n, n:1 and n:m. I can imagine what the impact of a "Join Type" (eg Inner, Left Outer etc) would be as the SQL script behind the scenes will use this join type to join the views. However, what is the role of the cardinality? For. e..g, if I get my cardinality wrong but my Join right, would a reporting query on an attr/analytical view bring back wrong results? or will there be just a performance impact? Does the SQL optimizer or the OLAP/Join engine optimize the SQL query generated based on the cardinality rule?

Any thoughts on this would be highly appreciated.

Thanks in Advance,

Anooj

[former_member184768 · Accepted solution]

My two cents:

In many cases, the cardinality representation is related to the logical data model. It depicts the relationship from the master and child table perspective.

From the underlying join point of view, the optimizer will define the driving table and joined table. Depending upon the join (1:1 resulting in Equi join, 1:n resulting in inner / outer join), the optimizer (Rule based or Cost based) will try to optimize the Query generation.

It is recommended that the master table in the join condition should be smaller than the child table, hence the relationship 1:n would be optimized than n:1.

Hope this helps.

Regards,

Ravi

[Former Member]

This message was moderated.

[Former Member]

I'm having confusion with certain cardinalities, especially many to one.

For example, if I have 2 tables. First table has 100 records and second has 10 records, if I use many to one cardinality (let's use inner join), I'm expecting the records to reduce, yet it does not happen.

This is confusing me because the opposite approach increases the record count (1...m).

Anyone have an idea?

[rama_shankar3]

Anooj:

The cardinality and join are complex topics which directly relate to SQL performance.

Here are my thoughts on your questions:

1 - However, what is the role of the cardinality? For. e..g, if I get my cardinality wrong but my Join right, would a reporting query on an attr/analytical view bring back wrong results? or will there be just a performance impact?

Cardinality is used by the SQL engine to  determine the optimal query plan to complete execution of a query.   Yes, the attr / anal view will bring back wrong results (in context to no.of rows returned and performance).    If cardinality is wrong, then your join condition has to be fine tuned.  The performance of the join will be impacted if the cardinality / join is wrong.

2 - Does the SQL optimizer or the OLAP/Join engine optimize the SQL query generated based on the cardinality rule? Yes, the SQL engine uses the cardinality factor

Regards,

Rama

[Former Member]

Thanks Rama - useful info there.

I also found Analytic view data preview throws up an error if you dont get the cardinality right. I had a MD(1):Fact(n) relationship in my Analytic view but because MD table didn't have a primary key and so had duplicate records in it, the relationship 1:n didnt work. I had to remove the duplicates from the MD table to get 1:n (which is the right cardinality) working.

So it appears that at query runtime the cardinality is checked and further the data on both tables are checked to see if the cardinality rule holds true.

Thanks all for the valuable input.

[former_member184768]

Hi Anooj,

It is quite obvious that for the 1:n (Master - child) the relationship has to be defined on the Primary Key and reference key values. By default the PK is unique. If you haven't set the Unique key constraint either with PK or Unique Key, the relationship will always throw error.

On similar lines, when you define the foreign key relationship in reference to Primary key, the check is performed by the database when you try to insert data in the child table. If the reference key value does not exist in the Primary key table, it gives referential Integrity constraint error.

Regards,

Ravi

[Former Member]

Hi Anooj

  As per my understanding it does not effect the no.of records, only the type of join effects result rows ,the cardinality will have impact on performance.

Thanks

Santosh

[Former Member]

Thanks both Ravi and Santhosh.

I have been playing around with different joins and cardinality rules to see if there is either a performance impact or a change in the number of records returned and have not been able to see any difference on both fronts really - could be down to my datasets & quality of data.

I'll assume for now that there will be a performance impact depending on the cardinality rule, shall update this thread if I manage to find anything different.

Thanks again,

Anooj

[former_member184768]

See if you can test it with 2 tables having 10 million and 50 million records each.

Check how the query performance works in the execution plan. It should tell you how the index scan and joins are performed.

Regards,

Ravi