Microsoft Office Tutorials and References
In Depth Information
In fact, the FactInternetSales table has three different relationships with the time
dimension, for the order, ship, and due dates. After loading the second instance of the time
dimension, you had two time dimensions in the database, one of which already had a
relationship with the OrderDateKey. So, there is one free time dimension and two
candidates for the relationship: ship and due date. PowerPivot detected the correct candidate,
selecting the OrderShipDate column. The problem is that it might have found the
relationship on the DueDateKey, if the automatic algorithm failed to do its job properly for
The relationship detection algorithm analyzed not only the data in the tables, but also the
names of the columns; this is the reason it detected the relationship with ShipDateKey and
not with DueDateKey. Nevertheless, relying only on automatic relationship detection based
on heuristics is not a safe option. You need to be sure that the relationship is set up correctly,
so even if you can give the automatic algorithm a chance, you definitely need to double-check
its work using the Manage Relationship window, where you can verify that all relationships
have been found correctly.
Note Even if it might be strange for us to say this, we strongly advise never to trust the
automatic relationships detector. We suggest that you use it but always verify the relationship the
detector finds, and in case of any doubt, double-check it with the Manage Relationship dialog
box. A wrong relationship causes the display of wrong data, and if it is not checked at the time
of creation, it might be very difficult to find later.
Role dimensions are useful because they lead to an easier model to manage. Attribute
consolidation is also welcome because it makes it easier to update the model with new
and interesting attributes. You can try, as an exercise, to complete the three roles of the
time dimension by adding a new time dimension that has the role of ship date.
Another interesting kind of relationship, which we use in later chapters, is the many-to-many
relationship. Many-to-many relationships are common, and at first sight, pretty complex to
understand. Nevertheless, they are powerful and you definitely need to understand them
to exploit their power. Later in the topic, you will make extensive use of the many-to-many
relationship. For now, it is enough to introduce them with an example, to help you
understand what they are.
In the AdventureWorks data warehouse, each line of an order might refer to some sale reasons.
A Sale reason might be Magazine Advertisement, Review, Demo event, or other reasons that
are listed in the table DimSalesReason.