Cardinality and its effect on SQL

[Cognos_World]

Hi,

Suppose, we have a order header (dimension) and a order line (fact),and instead of giving cardinality as 1..1 and 1...n respectively, if we give 1...1 and 1...1, will the result change? And if the result changes, then did the SQL change?

Thanks,

CW

[MFGF]

In a simple query involving just Order_Header and Order_Detail, the results and the SQL will be the same. However, if you were to bring in values from a different Fact in the same query, having your Order_Detail defined at the 1..n end of a relationship would (correctly) identify it as a Fact and result in a Stitch Query, whereas if it is at the 1..1 end of a relationship it would be (incorrectly) identified as a DImension and you would not get the desired Stitch Query generated.

Regards,

MF.

[cognos3]

I set the Auto group and summarize property to 'No' but its still not showing the duplicates,

Should I change the cardinality of fact(1:1)-----Dim(1:1) in framework manager to Fact(0:n)------Dim(1:1)?

[MFGF]

I set the Auto group and summarize property to 'No' but its still not showing the duplicates,

Should I change the cardinality of fact(1:1)-----Dim(1:1) in framework manager to Fact(0:n)------Dim(1:1)?

You need to set the cardinality of your relationship to 'n' at the Fact end of your relationship to allow the query engine to identify it as a Fact query subject, but generally 1..n is ok (it's unusual to have dimension members with no facts, and where these do exist, users often want to ignore them). The cardinality will not affect whether grouping and summarization is performed, though. What do you mean by "its still not showing the duplicates"? Can you describe what you're trying to achieve?

MF.

[wyconian]

MF is of course right :-) Thought I would just share this though.

I've been working on a number of what I would call 'performance' warehouses/models where the main purpose is to record work flow rather than to aggregate values. In these cases I've developed a number of dimensions and 'factless' facts i.e. facts without any measures. The purpose of these is to merely join the dimensions together.  In these cases it has been useful to 'fake' the cardinality into the fact to get round issues caused by stitch queries.