Dimension - Slowly Changing Dimension Technique

This is the concept of “Slowly changing Dimension”. This is widely used in data warehousing environment

 

Slowly changing Dimension

ü Type 0 - The passive method à Dimension data is left as such ( For ex: state codes)

 

 

ü Type 1 - Overwriting the old value à Dimension data is over written if a new record is received

 

 

Before change:

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

After change:

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Retail

 

 

ü Type 2 - Creating a new additional record

 

Before change:

 

 

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	22-07-2010	31-12-9999	Y

After change:

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	22-07-2010	17-05-2012	N
2	Cust_1	Retail	18-05-2012	31-12-9999	Y

 

ü Type 3 - Adding a new column

 

Before change:

 

Customer_ID	Customer_Name	Current_Type	Previous_Type
1	Cust_1	Corporate	Corporate

After change:

Customer_ID	Customer_Name	Current_Type	Previous_Type
1	Cust_1	Retail	Corporate

 

ü Type 4 - Using historical table

 

Current Table

 

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

 

 

Historical table

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date
1	Cust_1	Retail	01-01-2010	21-07-2010
1	Cust_1	Oher	22-07-2010	17-05-2012
1	Cust_1	Corporate	18-05-2012	31-12-9999

 

ü Type 5 - Combine approaches of types 1,2,3 (1+2+3=6)

 

·  Customer_ID	Customer_Name	Current_Type	Historical_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	Retail	01-01-2010	21-07-2010	N
2	Cust_1	Corporate	Other	22-07-2010	17-05-2012	N
3	Cust_1	Corporate	Corporate	18-05-2012	31-12-9999	Y