SCD – Slowly Changing Dimensions
Implementing SCD’s is common concern in Data Warehouse design. Let me brief about different types of SCD’s first then we can step in to SCD’s design.
Dimensions that change over time are called Slowly Changing Dimensions. For instance, a product price changes over time; People change their names for some reason; Country and State names may change over time. These are a few examples of Slowly Changing Dimensions since some changes are happening to them over a period of time.
Everybody knows that ‘Slowly Changing Dimensions’ is the acronym for SCD. We have 4 types of SCD’s in Data Warehouse
Let’s say I have a customer dimension with these columns mainly (Customer Id, Customer First Name, Customer Last Name, Customer Country)
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
|
1 |
Sudheer |
Sharma |
India |
Now, this guy moved to US. In source the country name has been changed to US, we need to update that in our target dimension to reflect this change.
SCD Type 1: The new incoming record (changed/modified data set) replaces the existing old record in target.
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
|
1 |
Sudheer |
Sharma |
US |
Old value (India) is overwritten by the new value (US) and there is no way to find out the old version of data. It holds only the current version of data.
SCD Type 2: In this case, an additional record is added into the customer dimension. The beauty of this approach is it will maintain two versions, you will find two records the older version and the current version. In other words it maintains history. Again we can implement Type 2 in following methods
- Versioning
- Effective Dates
- By setting Current Flag values/Record Indicators.
Method 1: Versioning
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
Effective Year |
Version |
|
1 |
Sudheer |
Sharma |
India |
2008 |
0 |
|
1 |
Sudheer |
Sharma |
US |
2009 |
1 |
Method 2: Effective Dates
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
Effective Start Date |
Effective EndDate |
|
1 |
Sudheer |
Sharma |
India |
01/01/2008 |
12/31/2008 |
|
1 |
Sudheer |
Sharma |
US |
01/01/2009 |
tilldate |
Method 3: Effective Dates & Current Record Indicators
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
Effective Start Date |
Effective End Date |
Current Record IND |
|
1 |
Sudheer |
Sharma |
India |
01/01/2008 |
12/31/2008 |
N |
|
1 |
Sudheer |
Sharma |
US |
01/01/2009 |
tilldate |
Y |
SCD Type 3: In this approach, only the information about a previous value of a dimension is written into the database. An ‘old ‘or ‘previous’ column is created which stores the immediate previous attribute.
|
Product ID |
Product Name |
Current Year |
Current Price |
Previous Year |
Previous Price |
|
1 |
Close-up |
2008 |
50.00 |
2007 |
45.00 |
The problem with this approach is over years, if the product price continuously changes, then the complete history may not be stored, only the latest change will be stored. For example, in year 2009, if the product price changes to 60, then we would not be able to see the complete history of 2007 prices, since the old values would have been updated with 2008 information.
SCD Type 4: In this approach, one table hold current data and another table keeps historical data for each dimension.
Customer Dimension
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
|
1 |
Sudheer |
Sharma |
US |
Customer History Table
|
Customer Id |
Customer First Name |
Customer Last Name |
Customer Country |
Effective Year |
|
1 |
Sudheer |
Sharma |
India |
2008 |
Hi,
Your example is very nice, but you have missed flag value. how to use falge value and where we can use.
This would be better hlep for us
Hi Ravi,
In SCD Type 2 Method 3 example, I have used the flag scenario as well. Do let me know if you’re looking for more information.
Cheers,
Sudheer
Hi Ravi,
In SCD Type 2 Method 3 example, I have used the flag scenario as well. Do let me know if you’re looking for more information.
You have provided Helpful information,
Can you also provide how do we implement these in BODS atleast SCD2?
Thanks
Raju
hmm will do… I knew i’m taking too much time to update my website… But definitely do dude.
Cheers,
Sudheer
Greate explanation…….example please ….implimentation in BODI ..please
great explanations .. DI implemantion will be more helpful
How to implement SCD 1 and SCD 2? What are the steps and transforms to use? Help please.
There are everal ways of implementing SCD 1 and 2. Most of the times, depends upon your data volume.
Sample Model: SCD 1 implementaion Method 1
Source—Query(with lookup to find out inserts and updates)—Case(to route insert and update data sets)—Query(for Inserts)—Target
—Query(for Updates)—-Map Operation(Set opcode normal as update)—Target
Sample Model: SCD 1 implementaion Method 2
Source—Query—TableComparision—MapOperation—Target
Sample Model: SCD 1 implementaion Method 3
Source—Query—Target(Enable Auto Correct Load option which can be found at target table options), Use this method where the business logics are less and small amount of data.
Cheers,
Sudheer