Database Reference
In-Depth Information
Modeling attribute relationships on a Type II SCD
Let's consider a
Customer
dimension table, which features two columns common to
many Type II SCDs:
•
A surrogate key column,
CustomerKey
, which is the primary key on
the table.
•
A business key column,
CustomerAlternateKey
, which identifies a single
customer. If several Type II changes take place there may be several rows,
each with different surrogate keys, for each customer; each of these rows,
though, will have the same value for
CustomerAlternateKey
.
Two attributes should be built on the Analysis Services
Customer
dimension built
from the discussed table:
•
One which uses the surrogate key in its
KeyColumns
property, let's call
Customer SCD
in the following discussion
•
One which uses the business key in its
KeyColumns
property, which we'll
call
Customer
Customer SCD
will be the key attribute of the dimension and an attribute
relationship should be built to reflect the one-to-many relationship between
Customer
and
Customer SCD
. We can then build other attributes on the dimension
and configure attribute relationships so that those that will never change have
attribute relationships that run through the
Customer
attribute and those that will
be subject to Type II changes have attribute relationships that bypass
Customer
and
go straight to
Customer SCD
. Here's an example of what a first attempt at modeling
these attribute relationships might look like:
Search WWH ::
Custom Search