Microsoft Office Tutorials and References
In Depth Information
In the end, she didn’t need to know anything about relationships. PowerPivot handled
relationships among tables for Michelle. The overall work has been slightly easier, the Excel file size is
much smaller (more or less 8MB), and the queries are blazingly fast.
The real difference between the two stories is that in the first one, Michelle used Excel to
load data into worksheets and then used formulas to perform some calculations. In the
second story, she really created a data model without VLOOKUP functions and a pivot
table querying the data model, not an Excel worksheet.
Because we want to learn what a data model is, we need to better understand what is going on
under the cover when PowerPivot detects a relationship and what a relationship is anyway.
What Is a Data Model?
These two scenarios demonstrate that the Excel way does not need a data model whereas
the PowerPivot way creates a data model. But one question is still open: what is a data
model? A Data Model is nothing but a list of tables (sometimes referred to as entities)
with arrows connecting them. A table, as its name suggests, is a list of columns that hold
the real data. The arrows represent the existence of relationships between tables and are
normally read as refers to . So, we might say that an order line refers to a product, meaning
that the order line is about a specific product.
Let us take a look at Figure 4-13, which shows the data model for the tables that Michelle has
loaded to perform her analysis, which, in turn, is the data model Bill in the IT Department
was speaking about.
FIguRE 4-13 The data model of product, models, sales order header, and detail.