Best practise dimensional modeling Fabric data warehouse

[BastiaanK]

I am looking for the best practice for implementing a dimensional model in Fabric warehouse.

What I run into with Fabric is that it has all the features of a Data AI platform like for example Databricks and Snowflakebut falls short in terms of capabilities.

Take for instance the parallel and independent loading of your fact and dimension tables. This in itself is possible despite the statement currently not supporting MERGE INTO and hash(*). I.e. you can apply parallel loading with some workarounds but then comes the next problem how do join the scd2 dimension tables then in your Power BI semantic model based on hashed keys that occurs multiple times?

There are also workarounds for this by adding additional index columns in Power Query but performance wise and complexity wise this is not a desirable solution for larger models. Incidentally, this plays out not only with a Fabric warehouse in combination with Power BI but also with e.g. Databricks in combination with Power BI.

I have searched a lot on the internet and among the Microsoft sources I mainly see that they recommend traditional data warehouse solutions as best practice.

[v-lgarikapat · Accepted solution]

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