Best practise dimensional modeling Fabric data warehouse

Hi BastiaanK ,

Thank you for reaching out to the Microsoft Fabric Forum Community.

 You're right that implementing a dimensional model with SCD Type 2 (Slowly Changing Dimensions) in Microsoft Fabric comes with some practical challenges, especially when integrating with Power BI semantic models. Here's a breakdown of best practices and workarounds that I've seen work well in real-world implementations:1. Stick to 1.Dimensional Modelling Principles

Even in Fabric, traditional star schemas with surrogate keys remain best practice:

• Use surrogate keys (not hashes) as primary keys in dimensions and foreign keys in fact tables.
• Avoid relying on natural keys or hash (*) functions for joins, especially with SCD2 dimensions.

2.  Handling SCD Type 2 Dimensions in Fabric

Since Fabric doesn’t currently support MERGE INTO, here's a recommended pattern:

• Maintain EffectiveFrom, EffectiveTo, and IsCurrent columns in your SCD2 dimensions.
• During fact table load, lookup only the current version of the dimension (where IsCurrent = 1 or using date range between EffectiveFrom and EffectiveTo) and join using the surrogate key.
• This ensures that facts point to the correct version of the dimension without ambiguity.

3.  Loading Data Without MERGE

To implement SCD2 without MERGE INTO, use the following approach:

1. Load delta data into a staging table.
2. Use DELETE + INSERT logic to maintain historical records.
3. Generate surrogate keys using identity columns or manual sequencing.

This approach works well in Fabric's T-SQL engine and avoids unsupported constructs.

4.  Power BI Joins with SCD2

If you join fact and dimension tables in Power BI:

• Always prefer surrogate key joins.
• If you must join on business key, filter dimension data using IsCurrent = 1 in DAX, but this may impact performance.

CALCULATE(

    [Measure],

    FILTER('Customer', 'Customer'[IsCurrent] = 1)

)

5.  Performance Tips

• Push as much transformation logic as possible into Fabric using SQL views or notebooks.
• Avoid heavy logic in Power Query or DAX where performance can degrade quickly.
• Consider pre-aggregated views or materialized tables for large datasets.

6.  Optional: Use Flattened Views

To reduce complexity in Power BI, you can create flattened views in Fabric that already include the correct dimension version joined to facts. This avoids ambiguity and improves report performance.

Dimensional Modeling - Microsoft Fabric | Microsoft Learn
Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

If my response has resolved your query, please mark it as the 'Accepted Solution' to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you
LakshmiNarayana