In Data warehouse, the data remains persistent, but the dimensions evolve slowly over the period.For example, customers change their names, relocate, have children, change their Incomes. These dimensions are known as slowly changing dimensions.
To capture changes these changes, a combination of strategies are evolved. These strategies are usually known as SCD types.
The Strong Knowledge of Slowly changing dimensions in a data warehouse is the key to success in the BI domain.
Why Slowly changing dimensions (SCD)?
To report data historically, these changes in the attribute of dimensions are captured. This help us to track the events.
In a normal scenario of the employee database, The fact table and dimension table would be linked by way of foreign keys. One dimension table may contain the office location of the employees.The employees may get transferred to other location over the period. To prepare a report, it may require tracking the location history of the employee.
To handle this issue some methodologies or strategies are adopted, which are referred as SCD type 0 to 6.
SCD Type 0: No Change
This type is rarely used. It maintains the dimensional changes as they were initially at the time the record is added, so No action is required.
SCD Type 1: Replaces the Previous record
In this strategy, the New record replaces the previous record and finally no historical data is maintained.
For example: Once the location of the employee is updated we would have no records of his previous locations.
SCD Type 2: Maintain Additional Record
In this strategy, the history is maintained with the additional records in the dimension table with two columns Active_Flag and Expired_date. The active_flag column determines the latest records and Expired_date column determines the period till the records were active.
SCD Type 3: Attribute as Column
This method maintains the history by means of additional columns for holding the value for the previous events. History is limited to columns defined to hold historical data.
|12345||IT||John Mathew||New Delhi||22-Dec-2004||London|
In the above example, the Location history is maintained using extra column Previous_location.
SCD Type 4: History table
In This method, Dimension table holds the current data, and the previous history is maintained with history table. Keys in both tables are linked with Fact table to improve the result.
Main Dimension table with current records
History table: Dimension table with previous records: