Role – Playing Dimensions in Fabric Direct Lake Semantic Models

Did you know that you can include the same physical table from OneLake multiple times in a single Direct Lake semantic model?

For example, imagine you have two tables in a Fabric Lakehouse. One of them is a fact table named Sales and the other is a dimension table called Date:

Note that the Sales fact table has two date columns, OrderDate and ShipDate.

If you create a DirectLake semantic model using the Web Editor and add these two tables, you could rename the Date table to Order Date and build a relationship between it and the OrderDate column on the Sales table:

What if you want to analyse data by Ship Date instead? While you could create a physical copy of the Date table in your Lakehouse and add it to the model, there’s a more efficient alternative.

Using a tool like Tabular Editor, you can duplicate the Order Date table directly in the model and rename the new table to Ship Date.

You then have two tables in your semantic model connected to the same physical table in your Lakehouse, and you can create a relationship between this new table and the Sales table and then use the new dimension in your reports:

Conclusion

This approach is particularly useful for managing role-playing dimensions, which are often Date dimensions but can include other types as well. In fact, there are scenarios where adding the same fact table to a model multiple times becomes necessary. The advantage of having a single physical copy includes faster refresh times, reduced storage costs (though the savings for most role-playing dimensions may be minimal), and streamlined ETL processes.