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.)?

[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. 

[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.