Slow Changing Dimensions (SCD) as pioneered by Kimball is very helpful on our
data warehouse project.

The way that we handle the SCD in Analysis Services 2005, is by creating a
hierarchy with the Business Key (natural key from the source system) on the
top level of the hierarchy and the client name on the second level. (We use
surrogate keys to link the dimensions to the fact tables.)

This SCD strategy works well for us. One can then easily see the aggregated
sales of a specific client (even if the details have changed over time), but
then drill into more detailed info of this specific client, if required. (For
example, see the sales as the client's details have changed over time, e.g.
what was sales when the client was in Region A and what was the sales when
the client was in Region B).

Are there other ways of handling the SCD in Analysis Services?

We have also set the SCD settings SCDEndDate, SCDStatus, SCDStartDate,
SCDOriginalID on the Type property of the Dimension attributes, but does this
cause the cube to work differently, or is it just meta-data that can e.g. be
used by a front-end tool?

a SCD dimension is created when you want to handle changes like yours
I mean you must have 2 surrogate keys for the same client but each surrogate
key is associated to each region.
The client appears 2 times, 1 time under region A and then under region B

All Regions
--> Region A
------> Customer 1
--> Region B
------> Customer 1

each sale is associated to the right region / customer combination.
if your customer dimension is not by region, then you don't need to have a
surrogate key
(maybe you have other attributes where you want to track the changes)
also, if you want to have all the sales of customer 1 in an hierarchy like:

All Gender
--> Male
------> Customer 1

in this case, the customer 1 level will not be the surrogate key attribute
but the business key, or the name himself attribute.

What I recommand is to create a dimension like:
Key attribute: name of the attribute=Changing Name / key column=Surrogate
key / name column=customer name / hierarchy visible=false
other attributes:
name of the attribute=Customer / key column=Business key / name
column=customer name / hierarchy visible=false
name of the attribute=Region / key column=Region key / name column=Region
name / hierarchy visible=false
name of the attribute=Gender / key column=Gender key / name column=Gender /
hierarchy visible=false
etc...
(All the attributes are associated to the key attribute)

now 2 user hierarchies:
"Customers by region" (the slow changing hierarchy)
Level 1 = Region attribute
Level 2 = Changing name attribute (renamed to Customer)

"Customers by gender" (the non-slow changing hierarchy)
Level 1 = Gender attribute
Level 2 = Customer attribute

so you have 2 times the customer name in the dimension, but 1 is changing
and the second is not changing.
there is other way to do this type of dual usage (changing and non changing
information in 1 dimension), but the key column must be the surrogate key.

I hope this answer some of your needs(?)

Jerome.


"Willem de Ru" <WillemdeRu (AT) discussions (DOT) microsoft.com> wrote