Designing More Complex Dimensions - Expert Cube Development with SSAS Multidimensional Models - page 65

Modeling Slowly Changing Dimensions
Slowly Changing Dimensions ( SCDs ) are a fact of life in almost all data
warehouses; we discussed how to deal with them in our relational model in
Chapter 1 , Designing the Data Warehouse for Analysis Services , and we'll now look
at the issues that arise with each different type when building Analysis Services
dimensions. In many cases, of course, different attributes on the same dimension
can be subject to different types of change and we will use a combination of the
techniques outlined in the next section, but it's helpful to discuss the three most
common types of change separately for the sake of clarity.
Type I SCDs
Since changes in Type I SCDs involve overwriting existing values in the dimension
table, no special design is needed in Analysis Services to support this. Running a
Process Update on the dimension will ensure that any changes that have been
made in the dimension table are reflected in your Analysis Services dimension.
We'll discuss processing in more detail in Chapter 10 , Going in Production .
It's at this point that we'll see an error if any attributes have changed that should not
have changed, that's to say, if any members on attribute hierarchies which have Rigid
relationship types have changed position in the dimension relative to each other. For
example, if on the Time dimension, there were Date and Month attributes that had
a Rigid attribute relationship defined between them, and the Date January 1st 2001
somehow got moved from under the Month January 2001 to the Month March 2002,
we'd expect to get an error because something has clearly gone wrong. It's also possible
that a Type I change will result in the data in the dimension not being consistent with
the attribute relationships we have defined, and what we thought to be a one-to-many
relationship, turns out to be a many-to-many relationship. For example, we might
have defined a chain of attribute relationships on a Customer dimension going from
Continent to Country to City , but then realize that there is an exception to this rule
when the cities of Istanbul and Ankara are added: both are in Turkey, but Istanbul
could be listed as being in Europe and Ankara in Asia.
You might think that an update to a dimension that invalidates
an attribute relationship would raise an error during dimension
processing, but it doesn't always. If you run a Process Full
command on a dimension with invalid attribute relationships, and
you specifically configure processing error handling to trap duplicate
key errors, then processing will fail. However, a Process Update
will complete successfully in this situation, regardless of how you
configure processing error handling, leaving the dimension in a
dangerous state where incorrect data could be returned from the cube.