Microsoft Office Tutorials and References
In Depth Information
FIguRE 4-23 The SQL query designer, with the correct relationships set.
Let us review the relationship definition:
■ The first relationship is between ProductSubcategory and Product. Please note that
ProductSubCategory is the left table, and you want to get all products, even the ones
that contain NULL in the ProductSubcategoryID column. So we need a RIGHT OUTER
JOIN to exist between the two tables, so that the right table (Product) is preserved.
■ The second relationship is between ProductSubcategory and ProductCategory. The
ProductSubcategory table is still the left one. Nevertheless, now you want all the
subcategories, even if they do not belong to a category. So here you need a LEFT OUTER
JOIN type so that the left table (ProductSubcategory) is preserved.
If you now save this query and refresh the table, you get all 504 products in your PowerPivot
database, even those that do not have a category or subcategory set.
Note LEFT and RIGHT outer joins are interchangeable because both types of join save the
rows from one table (the left or right one, depending on the type of join). If you swap the two
tables, you need to change the join type. We personally find that it is better to have only one
kind of join (that is, left or right—usually, left is our preferred one) in a query definition because
it makes it easier to follow the chain. In this example, we would prefer to swap the tables in the
first relationship so that we could follow a chain that starts from Products, then LEFT OUTER
JOINs to ProductSubcategory that, in turn, LEFT OUTER JOINs ProductCategory. Unfortunately,
if you rely on the auto detection algorithm, it chooses the left and right table by itself, making
your work a bit harder.
If you now look at the imported table in Figure 4-24, you can appreciate the fact that it is
already denormalized, and it merged the three tables into a simpler one, which is much
nearer to the logical than to the physical data model.