Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-18 Creating a relationship between the SalesOrderHeader and OrderDate tables.
You can find the resulting model in the CH07-02-OrderAndShippingDate.xlsx workbook included
on the companion DVD. However, as you can see in the next section, duplicating tables might
not be enough. Because Excel does not show the table name to which a column belongs when
you use it for Slicers and Filters, you might want to add a prefix to your columns. It is better to
do that directly in the source Excel table rather than renaming the columns in PowerPivot only so
that the overall model is simpler to understand.
Dates Columns in Different Tables
You must define a separate Dates table to distinguish the semantics of different dates
in your data. This is certainly true whenever different date columns belong to the same
table, as in the case of the OrderDate and ShipDate columns in the SalesOrderHeader
table. However, when you have dates columns in different tables, you have to evaluate
whether the semantics of these dates is the same or not.
Every time you have a different role for a date, you have to create separate Dates tables
to browse data, just as you saw in this section. On the other hand, you have to use the
same Dates table whenever these dates have the same meaning, at least for your analysis.
For example, if you have an OrderDate in the Sales table and a CallDate in a CallCenterCalls
table, you might decide to create two separate data tables named OrderDate and CallDate.
But you might also want to create a single Dates table that connects both events, which
would ease the browsing over time of data from both tables in the same report. If you
have no other dates in your model, no ambiguities arise from that arrangement, but if
there are other dates involved in the same model, you should consider a separate model
for doing correlation analysis, avoiding misleading names in your model.