Skip to Content

Hi All..

I am explaining importance of cardinality setting between the join and changing the cardinality impact.


  • Cardinality is a property of a join that describes how many rows in one table match rows in another table. and it’s expressed as the minimum and maximum number of rows in a column at one end of a join that have matching rows in the column at the other end of the join. The minimum and the maximum number of row matches can be equal to 0, 1, or N.


  • If joined two tables (Customer and City) without defining the cardinality and to know what is the default cardinality consider the join . And if we change the cardinality  will it impact the results.

Below are the Data Base tables:

     Customer Table Data                                                             City Table Data

Customer Table.pngCity Table.png


Cardinality Un-Know

Joined two tables and Specified the Cardinality as Un-know and returned total number of rows are 12.


UnKonw.png

Cardinality one-to-one (1,1)

Joined two tables and Specified the Cardinality as one-to-one (1,1) and returned total number of rows are 12.

One to One.png

Cardinality one-to-many (1,N)

Joined two tables and Specified the Cardinality as one-to-many (1,N) and returned total number of rows are 12.

One to Many.png

Cardinality many-to-one (N,1)

Joined two tables and Specified the Cardinality as many-to-one (N,1) and returned total number of rows are 12.

Many to One.png

Cardinality many-to-many (N,N)

Joined two tables and Specified the Cardinality as many-to-many (N,N) and returned total number of rows are 12.
Many To Many.png

So cardinality with Un-Know or one-to-one (1,1) or one-to-many (1,N) or  many-to-one (N,1) or many-to-many (N,N)  will get the same results. And cardinality of a join does not have a role in the SQL generated when you run a query.

But we need to defining cardinality for all the joins in data foundation to detect the context. which are mainly detected by the CARDINALITIES. So as per best practices cardinalities are set according to the key status of the column in the two tables for all the joins in data foundation as follows


First Table.png

Hope this helpful.

Thanks,

Sreeni

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Prosser

    Cardinalities never have affected SQL generation in Business Objects. That has never been their purpose. Their main functionality within Designer is to facilitate the detection of contexts – indeed the context detection algorithm cannot work without all cardinalities being set.

    In addition to this, never use the detect cardinalities function – this assumes perfect data and can take a long time and may still not be correct. You are better off using a data schema or talking to the database designer.

    Cardinalities also provide a visual aid to help others understand the schema; as such, you should get into the discipline of setting the cardinality of the join each time you create one. Self-joins should be set to a one-to-one cardinality.

    (0) 
    1. Sreenivasulu Dasari Post author

      Hi Mark

      In my blog I am highlighting if we change join cardinality results and generated SQL not effect, and I didn’t say use the detect cardinalities function. And finally I said to detect the context need to specify cardinality for all the joins.

      (0) 

Leave a Reply