Slowly Changing Dimension Part 2
This is a follow up article describing the remaining types and transformations available in slowly changing dimension for dimension data management. The part one of the series covered the details from Type 0, Type 1, Type 2 and Type 3. The part one of the series can be found here .
In this follow up article, we will see Type 4, Type 6 and the Combining Types.
Before we move ahead, the table below will serve as a quick recap of the transformations available for the SCD’s.
Fixed. No history preservation
Overwrite old data with new data
Create new record by tuple versioning
Maintain change data in a separate column
Maintain change in a separate history table
Concept derived using Type 1,2 and 3
A combination of transformation on the underlying dimension data
In type 4, we maintain a separate a ‘history table’ which will house all historical data or changes made to the dimension data in a similar table. I say similar and not identical SQL table for a reason. The history table is designed may/may not have all the columns as in the original dimension table.
For example: The business might just want few columns to be preserved in the dimension ‘history table’
Sales Person ID
Suppose the ‘State’ column is managed using type 4 SCD, in that case
Case 1: All columns are preserved in the history table.
Case 2: Selected columns are maintained in the history table.
Expert Comment: The advantage over SCD type 2 is that, the underlying dimension table is not burdened with a set of legacy data or history changes. Thus the size of dimension table can be restricted by maintaining a separate table for history data, as described above.
This approach for data management of the dimension data encompasses Type 1 + Type 2 + Type 3. This can be illustrated as below.
Table structure after employee ID 1234 moves to some other state
This kind of exhaustive approach for dimension data manageability describes type 6. Let us now try to understand more what makes Type 6 by a short recap for Type 1, Type 2 and Type 3.
With this we shall end our attempt to revisit the SCD’s.
The combining type as the name suggests can be implemented to facilitate dimension data manageability in way which best suits the business requirements.
We have learnt that Type 6 is nothing but a composition of Type 1, Type 2 and Type 3. On similar lines, a combination of above listed SCD transformations can be composed to suit the business need and implement a SCD transformation which falls in the category of ‘Combining Type’.
Feel free to let me know your views and any other aspect of Data Warehousing concept; you would like me to compose a write-up on.
Leave a comment