Framework Manager cardinality

[srinu51]

Can some one help me out with framework manager relationships and cardinality. I was really confused when i am supposed to keep 1:1, 1:n or o:n between 2 tables. If any one can explain this elaborately , it will be much help full for me.

[Satheesh]

Hi,
1:1, 1:n It means Inner joins...
0:1, 0:n It means Outer Joins...


I think you know what is Inner Join and what is Outer join...


If you know more You can see the relation meaning while you apply join in Report studio it will clearly tell you as per your condition.


Majorly we apply Inner Joins in rare conditions we go for outer join. By default also Inner join only.


Regards
Sateesh

[blom0344]

Cardinality is used in more than one context. In the Cognos modelling context it mainly deals with the relationship between facts and dimensions. It allows Cognos to define the right query strategy - combined with  determinants - to generate stitched set and compensate for overcounting.

This type of cardinality has nothing to do with the database. The database itself handles the inner / outer join strategy.  The FM documentation has large sections on both topics

[srinu51]

Thanks satish and blom344.

I am not that much good with framework manager cardinality and report studio joins. Can you guys elaborate on report studio joins based on FMW cardinality. The other question which i have is when to keep 1:n on the fact side or 0:n on the fact side, same thing on dimension. I mean how we will decide whether to apply inner join or outer join to facts and dimensions.

[Lynn]

I would suggest you review the FM user guide with particular attention to chapter 10.

Outer joins are generally going to affect performance negatively and shouldn't be necessary if you are modeling a properly designed data warehouse.

Let's assume you have a dimension for Country that contains 4 rows: France, Spain, Germany, and Italy. Further assume you have sales in your fact table for four countries as below.

Country	Sales
USA	300
France	100
Spain	450
Italy	200

The need for outer joins is simply to avoid losing data. Since your country dimension does not have a record for "USA" an inner join would drop USA sales from the result set. Ideally adding records in the dimension to avoid the drop out would be preferred over making an outer join.

If you can't have the missing dimension records added in the database, then an outer join would be needed on the dimension side (not the fact side) so you will get all the facts regardless of whether there is a matching dimension record or not.

If you put the outer join on the fact side you'd be telling the database to give you all dimensions regardless of whether you have facts or not. You'd still lose the USA sales but you'd be including Germany in the result even though you had no sales in that country.

You need to understand and profile your data carefully to understand the joins you need. Having a good working relationship with your DBA can be a big help in this area.

[blom0344]

To add to Lynn's explanation:  it basically comes down on whether the foreign keys on the fact table are nullable or not.  A nullable FK will enforce you to define an outer join for the risk of losing fact-data.  A not nullable FK would imply an inner join, provided the dimension will exist (this should be a matter of proper design)
The best design does not allow nullable FK's of course!!