Forum Discussion
Composite model relationships / shared dimension tables
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.)?
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.
10 Replies
dhannaa , the dimension should be only one. You can prefer to have dimensions in dual or import mode.
if needed, append dimensions data.
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.
Thank you Anonymous 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
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 ?
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.
