Microsoft Office Tutorials and References
In Depth Information
There is something wrong here: why has PowerPivot lost 209 rows during the loading
process? The reason is that the designer is able to understand that a relationship exists
between two tables, but it is not as good in determining the type of relationship; having
selected the wrong one, the database failed to return all the products. It is definitely time to
understand better what kinds of relationship you can set and how they affect the final result.
When you relate two tables (left and right—in the examples, we use left for Products and
right for ProductSubcategory) you have four different options, listed in Table 4-1. As you
will understand when you study Table 4-1, the big difference among all types of JOIN
relationships is in the handling of NULL values in the left or right table.
TABLE 4-1 Different types of relationships.
Only the rows where there is a match between the left and right table are
returned. In our example, the query returns only the products that have a
subcategory, discarding the ones that have the SubcategoryID column set
LEFT OUTER JOIN
The left table is preserved. This means that rows coming from the left table that
do not have corresponding rows in the right one are returned anyway, leaving
all the columns for the right table as NULLs. In our example, all products are
returned and, if they have no subcategory, the subcategory name column is
left as NULL.
RIGHT OUTER JOIN
The right table is preserved. In our example, we would get all the subcategories,
even the ones that have no products in them. Moreover, because the left table
is not preserved, the products that have no subcategory are not returned.
FULL OUTER JOIN
Both tables are preserved. In our example, you receive all the products and all
the subcategories. Nonreferenced subcategories contain NULL values for the
columns belonging to the product table and, vice versa, products without a
subcategory contain a NULL value for the subcategory column.
If you look carefully at Table 4-1, you can see what happened. The query designer chose the
INNER JOIN type for the relationship between Products and Subcategory, and so discarded
all the products that do not have a subcategory set. In other words, all the products that
contained NULL in the ProductSubCategoryId column are not returned by the query because no
subcategory exists with NULL in the key column.
To load all the products, you need to change the relationships to be an OUTER one. You can
edit the query again by clicking the Table Properties button in the PowerPivot window. Doing
this, PowerPivot opens the query editor again, and from here, you can work again with the
In Figure 4-23, you can see the query designer again, this time with the correct relationships set.