Microsoft Office Tutorials and References
In Depth Information
Note You might wonder why PowerPivot is so good at finding relationships during the loading
of tables and did not detect the relationship between Product and ProductSubcategory, even if
this relationship is already stored in the database metadata. The reason is that, during loading
of data, PowerPivot searches for relationships among the tables it is currently loading, ignoring
tables that are already present in the PowerPivot data model. These other relationships (between
existing and new tables) need to be detected later, through the relationship-detection algorithm.
Now that you have empowered the PowerPivot data model with these two tables, you easily
can produce complex reports like the one shown in Figure 2-27, in which we mix columns
from products, categories, subcategories and orders, letting PowerPivot resolve the complex
relationships that make the browsing process possible.
FIguRE 2-27 Sample report with categories and subcategories.
The report, as it looks now, is quite nice. Nevertheless, because you are surely striving for
perfection, you notice a couple of small issues:
■ Both tables (ProductCategory and ProductSubcategory) have the same description for
the column name, as you can see in the Row Labels list in Figure 2-27. This is not very
user friendly because it is hard to understand whether you correctly put subcategory
under category or vice versa (apart, clearly, from the rule of common sense as soon as
you see wrong data).
■ ProductCategory and ProductSubcategory are separated from the Products table, even
if they are strictly related to products. In a small pivot table like this one, this is not a big
issue. However, as the data model gets larger, you should try to reduce the number of
tables shown to the user as much as possible, to make it easier to find the columns. A rule
of thumb in the Business Intelligence world dictates that you should never browse more
than 15 different tables. If you let tables spread at a speed of one table per lookup, you
reach that limit very quickly.