Slowly Changing Dimension
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.