Forum Discussion
Modeling slowly changing dimensions
Hello Community,
I ve been working these last days on 3 tables that I want to get insights from (EmployeeAssignment, Person, Department)
the data looks like :
EmloyeeAssignment Table :
| AssignmentId | PersonId | DepartmentId | info | validFrom | validTo |
| 1 | 2 | 3 | info1 | 12/01/2021 | 15/01/2021 |
| 1 | 2 | 3 | info2 | 15/01/2021 | 31/12/4999 |
| 2 | 3 | 5 | info1 | 10/01/2021 | 31/12/4999 |
Department Table :
| DepartmentId | info | validFrom | validTo |
| 1 | info1 | 01/01/2021 | 16/01/2021 |
| 1 | info2 | 16/01/2021 | 31/12/4999 |
| 2 | info1 | 12/01/2021 | 31/12/4999 |
Person Table :
| PersonId | info | validFrom | validTo |
| 1 | info1 | 01/01/2021 | 16/01/2021 |
| 1 | info2 | 16/01/2021 | 31/12/4999 |
| 2 | info1 | 12/01/2021 | 31/12/4999 |
you can see that when the a specific row in the 3 tables changes, we use the SCD2 to keep the history of the records.
I want to be able to model these tables so I can take a snapshot on a date (any specific date) and see the result based on this date. if I create a sort of surrogate key for each table( ex : combine DepartmentId with validfrom), I wown't be able to replace the forreign keys in the table EmployeeAssignment. How can I hundle this situation ?
best regards
In this scenario, you can add an independent Dim Date table to the model, which is disconnected to all these 3 dimension tables. Then put date column from Date table into a slicer visual, use measures to get corresponding values from each dimension table by comparing the selected date in slicer with validFrom/validTo dates in each dimension table.
For example, you can use a measure like below to get info from Person table.
PersonInfo = VAR vSelectedDate = SELECTEDVALUE ( 'Date'[Date] ) RETURN MAXX ( FILTER ( Person, Person[validFrom] <= vSelectedDate && Person[validTo] > vSelectedDate ), Person[info] )Or you can refer to below videos and articles to get more ideas about how to deal with SCD.
Slowly Changing Dimensions in Power BI - YouTube
Working with Slowly Changing Dimensions in Power BI - YouTube
https://p3adaptive.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/
MSPTDA 35: Power BI & DAX Formula for Data Modeling with a Slowly Changing Dimensions - YouTube
MSPTDA 34: Data Modeling for Slowly Changing Dimensions with Power Query & Power Pivot - YouTube
Hope they are helpful.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
1 Reply
In this scenario, you can add an independent Dim Date table to the model, which is disconnected to all these 3 dimension tables. Then put date column from Date table into a slicer visual, use measures to get corresponding values from each dimension table by comparing the selected date in slicer with validFrom/validTo dates in each dimension table.
For example, you can use a measure like below to get info from Person table.
PersonInfo = VAR vSelectedDate = SELECTEDVALUE ( 'Date'[Date] ) RETURN MAXX ( FILTER ( Person, Person[validFrom] <= vSelectedDate && Person[validTo] > vSelectedDate ), Person[info] )Or you can refer to below videos and articles to get more ideas about how to deal with SCD.
Slowly Changing Dimensions in Power BI - YouTube
Working with Slowly Changing Dimensions in Power BI - YouTube
https://p3adaptive.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/
MSPTDA 35: Power BI & DAX Formula for Data Modeling with a Slowly Changing Dimensions - YouTube
MSPTDA 34: Data Modeling for Slowly Changing Dimensions with Power Query & Power Pivot - YouTube
Hope they are helpful.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.