Microsoft Office Tutorials and References
In Depth Information
Even if it seems intuitive, this simple operation hides one of the most important features of
PowerPivot. You have chosen an attribute from the SalesOrderHeader table and one from
the SalesOrderDetail table. Even if they belong to two different tables, you can slice the total
of the details by using attributes from the header. How is PowerPivot able to do that? The
key is in relationships.
A relationship, as its name suggests, is used to tie together two tables. A relationship is
graphically shown as a line between the source table and the destination one. The
relationship shown in Figure 1-11 can be read as “for each sales order detail, there is a header
FIguRE 1-11 Relationship between SalesOrderDetail and SalesOrderHeader.
The AdventureWorks database resembles almost any relational database in containing
relationship information between tables. PowerPivot, reading these tables, is able to determine
that each row inside the SalesOrderDetails table contains a column (SalesOrderId) that relates
to a column with the same name inside the SalesOrderHeader table. In other words, whenever
you read a row from the detail table, you can link it to its header by searching inside the header
table for a row with the same value for the column SalesOrderId. It is not yet time to discuss
relationships in detail; the important thing now is to understand that two tables can work
inside the same pivot table if and only if there is a known relationship between them. If this
is the case, you can rely on the PowerPivot database engine to analyze the relationship and
allow you to slice a table using columns coming from another one. As simple as this feature
might seem, it is one of the most powerful capabilities of PowerPivot because it lets you
combine many different tables inside a single pivot table. You can explore your database
in complete freedom.
Armed with this powerful knowledge, you might want to play with your tables and perform a
more complex analysis. Let us move the OnlineOrderFlag on the column label and then choose
the column Color from the product table. When you do this, you are bringing together three
different tables in a single pivot table. Nevertheless, the result is not exactly what you would
expect, as you can see in Figure 1-12.