Joins and Cardinality Demystified Elizabeth SnowTrenkle Rocky Mountain

Joins and Cardinality Demystified Elizabeth Snow-Trenkle Rocky Mountain Cognos User Group Meeting May 17, 2013

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 2

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

Underlying Tables Consider the following tables: 4

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

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

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

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

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. 9

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

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

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. 12

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

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 AND Payments 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 OR Payments 14

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. 15

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

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. 17

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

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 19

Questions?