blog

Composite models in Power BI

A lot of organizations deploy multiple central data models for their analysts. Those data models are centrally managed and maintained, which guarantees the quality and correctness of the data. Often people want to expand those enterprise data models with their mappings of additional data. Also, people want to combine data from 2 data models into 1 report (f.e. combine finance with HR data).

Leveraging organizational datasets with your local models

In the past, those 2 problems could not be resolved without creating a new data model yourself or creating ad hoc reports via exports in f.e. excel. This meant recreating queries and measures from the original data model, which makes it error-prone and no longer secure and qualitative. Data will be duplicated into multiple data models, and risks occur about the correct usage of KPI definitions into the different reports. 

Since the introduction of Composite models in December 2020, you have a valid alternative for the above-mentioned cases. Composite models give you the possibility to combine multiple data models into 1 new (linked) data model. Also, you can expand an existing data model with your tables and measures without changing the original (enterprise) data model. This allows key users to fully exploit the advantage of centralized data models, with the flexibility to add their own KPIs and mappings to increase self-service reporting. 

 

Some advantages of this new approach: 

  • Focus on the single source of truth. No duplicate dimension tables, fact tables, and measures when there is already a controlled dataset available. 
  • Key users can add their tables and measures, linked to the controlled dataset 
  • Less data duplication since models are linked to the original models 
  • Reduce time spent on modeling data and validating the outcome of measures 
  • Fewer risks on changed KPI definitions throughout the different reports 
  • Original and composite data models are being refreshed at the same time. 
  • Changes in the original models will also occur in the composite models 

 

There are however some limitations and important remarks to take into consideration: 

  • A good star Schema design with conformed dimensions is needed to link multiple data models 
  • The underlying datasets will have an impact on your composite model. Any changes to the underlying models will affect the composite model as well. 
  • Make sure table names are specific and well defined. Naming conflicts between tables could break certain measures in your model if there are duplicate names. 
  • Some data sources are not supported for composite models (f.e. SSAS Multidimensional, SAP Hana, and SAP BW, …) 

Interesting reads about this topics

More information can be found on the Microsoft website using this link.

More information on combining power bi datasets can be found via this link.

A more in-depth article can be found on the website of sqlbi.

Grow your career

Come join us

Expand your business

Let's work together

Sign up for the latest industry insights
Set preferences