Data Model with SCD type 4 implementation

[santhidhanuskod]

Hi,

I have implemented SCD Type 4 and loaded data in 2 diff tables, live and history. WE have lots of tables and all of them are implemented with tpe 4. and we will defintely have many-many relationships when we model this in power bi.

As we maintain data datewise, there will be duplicates in the certain columns. For eg I have  a header column which is duplicated bec of type 4 implementation. We can identify the unique records only with the combination of header and recorddate. 

Business requirement is to select the recorddate in the report and they should be able to see the data for tht particular date.

how cn I model in power bi, how to get rid of man-many issue?

[Sahir_Maharaj · Accepted solution]

Hello @santhidhanuskod,

Can you please try this approach:

1. Create a Date Dimension Table

DateTable = ADDCOLUMNS (
    CALENDAR (DATE(2000,1,1), TODAY()),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

2. Create a Bridge Table

BridgeTable = DISTINCT(
    UNION(
        SELECTCOLUMNS( LiveTable, "Header", LiveTable[Header], "RecordDate", LiveTable[RecordDate]),
        SELECTCOLUMNS( HistoryTable, "Header", HistoryTable[Header], "RecordDate", HistoryTable[RecordDate])
    )
)

[v-zhengdxu-msft · Accepted solution]

Hi @santhidhanuskod 

Usually, creating a bridgiing table is a general solution for many-to-many relationships.

Many-to-many relationship guidance - Power BI | Microsoft Learn

Connecting Fact Tables in Microsoft Fabric: A Brid... - Microsoft Fabric Community

Or you can use the Merge queries to merge tables with the same key value in the Power query.

Merge queries overview - Power Query | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sahir_Maharaj · Accepted solution]

Hello @santhidhanuskod,

Can you please try this approach:

1. Create a Date Dimension Table

DateTable = ADDCOLUMNS (
    CALENDAR (DATE(2000,1,1), TODAY()),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

2. Create a Bridge Table

BridgeTable = DISTINCT(
    UNION(
        SELECTCOLUMNS( LiveTable, "Header", LiveTable[Header], "RecordDate", LiveTable[RecordDate]),
        SELECTCOLUMNS( HistoryTable, "Header", HistoryTable[Header], "RecordDate", HistoryTable[RecordDate])
    )
)