Framewoek manager CARDINALITY

[beinownow]

Hello,

When building a brand new model in Framework and setting the relationships between the tables (database layer) we follow what has been specified to us by the documentation given.

But there is only reference to what columns to use to join our tables but there is no where description of the CARDINALITY: 1..1 TO 1..N for example.

Is it the framework manager role to guess those ?

Or does the ORACLE table modeleler needs to gives us some type of document or even better diagram showing that ? Or the person that wrote the documentation needs to gives us that ?


Thanks !

[cognostechie]

Are you using Oracle EBS ? 

[beinownow]

I just know we have ORACLE client 11 g

[bus_pass_man]

I'm not sure what you're trying to find out so I'll shotgun out a bunch of stuff.

If you are using default metadata import setting of using primary and foreign keys then the table which is one the FK end will be on the N end of the relationship which is created during import. 

The cardinality of the relationship should be that which matches the data contained in the two tables.  For example if there are one record in one table (table A)  for many matching records in another table (table B for example) then the cardinality of the relationship should be 1.n with the N being on the table B end.

In the relationship editor the cardinality choices that you've made are also written out in the relationship impact section.  This helps make clearer what your cardinality choice is asserting about the nature of the relationship between the data in the two tables.

The concept is derived from math.
"noun Mathematics
noun: cardinality; plural noun: cardinalities

the number of elements in a set or other grouping, as a property of that grouping."

The FM help isn't that bad on the topic actually.

These sorts of concepts are not an esoteric Cognos thing.

[beinownow]

Thank s to both. of you.  By the way cognostechie why the question about ebs ?

[cognostechie]

That's because I already have a FM model built for Oracle EBS so I could have helped you out  :)

The cardinality is confusing to most people when they start learning Cognos. The 1..1 and 1..n does not mean that 1 record of Table A will be joined to many records of Table B. If you see the SQL generated, there will be no mention of how many records are joined to how many, neither in Cognos nor in any other tool which generates SQL. It is a way to tell Cognos which is the Dimension tables and which is the Fact table so that Cognos can generate appropriate SQL so as not to double-count or wrong-count the data.

Consider this:

Date Dimension

Orders Fact
Invoice Fact

In this scenario, the best way to join would be this:

Date  Dimension 1..1 <--> 1..n    Orders Fact
Date  Dimension 1..1 <--> 1..n    Invoice Fact

The actual SQL that is generated is the one when you run the report, not the one you see in FM. When the report is run, Cognos will generate proper SQL and send it to the DB. In this case it will generate one SQL (let's say SQL 1) for Date Dim and Orders Fact with an inner join (without considering how many records are joined to how many on either side) and another SQL (SQL 2) with Date Dim and Invoice Fact with an inner join and then create a 3rd SQL which will then join  SQL1 and SQL2 with a full outer join so that if an Order exists which has not yet been Invoices, the report will still capture that Order.  This is a simplistic example but it is really useful in complicated cases.

1..1   - Dimension Table inner join
1..n   - Fact table inner join
0..1   - Dimension table outer join
0..n   - Fact table outer join
 
Hope it helps.