Microsoft Office Tutorials and References
In Depth Information
The small icon in the OrderDateKey column indicates that this column already has a
relationship with another table. So we know, by looking here, that the date dimension has a
relationship with the date of the order. Knowing that, you can see that it would be much
better to rename the DimTime dimension with a name that indicates the relationship. You
can easily do that by right-clicking the table name and choosing Rename to name it, for
Now you have a time dimension that has the correct name, but what if you want to slice your
data for the shipping date year or the due date month? You could be tempted to create many
relationships between FactInternetSales and the date dimension. In a relational model, you are
permitted to have more than one relationship between two tables, but this is not the case in
PowerPivot. If one table has a relationship with a second table, you cannot set up a new
relationship between the same two tables. In other words, two tables can share only one relationship
even if a table can have a relationship with other tables.
This situation is well known in the data warehouse world and is known as role relationship or
role dimension (that is, a dimension is referred many times from a fact table and each
relationship imposes a role on that dimension). The Time dimension acts as an order date, a shipment
date, or a due date. In other words, the same time acts according to different roles, depending
on the relationship you follow to get there.
As we said before, role dimensions are not natively supported in PowerPivot. To work around it,
you can load the same table more than once and set up relationships from the fact table with
those different instances of the same Time dimension, superseding the current limitation.
If you want, for instance, to have a dimension that acts as the ship date, you can open the
connection with the database, select the DimTime table again, and load it. It loads as DimTime1.
(PowerPivot adds a number after the dimension name to make it evident that this is not the
first instance of the table, unless you remember to define a new friendly name.) You should
rename it OrderShipDate, to make its meaning evident.
When you add this new dimension to a PivotTable containing some values from FactInternetSales,
PowerPivot displays the message Relationship Needed because you still have not defined the
relationship between the fact table and the dimension. You now can ask PowerPivot to detect
relationship and chances are good that it will detect the relationship between the new time dimension
and the ShipDateKey column in the fact table. The relationship you find takes this form:
[FactInternetSales].[ShipDateKey] -> [OrderShipDate].[TimeKey]
Does it look easy? It definitely is. Nevertheless, if you think twice about what happened, you
discover a strong need to understand how PowerPivot detected that relationship because it
seems too easy to be true.