Joins and Cardinality Demystified

1. Joins and CardinalityDemystified Elizabeth Snow-Trenkle Rocky Mountain Cognos User Group Meeting May 17, 2013

2. Topics • Introduction to Joins • 1..1 “The Inner Join” • 0..1 “The Outer Join” • Classifying the “Fact” Table • 1..n “The Fact Table” • 0..n “The Outer Fact Table” • Considerations

3. Introduction to Joins • What is cardinality within Cognos? • Relationship between tables: • Traditionally, inner and outer joins • Cognos introduces Fact Detection

4. Underlying Tables Consider the following tables:

5. Requirement/Goal This report shows Patient Id with associated Charges and Payments.

6. 1..1 “The Inner Join” Returns rows when there is at least one match in both tables.

7. 1..1 “The Inner Join” Expected Actual

8. 1..1 “The Inner Join” Examine the SQL select Patient.PatientId as PatientId, XSUM(charge.ChargeAmt for Patient.PatientId ) as ChargeAmt, XSUM(payments.PaymentAmt for Patient.PatientId ) as PaymentAmt from Test.Test.dbo.Patient Patient, Test.Test.dbo.charge charge, Test.Test.dbo.payments payments where (Patient.PatientId = charge.PatientId) and (Patient.PatientId = payments.PatientId) group by Patient.PatientId Will count 2x payments because there are 2 charges.

9. 0..1 “The Outer Join” Returns all rows from the left table in conjunction with matching rows from the right table. If there are no matching columns in the right table, the outer join returns NULL values.

10. 0..1 “The Outer Join” Expected Actual

11. 0..1 “The Outer Join” Examine the SQL select Patient.PatientId as PatientId, XSUM(charge.ChargeAmt for Patient.PatientId ) as ChargeAmt, XSUM(payments.PaymentAmt for Patient.PatientId ) as PaymentAmt from Test.Test.dbo.Patient Patient left outer join Test.Test.dbo.charge charge on (Patient.PatientId = charge.PatientId) left outer join Test.Test.dbo.payments payments on (Patient.PatientId = payments.PatientId) group by Patient.PatientId Will count 2x payments because there are 2 charges.

12. Classify the “Fact” Table • Center of a star schema • Only facts and keys; attributes come from dimension tables • Multi-Fact queries are possible, but require a conformed dimension. In this case, Patient is the conformed dimension.

13. 1..n “The Fact Table” Expected & Actual

14. 1..n “The Fact Table” • Why does changing from 1..n to 1..1 sometimes resolve the issue? • If you want a report that shows only patients with Charges ANDPayments • Why does changing it from 1..1 to 1..n sometimes resolve the issue? • If you want a report that only shows patients with Charges ORPayments

15. 1..n “The Fact Table” • Examine the SQL in Report Studio-- (It’s too long!) • The Fact Table creates two inner join “queries,” then performs a full outer join “query” between the two.

16. 0..n “The Outer Fact Table” Expected & Actual

17. 0..n “The Outer Fact Table” • Examine the SQL in Report Studio -- (It’s too long, again!) • The Outer Fact Table creates two outer join “queries”, then performs a full outer join “query” between the two.

18. Considerations Tuning: • Indexes should be evaluated based on the sub-queries they create • Ignore the very misleading FM Relationship Impact description: • 1..n/0..n means MUCH more and has BIGGER impact/ramifications

19. Conclusion • Which do we use – 1..1, 0..1, 1..n, 0..n? • Know the reporting requirements • Dimensional data warehouses should typically leverage 0..n or 1..n cardinality • Know the data, how to present it, and the capabilities

20. Questions?