Microsoft Office Tutorials and References
In Depth Information
These kinds of relationships, which appear very often in the database world, are called
chained relationships because they form a chain that you can follow from the beginning
to the end to relate many tables.
Note Please note a curious phenomenon that often appears in the world of databases. Even if
we plan to slice data by category first, then by subcategory, and finally by products, following a
very natural path, in reality the chain of the relationships is reversed, starting from the more
detailed table and going into the less detailed one. This is absolutely normal—it concerns how
data is modeled in relational databases. Throughout the topic, we discover many other
relationships that need to be read in this reversed way.
To make PowerPivot allow you to slice data with the columns of these new tables, you need
to import them into your data model. To do that, it is enough to repeat the loading process
you did before to import the first three tables. This time, instead of using the From Database
button, you should use the Existing Connections button, which is located on the Design tab of
the PowerPivot ribbon, as you can see in Figure 2-26. You can do this because the connection
to the database has been already saved inside the Excel workbook and you can now use it to
import all the useful tables without needing to create a new connection. You can find this
example, with the tables already loaded, in the workbook CH02-04-NewTables.xlsx.
FIguRE 2-26 The Existing Connection button opens a connection to a previously used database.
You already know that during the loading process PowerPivot detects the relationship
between Subcategory and Category. Moreover, you also know that you need to hide
technical columns (ProductSubcategoryID, ProductCategoryID) and to remove the
useless ones (rowguid and UpdatedDate in both tables) to create a clean data model.
Now, if you try to add Category or Subcategory to the PivotTable, PowerPivot detects the
need for new relationships and, when asked to, it automatically detects the relationship
between the SubcategoryID in the Products table and the column with the same name in
the ProductSubcategory one.