Database Reference
In-Depth Information
Given that the same customer may appear many times in the
Customer SCD
attribute
hierarchy after Type II changes have taken place, and that the end user may be
confused by this, it makes sense to hide the
Customer SCD
attribute hierarchy by
setting its
AttributeHierarchyVisibleState
to
False
. The user can instead use
the
Customer
attribute hierarchy to view individual customers. However, it's also
likely that we would want to build a user hierarchy that would allow users to drill
from
Country
to
City
and down to
Customer
. With the attribute relationships
modeled previously, we would have two options, neither of which would be ideal:
•
Build the user hierarchy with levels based on the
Country
attribute
hierarchy, the
City
attribute hierarchy, and the
Customer SCD
attribute
hierarchy. This would be a natural user hierarchy so you would get optimal
performance, especially important on a dimension such as
Customer
that can
be very large. But, what would happen if a customer got married, and this
created a new row in the dimension table? A new member would be created
on
Customer SCD
, so when the user drilled down from the
City
level they
might see the same
Customer
listed twice, once in their unmarried state and
once in their married state; clearly not a good thing.
•
Build the user hierarchy with levels based on the
Country
attribute
hierarchy, the
City
attribute hierarchy, and the
Customer
attribute hierarchy.
This would ensure the user only ever saw one instance of a customer per
city, but it would also be an unnatural user hierarchy and would therefore
perform worse.
One solution to this problem is to create an extra attribute hierarchy to represent
unique combinations of individual customers and cities, with a composite key based
on the
CustomerAlternateKey
business key and the key used for the
City
attribute
hierarchy. This would allow us to model the attribute relationships as follows:
Search WWH ::
Custom Search