Microsoft Office Tutorials and References
In Depth Information
You can see that there are four tables (entities) in the diagram:
■ SalesOrderHeader This contains the global information about an order.
■ SalesOrderDetail This contains the single lines of the order—that is, the products
sold with the order with detailed information about price, quantity, and so on.
■ Product This table, as its name clearly suggests, contains the product information
stored in columns, which are shown in the product box.
■ ProductModel This contains the different product models.
An arrow starts from the Product table and goes to the ProductModel table. This indicates that
there is a column in the Product table whose value needs to be found in the ProductModel
table so that you can get the name of the product model. In other words, Product and
ProductModel are connected through a relationship.
Although there are many complex ways to define relationships, the standard method is to
create a column in both tables (usually the column has the same name in both tables) and
state that two rows from the two tables are related when that column contains the same
value. In our example, the column ProductModelID exists in both tables, and it is the one
responsible for holding the relationship.
Note In Excel, we used the VLOOKUP function to perform the lookup; using PowerPivot, we need
a relationship between the two tables. The result is the same: using the relationship column, we are
able to find product model information and use it in the product table.
It is now easy to read the other arrows: the SalesOrderDetail table shares a relationship with the
Product table, which means that a line of an order refers to a product, which is somehow obvious.
In this case, the relationship column is ProductID. Moreover, SalesOrderDetail shares a
relationship with the SalesOrderHeader table, which means that each line belongs to an order.
To make use of all of the PowerPivot functionalities, you need to learn to read, to understand,
and maybe to write some data models—that is, draw boxes and arrows to represent the data
you want to describe and use.
As you are about to discover, data modeling is not an easy task and is likely to cause you more
than one headache. Nevertheless, because it is the key to building complex analysis, we are
going deeper in the discovery of different data models to provide a solid foundation of data
modeling problems and solutions.