Modeling slowly changing dimensions

[ey_boubellouta]

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

[v-jingzhang · Accepted solution]

Hi @ey_boubellouta 

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.