Slowly Changing Dimension
Ch.02: DWH | DWH Components | Data Modeling | Dimension Types
Lesson Notes
Slowly Changing Dimensions Lesson Notes
Video
Slowly changing Dimensions
-
It the dimension which changes over time. So, for a specific date we have different value.
-
It has different types as following
-
Type 0 (Fixed Dimension): We don’t change the current even the source changes.
-
Type 1 (No History): No history is maintained only the latest replace the current.
-
Type 2 (History): Series of history of records are maintained.
-
Type 3 (Hybrid): Only the last Change and the Current new change is stored
-
Type 4 : We split the data into two tables, first the current record and second is the historical (most common usage).
-
Slowly changing Dimensions Other Types
There are some other types which is a combination between the above similar than type 3 combined between 1 | 2.| You can check the chapter resources for more information about the other types.
Slowly changing Dimensions Types
Type 0
No update if the previous dimension updated.
-
Ronaldo current address in Madrid
CustomerID Name City 123456789 Ronaldo Madrid -
Ronaldo updated his to Turin
CustomerID Name City 123456789 Ronaldo Turin -
Results in type 0 will not updated.
ID CustomerID Name City 1 123456789 Ronaldo Madrid
Type 1
-
Ronaldo current address in Madrid
CustomerID Name City 123456789 Ronaldo Madrid -
Ronaldo updated his to Turin
CustomerID Name City 123456789 Ronaldo Turin -
Results in type 1 will update the record without history maintenance.
ID CustomerID Name City 1 123456789 Ronaldo Turin
Type 2
-
Ronaldo addresses history
CustomerID Name City UpdatedDt 123456789 Ronaldo Madrid 2018-12-12 123456789 Ronaldo Turin 2019-06-12 123456789 Ronaldo London 2019-08-12 123456789 Ronaldo Porto 2019-12-12 -
Type 2 will keep all the history and keep the current with
terminationDt as nullID CustomerID Name City effectiveDt terminationDt isCurrent 1 123456789 Ronaldo Madrid 2018-12-12 2019-06-12 false 2 123456789 Ronaldo Turin 2019-06-12 2019-08-12 false 3 123456789 Ronaldo London 2019-08-12 2019-12-12 false 4 123456789 Ronaldo Porto 2019-12-12 null true
Type 3
- Maintain the current and previous only with maintaining the history.
CustomerID Name City UpdatedDt 123456789 Ronaldo Madrid 2018-12-12 123456789 Ronaldo Turin 2019-06-12 123456789 Ronaldo London 2019-08-12 123456789 Ronaldo Porto 2019-12-12 ID CustomerID Name City UpdatedDate previousCity 1 123456789 Ronaldo Porto 2019-12-12 London
Type 4
- Split current and historical into two tables.
- This change help to easy join with only current active records without the needs for the filter such as type 2.
ID CustomerID Name City effectiveDt TerminationDt 1 123456789 Ronaldo Madrid 2018-12-12 2019-06-12 2 123456789 Ronaldo Turin 2019-06-12 2019-08-12 3 123456789 Ronaldo London 2019-08-12 2019-12-12 4 123456789 Ronaldo Porto 2019-12-12 null ID CustomerID Name City UpdatedDate 1 123456789 Ronaldo Porto 2019-12-12
Slowly changing Dimensions
-
How does the Facts join SCD? We have two scenarios as following:
-
Getting the current customer information (Join with the latest).
-
Getting the historical customer information (Join with the historical table based on cust id | date).
ID CustomerID TotalCalls CallDate 1 123456789 30 2018-12-12 2 123456789 30 2019-12-12 -
Slowly changing Dimensions Extraction Example:
```sql
--Get latest customer details from customer profile snapshot
select * from cust_usage_dly a
inner join cust_profl b
on a.CustomerID = b.CustomerID;
--Get historical customer details from customer profile hist
select * from cust_usage_dly a
inner join cust_profl_hist b
on a.CustomerID = b.CustomerID
and CallDate between effectiveDt and TerminationDt
```
Further Reading
Dimensional Modeling: In a Business Intelligence Environment. The book is free, and you can download it from this link. You can read the following:
- Chapter 5.4.6 Slowly Changing Dimensions page 159.
- Chapter 6.3.5 Slowly Changing Dimensions page 261.