1-1 vs 1-N Joins in Framework

[raj_aries81]

Hi All,

I am just wondering what difference does it make if I change the join from 1-1 to 1-n. Both represent an inner join and from a Framework Manager standpoint 1-1 is dim and 1-n is fact. However, I would like to understand the how Cognos generates the SQL if I change 1-1 to
1-n. I assume both should be inner joins in Cognos generated query. Will I see any difference in report o/p and the way Cognos writes the query if I make this change.

Regards
Raj

[MFGF]

Hi All,

I am just wondering what difference does it make if I change the join from 1-1 to 1-n. Both represent an inner join and from a Framework Manager standpoint 1-1 is dim and 1-n is fact. However, I would like to understand the how Cognos generates the SQL if I change 1-1 to
1-n. I assume both should be inner joins in Cognos generated query. Will I see any difference in report o/p and the way Cognos writes the query if I make this change.

Regards
Raj

Potentially you may see a huge difference in some situations. Cardinalities are used in determining fact or dimension roles for query subjects. A query subject is treated as a dimension if it is at the 1 end of ANY relationship used in the query, whereas a query subject is treated as a fact if it is at the many end of ALL relationships. If the query engine determines there are multiple facts in a query, it creates something radically different - a stitch query. It assumes that there may be facts in each fact table that don't have corresponding facts in the other, so you end up with a full outer join, and (what it sees as) the dimensional items in common to them are coalesced to remove nulls.

If you were really reporting off two fact tables at the centre of star schemas (or snowflake schemas) in a data warehouse, sharing conformed dimensions, this is exactly the right thing to do to get accurate, consistent results from both facts. If you are not, and have just messed with cardinalities for the sake of things, you may get completely inappropriate, inaccurate results returned.

Cheers!

MF.

[raj_aries81]

Potentially you may see a huge difference in some situations. Cardinalities are used in determining fact or dimension roles for query subjects. A query subject is treated as a dimension if it is at the 1 end of ANY relationship used in the query, whereas a query subject is treated as a fact if it is at the many end of ALL relationships. If the query engine determines there are multiple facts in a query, it creates something radically different - a stitch query. It assumes that there may be facts in each fact table that don't have corresponding facts in the other, so you end up with a full outer join, and (what it sees as) the dimensional items in common to them are coalesced to remove nulls.

If you were really reporting off two fact tables at the centre of star schemas (or snowflake schemas) in a data warehouse, sharing conformed dimensions, this is exactly the right thing to do to get accurate, consistent results from both facts. If you are not, and have just messed with cardinalities for the sake of things, you may get completely inappropriate, inaccurate results returned.

Cheers!

MF.

Thanks MFGF.
In my model I have a query subject with 1-N on one side and 1-1 on another side, when I created a report using this query subject and the data was not accurate ....when I saw the Cognos generated query, I observed that XSUM clause being applied instead of SUM however the join is still an inner join. I changed the cardinality from 1-N to 1-1 and noticed that XSUM got disappeared and SUM is applied and the report was showing up the right data.

Is this something occured due to 1-N Cardinality. I didn't realise that 1-N would result in an XSUM. There were no determinents applied though.

[MFGF]

Thanks MFGF.
In my model I have a query subject with 1-N on one side and 1-1 on another side, when I created a report using this query subject and the data was not accurate ....when I saw the Cognos generated query, I observed that XSUM clause being applied instead of SUM however the join is still an inner join. I changed the cardinality from 1-N to 1-1 and noticed that XSUM got disappeared and SUM is applied and the report was showing up the right data.

Is this something occured due to 1-N Cardinality. I didn't realise that 1-N would result in an XSUM. There were no determinents applied though.

XSUM is an extended summary, and often resolves to a SUM in the native SQL if query complexity and database capabilities allow. You will see XSUM in the Cognos SQL and SUM in the native SQL in this instance.

You haven't told us enough about your model and the items you are selecting to allow us to answer your question here. Are you bringing in items from data source query subjects or model query subjects? How are the item usages defined? How many query subjects in total? How many items in total? How many other relationships are being used? Is the relationship you refer to here between data source or model query subjects, or a mixture? Are you testing in a report or in FM?

MF.

[raj_aries81]

Hi MFGF,

Sorry for the delayed reply, I was on a vacation.

I have a model something as below - 

           Table E(1..1)
             |
             |
             |(1..1)   
Table B(1..N)<---->(1..1)Table A(1..1)<--->(1..1)Table C
                                              |(1..N)
                                              |
                                              |
                                              V
                                     Table D(1...1)

Does 1..N for Table A is somethine that needs to be taken care of inthe above model

Are you bringing in items from data source query subjects   - No, am using model Query Subjects
Are the item usages defined? - Most of them are attributes
How many query subjects in total? - 5 Query subjects
How many items in total? - Simple report with 5-6 columns
How many other relationships are being used? - 2-3 joins
Is the relationship you refer to here between data source or model query subjects, or a mixture? - No, relationships are just between model query subjects.
Are you testing in a report or in FM? - I generated the query in report. However, looks like there is no XSUM being applied in FM ( not very sure though, need to recheck)


Thanks & Regards
Raj


                                           

[raj_aries81]

Hi MFGF.

One correction...Its the relationship between A <->E not B<->E, somehow I could not put that in the message

Regards
Raj

[raj_aries81]

Hi All,

I am just wondering what difference does it make if I change the join from 1-1 to 1-n. Both represent an inner join and from a Framework Manager standpoint 1-1 is dim and 1-n is fact. However, I would like to understand the how Cognos generates the SQL if I change 1-1 to
1-n. I assume both should be inner joins in Cognos generated query. Will I see any difference in report o/p and the way Cognos writes the query if I make this change.

Regards
Raj

Can I use 1..1 & 0..1 instead of 1..N & 0..N unless I want to treat it as a fact table to get the stitched queries and extended aggregation?.

Is that a fair assumption..?

Regards
Raj

[Penny]

Interesting article regarding modelling 1:m as a 1:1 to achieve minimized sql.  See paragraph 'Model for predictable results'.

http://www.element61.be/e/resourc-detail.asp?ResourceId=748

[raj_aries81]

Interesting article regarding modelling 1:m as a 1:1 to achieve minimized sql.  See paragraph 'Model for predictable results'.

http://www.element61.be/e/resourc-detail.asp?ResourceId=748

Thank you for your help Penny.

"It is better not to condense the snowflake using a model query subject. Instead, model the snowflakes with 1:1 relationships. Tables in the snowflake can be joined using 1:1 relationships instead of 1:n relationships."

So in a single fact model where we dont need any stitched queries, we can change the hierarchical relation ships to 1..1 instead of 1..N

Eg: Instead of modeling Product Line ----<Product Type-----<Product, it would be good to model
Product Line ----Product Type-----Product

Please let me know if my understanding is correct.

Regards
Raj

[raj_aries81]

Thank you for your help Penny.

"It is better not to condense the snowflake using a model query subject. Instead, model the snowflakes with 1:1 relationships. Tables in the snowflake can be joined using 1:1 relationships instead of 1:n relationships."

So in a single fact model where we dont need any stitched queries, we can change the hierarchical relation ships to 1..1 instead of 1..N

Eg: Instead of modeling Product Line ----<Product Type-----<Product, it would be good to model
Product Line ----Product Type-----Product

Please let me know if my understanding is correct.

Regards
Raj

In Cognos 10.2.2 a new usage property for a query subject have been introduced that differentiates between a bridge table and Fact table with 1..N

Regards
Raj