Direct Query (multiple semantic models), multiple Date Table, and relationship

[Tom_Y]

Hi all, any comment will be appreicated. I don't want to walk a wrong path... and regret in near future.

I have four power BI reports, import mode, just from a lot of excels, each having its own semantic model, and their own date tables (identical, i just copy the DAX from one to another). The models didn't have any interaction.

Now I need to extract data from Model A (e.g. 10 years target) and Model B (next fiscal year business plan) into Model C (Actual performance) so that I can make the visual (e.g. line and bar chart) and compare that 3 figures.

Now I've started to create relationship between Model A (a few tables, not all), Model B (a few tables, not all) and (Model C Date Table) through direct query (OneLake? I see the logo when connecting). Most of the time the relationship works, but sometimes it says existing relationship in Model A prevents me doing it because tables were linked to their original Date table and logic is contradictory.

How do people do it in the industry? Do they just create one Date table as a separate semantic model and link all other models to it? Or is it normal to allow many date tables in many semantic models, and only linking when needed? My biggest concern is that I don't want to create everything again in future when it's too late.

In the short run I can copy and paste everyting into one single semantic model, but I can foresee I will need to link 10 semantic models together for a "Whole orgnaiztion report" in near future.

And feedback/ blog/ youtube will be highly appreciated.

[lbendlin · Accepted solution]

How do people do it in the industry?

If you have dimension duplication caused by composite models then you pick the dimension from one of the models and remove it from all others. Unless your business scenario requires it you should only have one calendar dimension table in your combined data model.

[Tom_Y]

@lbendlin Thank you very much! It's exactly what I mean, "dimension duplication caused by composite models". I didn't know the terms so I can't even search it.

So do you have any quick tips where I should place the "Organization-wide Date Table"? do people make one separate semantic model just for date dimension, or shall I keep my Date Table in the most important semantic model and link other models to that Date Table?

Thank you very much!

[lbendlin]

That's totally up to you. Doesn't seem to make a performance difference, at least not from my tests. YMMV.