Composite model relationships / shared dimension tables

[dhannaa]

I'm building a report with a composite model of two already quite extensive datasets. What is the recommended way to combine these two datasets as they both include a couple of identical dimension tables (date, cost centers etc.)?

[Tom_Y]

@dhannaa How did you solve the problem? I know it's 2 years ago. I have the same problem, quite a few extensive well established semantic models, but duplicated date table in each of them. Where do you finally put your date table?

Thanks in advance

[supriyapandey19]

Any updates on how to achieve this?

[Tom_Y]

I guess a practical way is to create a new semantic model and extract data from existing ones. Then, re-create the relationship to a particular dim table in the new model.

e.g.

model A, date table A

model B, date table B

new model, tables in model A shall has been linked to date table A. Now link tables in model B to date table A. Or try to link date table B to date table A, bi-directional.

Now I prefer a single model for everything...

good luck.

[supriyapandey19]

Can you explain in detail? Because when I am linking Tables in Dataset A to Date Table A and all tables in Dataset B to Date Table A, it is creating ambiguity for cross source relationships.

How to do handle this?

[MattWills]

This is the only way we've got around the issue, by in effect bringing in the common dimension with each linked dataset, plus also a new copy of the dimension then linking that to the two external dimensions - it works but I appreciate if you've lots of dimensions it's going to get messy. 

[supriyapandey19]

Have you tried this? Did it work?

[Guillaume_]

I have the same question and requirement and the solution provided does not answer the request from the OP.

Any idea? Is it actually possible in Power BI ?

[v-xiaoyan-msft · Accepted solution]

Hi @dhannaa ，

We recommend that you use the star schema, consider that each Power BI report visual generates a query that is sent to the Power BI model (which the Power BI service calls a dataset). These queries are used to filter, group, and summarize model data. A well-designed model, then, is one that provides tables for filtering and grouping, and tables for summarizing. This design fits well with star schema principles.

To build a star schema for your data model, you may refer to How to design a Star Schema and design tips ,and more details.

On the other hand, smaller models achieve faster data refresh, resulting in lower latency reporting, higher dataset refresh throughput, and less pressure on source system and capacity resources. Smaller table row counts can result in faster calculation evaluations, which can deliver better overall query performance.

You can reduce the size of the data model by focusing on the following ways：

• Remove unnecessary columns
• Remove unnecessary rows
• Group by and summarize
• Optimize column data types
• Preference for custom columns
• Disable Power Query query load
• Disable auto date/time
• Switch to Mixed mode

Also a composite model can be optimized by configuring table storage modes and by adding aggregations.

Hope it helps,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[dhannaa]

Thank you @v-xiaoyan-msft  for your thorough answer.

I'm actually having two datasets with star schemas (sales + finance) and they already both contain a date table as one dimension table. I'm just wondering here what is the best possible way to build the composite model here to achieve just one date table as a dimension. Are there some examples for building this kind of composite model, I haven't found any?

Jenni

[amitchandak]

@dhannaa , the dimension should be only one.  You can prefer to have dimensions in dual or import mode.

if needed, append dimensions data.