Microsoft Office Tutorials and References
In Depth Information
To use the PowerPivot relationship model, you need an intermediate table that can be used to
relate both tables. We call this new table CountryWeight because it can contain all the different
combinations of countries and weight. If you introduce such a table, country and weight are
keys in that table and you can then set up relationships for both Orders and PriceList to the
new CountryWeight table. You can see the new data model in Figure 10-29.
FIguRE 10-29 The many-to-many data model for the courier simulation. Double lines between tables
indicate that relationships are made by using two columns.
The new table has no information in it; it is just a technical table that you need to create the
relationship between PriceList and Orders. As you can see, you are changing the data model
to make it suit your needs.
Now that you have a data model that could be implemented in PowerPivot, you need to face a
simple problem: there is neither a PriceList table nor CountryWeight one. Nevertheless, you can
build both tables by starting from the original Excel table, using a modified version of the VBA
script that we used previously for banding. In Figure 10-30, you can see the Excel worksheet
with the computed tables that you will link in PowerPivot.
FIguRE 10-30 The three tables for the many-to-many solution, computed by a macro.
The VBA code of the macro used in this workbook is slightly more complex than the one we
used for banding. Nevertheless, it uses the same approach. The interested reader can find
the source code in the companion workbook CH10-04-CourierSimulationM2M.xlsm.
You can create linked tables for the two rightmost tables in PowerPivot to have both the
PriceList and the CountryWeight tables available in the PowerPivot database.