Microsoft Office Tutorials and References
In Depth Information
The relationship detected by PowerPivot starts from the ProductID column of the SalesOrderDetail
table, and it links this column with the one of the same name in the product table.
Note All relationships have the same format: they link a column in a table with a column in
another one. Two rows are considered linked if they share the same value for the column values. We
will return later to consider the relationship-detection algorithm. At this point, we do not want to
spend too many words on it; our focus is on the requirement of relationships to get meaningful
results, not on the internal workings of this complex algorithm.
When you close this window, the pivot table is automatically refreshed and shows the grid,
with the relationships active, as you can see in Figure 1-15.
FIguRE 1-15 With relationships accounted for, the values are now correctly computed.
Now the grid looks much more interesting because the relationship is working and the
data is sliced correctly by color. You are now joining three different tables inside the same
pivot table and you are slicing order details using values from the order header and the
product table. Moreover, as you might already have noticed, you have been able to analyze
data without building any queries to the database; it is obvious that you do not need to
know SQL to use PowerPivot.
Note All this work has been completed without your ever asking IT a single question. Believe it
or not, this is the very heart of self-service BI: you are building a BI solution by yourself, without
needing to ask for help from anybody else. Of course, later on you will surely encounter some
trouble in this process, and maybe some help from the IT team will be welcome. Nevertheless,
the effort you ask them to exert on your behalf is likely to be greatly reduced when compared
with what you would have asked when trying to analyze data with the Excel PivotTable tool alone.