Microsoft Office Tutorials and References
In Depth Information
FIguRE 4-24 The result of a SQL query is easier to read.
OuTER JOIN and Chains of Relationships
Please note a subtle yet important point about relationships: if you need to make the type
of relationship between Product and ProductSubcategory a LEFT OUTER JOIN, you
definitely need to make the relationship between ProductSubcategory and ProductCategory
a LEFT OUTER JOIN, too. In fact, if the second join is an INNER one, all the rows without
a subcategory are not returned, because when they have no subcategory, they have no
category, either. Whenever you handle chained relationships, if the first one is an OUTER
JOIN, all the rest need to be of the same type.
Even seasoned SQL programmers often fall into this problem and write wrong queries.
Nevertheless, because they are used to making such mistakes, they are used to checking
the query results and double-checking the chain of joins, if any exists. PowerPivot users
need to conform to the same double-checking strategy whenever they write a query.
You have seen that, using the SQL query designer, you can write queries to the database
that transform a simple table into a complex structure, which resolves the relationships
at the database level and returns a denormalized table to PowerPivot. So you can use the
SQL designer to perform denormalization whenever you need to.
When to Denormalize Tables
Now that you have learned two distinct techniques for performing denormalization, here is
an interesting question about data modeling: when do you need to denormalize tables in
queries and when is it better to perform the same operation in PowerPivot? Unfortunately,
as with many interesting questions, there is no definitive answer for all situations.
In the example of ProductCategory, the answer is simple: it is much better to denormalize the
column in a query because this operation leads to an easier data model to query and lowers
the number of tables in PowerPivot. The sole purpose of the ProductCategory table is to
provide a description of product categories, and so when you fulfilled this requirement by