Hi,
Going through the various existing topics on Cardinality, I come to understand that it is better to set the cardinalities manually for the joins, based on the Data Model.
I have recently made an enhancement in a Universe, and added new fact table. Before setting the cardinalities manually, did an auto-detect to study the behavior.
Each of the facts has a relation with one of the Dimension tables based on 2 columns, i.e. 2 joins with same dim. The existing fact tables, on auto-detect gave the correct 1:N cardinality. But when performed for new fact table, for each of these 2 joins, it gave N:N… Though I have set the correct cardinality manually, was just wondering WHY!
Cardinality detection runs 3 queries of this type:
select count(*) from table A
select count(*) from table B
select count(*) from table A, B where …join between A and B…
Based on the 3 numbers it gets, Designer decides what the cardinality of a join can be.
As you can see, it’s just an estimate. If you have a fact table that is not populated right now or has only a sample of data, it can happen that the fact table has less rows than a dimension table and the cardinality detection algorithm gives incorrect result.
So, it’s always better to set the join cardinalities manually than to depend on the above algorithm.
(BOB member since 2003-11-27)
(BOB member since 2011-01-18)
.
(BOB member since 2005-01-06)