When dimension changes over a period of time, it is called as Slowly Changing Dimension and it is very popularly known as SCD. It is used for tracking history of dimension attributes.There are mainly six types of SCD which are TYPE 0, TYPE 1, TYPE 2, TYPE 3, TYPE 4 and TYPE 6.
EmployeeDeatil table mentioned below is used as an illustration, contains columns Emp_Key, EmployeeID, Name and Designation. Emp_key is a Surrogate Key.
TYPE 0 (Retains original value): This is a passive method. Value remains exactly the same as that of when inserted for the first time. This is not often used.
TYPE 1 (Overwriting old value): In this type, old value is replaced by a new value in the dimension table. Old data is lost in this process.
Let us assume Employee has been promoted from Test Engineer to Test Lead. So, Designation column has been replaced with new value as shown below. Thus we are not maintaining any history of changes that has taken place.
Old Value
EmployeeDeatil table mentioned below is used as an illustration, contains columns Emp_Key, EmployeeID, Name and Designation. Emp_key is a Surrogate Key.
TYPE 0 (Retains original value): This is a passive method. Value remains exactly the same as that of when inserted for the first time. This is not often used.
TYPE 1 (Overwriting old value): In this type, old value is replaced by a new value in the dimension table. Old data is lost in this process.
Let us assume Employee has been promoted from Test Engineer to Test Lead. So, Designation column has been replaced with new value as shown below. Thus we are not maintaining any history of changes that has taken place.
Old Value
Emp_Key
|
EmpID
|
Name
|
Designation
|
111
|
1001
|
Sunil
|
Test Engineer
|
New Value
TYPE 2 (Adding new row): In this type, a complete
Emp_Key
|
EmpID
|
Name
|
Designation
|
111
|
1001
|
Sunil
|
Test Lead
|
TYPE 2 (Adding new row): In this type, a complete
