There are quite a few ways of ways of documenting the changes a business entity goes through over time. The most famous of them all (and the most difficult to build) is a Slowly Changing Dimension (SCD) type 2. doing this in a table, you would create a table which would include: an incremental key, the business key, the attributes you’d like to track for that entity, a start date and an end date (for which the information was right) and a flag which would equal 1 when we are looking at the last row available for the member of that entity.
A table for example would be:
For more information about SCD, please refer to an article by Margy Ross and Ralph Kimball.
So you’ve done a very bold and difficult move and created a Slowly Changing Dimension type 2. How does that actually manifest itself in the Analysis Services project you’re building?
Well, they added something new in SQL Server 2005. Now, if a dimension in a cube is an SCD type 2, you may update the Meta Data of the relevant attributes in that dimension. Each attribute in a dimension, has a property called “Type”. Attribute types help classify an attribute in terms of business functionality. You may like to map out a few attributes for an SCD type 2. The attributes that need to be mapped and their relevant types are:
The business key (in this example Worker_id) to type: ScdOriginalID
The start date to type: ScdStartDate
The end date to type: ScdEndDate
The flag to type: SCDStatus
And updating that should be peanuts for you, after building the SCD type 2 dimension ;P