Did you know you can add the same physical table in OneLake multiple times to the same Direct Lake semantic model?
Let’s say you have two tables in a Fabric Lakehouse. One is a fact table called 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 about analysing by Ship Date though? You could create a physical copy of the Date table in your Lakehouse and add that to the model, but there’s another option.
If you connect to the model using a tool like Tabular Editor, duplicate the Order Date table and rename the new table 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:
This is handy for handling role-playing dimensions, which are most often Date dimensions but may be other types of dimension too. Indeed, I’ve sometimes found the need to add the same fact table to a model more than once. The benefit of only having one physical copy is reduced refresh time, lower storage costs (although for most role playing dimensions the savings will be negligible) and simpler ETL.
Is it possible to implement the same in power BI service ? instead of using Tabular Editor 3 software.
I don’t think so- I actually wrote this post two months ago and found a bug which has since been fixed- but it’s possible that the web based editor supports it now. You can edit Direct Lake models in Desktop now too of course.
You can use shortcuts but I think that’s less elegant. You can use the free Tabular Editor 2 to do this too.
Let’s compare role playing dimension, to an inactive relationship, with “use relationship” instruction as a way to switch scope of analysis,
What do you think is better, regarding report performance, and capacity usage ?
UseRelationShip does something slightly different to having two tables in a model, so you can’t compare them
Hi Chris, doesn’t it run the query twice though? So if you have 10 calendar tables duplicated won’t it execute the 10 physical queries and hold 10 copies in memory? Wasn’t clear, hopefully it knows it’s a virtual copy over a single import??
Yes, as far as the model is concerned these are two separate tables that both consume their own memory
And the same should be possible for creating a aggregated fact table when (if ever) user defined aggregations will be available in direct lake mode
Next step: add a pre-fix to all column names in the table, called “Order … (month/year/etc)” and “Ship ..(month/year/etc)”, to keep User informed easily. Is possible without PwerQuery..?
Ps, For the Dates specifically, one could use the “UseRelationship” dax statement alternatively, so you don’t need to duplicate the dates table. But, you would end up with multiple measures (advantage is you can show Order AND shipping date trends in same graph..).
Sander
This only works if your role playing dimension all contain the same rows. This is typically true for a date dimension but say you have a “person” dimension with roles for various kinds of people. Each role playing dimension should only have in it the persons for that role to keep the size of the dimension down and make picking from lists, etc more pleasant for the user. I do not think you can apply filters with this approach.
Great stuff – thanks.
Just to warn you that I am having issues with seeing the images in your posts (not just this one).
I can click and the image often opens (not always) in a separate browser tab, and then with a few more refreshes the images will eventually show up on the post. But it is tricky.
Curious if it is just me.
(fast cable provider internet with powerful desktop in UK).
I was able to create 2 role playing date dimensions but get the below error when refreshing the tables in the UI. I believe this is due to having 2 tables in the semantic model pointed to the same Delta table
{“error”:{“code”:”ExternalServiceFailed”,”pbi.error”:{“code”:”ExternalServiceFailed”,”parameters”:{},”details”:[{“code”:”ModelingServiceError_Reason”,”detail”:{“type”:1,”value”:”Error in ModelingEngineHost”}},{“code”:”ModelingServiceError_Location”,”detail”:{“type”:1,”value”:”ModelingEngineHost”}},{“code”:”ModelingServiceError_ExceptionType”,”detail”:{“type”:1,”value”:”InvalidOperationException”}},{“code”:”ModelingServiceError_Message”,”detail”:{“type”:1,”value”:”Sequence contains more than one matching element”}}]}}}
Similar experience here. I was able to refresh the model but when trying to save any further changes from the Edit Tables screen resulted in the above error.
I posted the message on Nov 20, 2024.
I have been using Tabular Editor to edit tables (either update table schema or add additional tables).
I am only able to do so via Tabular Editor. Adding RPDs introduces the error in the UI.
Hopefully Microsoft adds support for RPD in the Fabric UI.
Thanks, I reported the error and it’s being fixed
Same issue here and looks like there’s no solution yet…
Did you find a solution?
Thank you for reporting the error, Chris! This will benefit many organizations.
Is there an update of this error? or another workarround?
I have been told that all known bugs were fixed a few months ago. However if you’re still seeing errors I would love to get a repro.
Hello Chris,
Thanks for your reply. I followed your blog and was able to create duplicates of the tables (via Tabular) in Power BI Desktop without any issues. However, when I synced my data model in Fabric, I encountered the following error:
{“error”:{“code”:”ExternalServiceFailed”,”pbi.error”:{“code”:”ExternalServiceFailed”,”parameters”:{},”details”:[{“code”:”ModelingServiceError_Reason”,”detail”:{“type”:1,”value”:”Error in ModelingEngineHost”}},{“code”:”ModelingServiceError_Location”,”detail”:{“type”:1,”value”:”ModelingEngineHost”}},{“code”:”ModelingServiceError_ExceptionType”,”detail”:{“type”:1,”value”:”InvalidOperationException”}},{“code”:”ModelingServiceError_Message”,”detail”:{“type”:1,”value”:”Sequence contains more than one matching element”}}]}}}
Please try again later or contact support. If you contact support, please provide these details.
The only difference in my setup is that we have our gold layer on a warehouse instead of a lakehouse. As a result, we cannot use this approach, and I now have to create views of my dimensions for role-playing purposes in my star schema architecture.
Additionally, another issue we’re facing is that while it’s possible to create folders in Tabular to group our dimensions and facts, these folders are not visible in the Power BI Semantic Model. This is something we need as well.
Thanks for providing a working solution, as this is crucial for us.
Best regards, Claudia
Hello Chris,
Thanks for your reply. I followed your blog and was able to create duplicates of the tables (via Tabular) in my data model in Power BI Desktop, without any issues. However, when I synced my data model in Fabric, I encountered the following error:
{“error”:{“code”:”ExternalServiceFailed”,”pbi.error”:{“code”:”ExternalServiceFailed”,”parameters”:{},”details”:[{“code”:”ModelingServiceError_Reason”,”detail”:{“type”:1,”value”:”Error in ModelingEngineHost”}},{“code”:”ModelingServiceError_Location”,”detail”:{“type”:1,”value”:”ModelingEngineHost”}},{“code”:”ModelingServiceError_ExceptionType”,”detail”:{“type”:1,”value”:”InvalidOperationException”}},{“code”:”ModelingServiceError_Message”,”detail”:{“type”:1,”value”:”Sequence contains more than one matching element”}}]}}}
Please try again later or contact support. If you contact support, please provide these details.
The only difference in my setup is that we have our gold layer on a warehouse instead of a lakehouse. As a result, we cannot use this approach, and I now have to create views of my dimensions for role-playing purposes in my star schema architecture.
Additionally, another issue we’re facing is that while it’s possible to create folders in Tabular to group our dimensions and facts, these folders are not visible in the Power BI Semantic Model. This is something we need as well.
Thanks for providing a working solution, as this is crucial for us.
Best regards, Claudia
Does this typically take a while to register if I am connecting to this model in desktop? The measures referencing this role-playing dimension are not working in Direct Lake mode. Or is there something else I can check for? Thanks for all your help!
What do you mean by not working? Is there an error message you can share?
Thanks for a great post. However, is there any way of doing this WITHIN the service as I cannot use tabular editor? I have not been able to find any ways of duplicating the dimension or load it twice.
No, I don’t think there is a way to do this in the Service at the moment. It may be possible in Desktop with TMDL View and the new ability to create Direct Lake models there though.
I am getting an error when trying to accomplish this with Tabular Editor 2.25. It works, I create the tables, but when I try to refresh the semantic model in Live Edit Mode in Power BI Desktop I get this error Sequence contains more than 1 matching element. I can do everything I need using tabular editor but I get this error trying to refresh the model (which should be just meta data since it is DirectLake Mode). I simply used Duplicate table, renamed it, then saved it back using tabular editor. But I can’t refresh it?
Same problem here:
Hello Chris,
Thanks for your reply. I followed your blog and was able to create duplicates of the tables (via Tabular) in my data model in Power BI Desktop, without any issues. However, when I synced my data model in Fabric, I encountered the following error:
{“error”:{“code”:”ExternalServiceFailed”,”pbi.error”:{“code”:”ExternalServiceFailed”,”parameters”:{},”details”:[{“code”:”ModelingServiceError_Reason”,”detail”:{“type”:1,”value”:”Error in ModelingEngineHost”}},{“code”:”ModelingServiceError_Location”,”detail”:{“type”:1,”value”:”ModelingEngineHost”}},{“code”:”ModelingServiceError_ExceptionType”,”detail”:{“type”:1,”value”:”InvalidOperationException”}},{“code”:”ModelingServiceError_Message”,”detail”:{“type”:1,”value”:”Sequence contains more than one matching element”}}]}}}
Please try again later or contact support. If you contact support, please provide these details.
The only difference in my setup is that we have our gold layer on a warehouse instead of a lakehouse. As a result, we cannot use this approach, and I now have to create views of my dimensions for role-playing purposes in my star schema architecture.
Additionally, another issue we’re facing is that while it’s possible to create folders in Tabular to group our dimensions and facts, these folders are not visible in the Power BI Semantic Model. This is something we need as well.
Thanks for providing a working solution, as this is crucial for us.
Best regards, Claudia
I have the same issue. I Have a warehouse as data storage and not a lakehouse, I think that will be the cause.