Composite model relationships / shared dimension tables

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.