Cardinality

[ghostraider]

Hi Everyone,

Could someone tell me what happens if 0..n relationship is modeled as 1..n. I have a model where two tables are joined on both 0..n and 1..n. Modeling 1..n relationships as 1..1 will give wrong counts or summaries but how about about 0..n being modeled as 1..n.
Please let me know.

Thanks

[CognosPolzovatel]

The 0 in the 0:N cardinality represents "optional." 0:N is to be used (versus 1:N) when the built relationship might not necessarily have a match. For example, let's say that you have a person table and a parking pass purchase table. If the rule is that a person MUST have a parking pass purchased (the system driving the table(s) makes sure of that), then this would be a 1 to many relationship. However, what if a person does not necessarily have a parking pass purchased? Let's say that he drives with his aunt, who purchased one for both of them. A bad example, but hopefully gets the point across. In this case, eliminating the "optional" (0) to many relationship: you will eliminate the user in your result. If you had a 0:N relationship placed originally, chances are that it was intentional. Hence, before changing it to a 1:N relationship, I would strongly recommend you analyze the data and the desired results. Moving from 0:N relationship to 1:N, you may lose important data. Hence, chances are you are better off with staying with the original DB design.

[raro]

0...1 Joins are "outer-Joins"
1...1 Joins are "inner-Joins"

http://en.wikipedia.org/wiki/Join_(SQL)


Never use "to-N Joins" if you dont know why!

N-Joins have no representation in SQL!! So "1:n" or "0:n" joins hold additional information for Cognos-SQL-Service.
If you dont know what information this is - you better don't use it!

Using n-Joins in a modell having a circular relationship between (e.g.) four tables, cognos may identify fact tables by the "n" in the relationship; this way cognos-service is able to e.g. seperate one big query into two querys against the fact tables and bring the result together using cognos-logic.

Remember to NOT use "...n-Joins" unless you know that you are doing!!

[blom0344]

0...1 Joins are "outer-Joins"
1...1 Joins are "inner-Joins"

http://en.wikipedia.org/wiki/Join_(SQL)


Never use "to-N Joins" if you dont know why!

N-Joins have no representation in SQL!! So "1:n" or "0:n" joins hold additional information for Cognos-SQL-Service.
If you dont know what information this is - you better don't use it!

Using n-Joins in a modell having a circular relationship between (e.g.) four tables, cognos may identify fact tables by the "n" in the relationship; this way cognos-service is able to e.g. seperate one big query into two querys against the fact tables and bring the result together using cognos-logic.

Remember to NOT use "...n-Joins" unless you know that you are doing!!

Turning a blind eye to setting the right cardinality is not going to do you any good. You really need to grasp the Cognos solution for multifact queries and why cardinality matters so much. There is little use in stopping halfway by going for 0:1  / 1:1 when dealing with facts.
The Cognos logic mentioned is labelled ' stitch-query'  by-the -way..

[rockytopmark]

I have had simple Dimension to Fact relationships, not work at all and instead Cognos generates a stitch query, when set to to 1..1 --> 1..N  (yes, no other relationships defined, etc...)

A colleague of mine suggested I change the 1..N on the Fact side to 1..1 so I thought...  Whatever, worth a shot.  The expected INNER JOIN magically appeared with this change.  We never did truly figure why the difference in cardinality made any difference, but it surely did!!

I wanted to raise a case with Cognos on that one, but since that is rather painful to work through these days, thought better of it.

Incidentally, there are numerous other 1..1 --> 1..N joins in the model that all work as designed, generating an INNER JOIN

[blom0344]

To make matters worse Cognos reworked the way it generates joins between different 8 versions,which means you can not be absolutely sure results will be unchanged after an upgrade.

My guess is , that if the model excludes multi-fact, multi-grain queries then using only 0:1 and 1:1 cardinalities may work just fine. (and no determinants needed to be set that can cause faulty SQL)

[ghostraider]

Thank You All, for you inputs :). My data here is coming from a live database. So if i have relation like 1..1-->1..n, does Cognos automatically assume that 'n' side is the fact and '1' side is the dimension. Because these are really tables directly from the live database. Please let me know.

Rockytpmark, even i was discussing it with a friend of mine and he suggested that i change my joins from 1..n to 1..1.

[blom0344]

Yep, the N side is the red flag for fact in Cognos' thinking  :)