Composite Model in Power BI

What is Composite Model in Power BI?

In Power BI, a composite model allows you to combine different storage modes (Import, DirectQuery, and Dual) in a single report. It means you can have some tables or data sources loaded into memory (Import) while others are queried directly from the data source (DirectQuery).

The composite models in Power BI comprise of 3 related features which include:

• Composite Models
  • This model allows a report to reference two or more data connections from different data source groups. The data source groups can have different combinations, such as one or multiple DirectQuery connections and an import connection, two or more DirectQuery connections, and so on.

• Many-to-many relationships
  • This model allows you to establish many-to-many relationships between the tables without any need to have unique values across multiple tables
• Storage mode
  • This mode enables you to define the backend data sources, which in turn improves performance and load reduction.

How to build the composite model in Power BI?

The composite model Power BI dataset allows you to combine two or more storage modes in a single report, enabling you to leverage the benefits of both DirectQuery and Import modes. It can be helpful when dealing with large datasets that are not feasible to import entirely into Power BI, but you still want to take advantage of some features provided by the Import mode.

Using composite models, it’s easy to connect with multiple datasets in Power BI by

• Importing data to Power BI
• Connecting directly to the source data repository using DirectQuery

Here’s a step-by-step guide on how to build a composite model in Power BI, including setting up relationships, using DirectQuery to Power BI datasets, and creating calculations with DAX.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Relationship in Power BI Composite Model

Define relationships between tables as needed for your analysis. Relationships are crucial for Power BI to understand how data in different tables is related and accordingly perform any logical operations on the back of these relationships.

Once the data is loaded, Power BI automatically determines the relationship between the tables and creates the relationship. You can view these relationships by navigating the model view in Power BI.

To establish relationships in the Power BI composite model, follow the steps highlighted below:

Step 1: Navigate to the Modeling tab in Power BI and click on Manage relationships

Step 2: Click on New in the Manage relationships window to create relationships between the tables

Step 3: Define the relationship across all the tables using the steps highlighted above.

Step 4: Once the relationships are defined, you can view them by navigating to Model view in Power BI

DirectQuery to Power BI Datasets

Using DirectQuery, you can establish a data connection to your data sources. It enables you to create a composite model Power BI dataset (For example, .pbix file) that can combine data from multiple DirectQuery sources or a combination of DirectQuery sources and data import.

The steps involved in DirectQuery connection are as follows

Step 1: Establish connection to DirectQuery sources available in your organization or enterprise-level servers (SQL server, Impala, etc.) by navigating to Get data à Power BI datasets

Choose the datasets from the available list of sources in your organization to connect using DirectQuery.

Step 2: Import data using the Get data option in the Home tab Import data using the Get data option in the Home tab. It can be any manual data or Excel or CSV etc., i.e., using import.

Calculations and DAX

You can create calculated columns and measures using Data Analysis Expressions (DAX) in Power BI. These calculations can be used to derive new insights from your data.

To create a new measure or calculated column, follow the steps highlighted below:

Step 1: Click on the New measure or New column option in the Model view.

Step 2: In the formula bar, enter your DAX formula to perform any calculations, such as aggregations and other calculated columns.

You can also create calculated tables to model using DirectQuery. DAX formula on the calculated table can reference either imported or DirectQuery tables or a hybrid setup.

Note: You must set up storage mode for each table in the composite model. The storage mode primarily indicates whether the connection is based on import or DirectQuery. You can view the storage mode of a dataset by selecting a dataset, navigating to the property pane, and selecting the properties.

Once the above setups are completed, you can perform thorough testing to validate your composite model and ensure the results are in line with your expectations. You can further optimize the performance as per your datasets and the relationship model.

The final step includes publishing your Power BI file to the Power BI service for collaboration and sharing with others.

It is always recommended that you refer to the latest Microsoft documentation on Power BI regularly to check for any changes to the above features.

Factors to consider when building Power BI composite models

The key factors to consider when building Power BI composite models.

Factors	Description
Data Source Types	• Understand the characteristics and limitations of your data sources. • Some data sources may not support DirectQuery or certain features may be limited.
Performance Considerations	• Be aware of the performance implications of using DirectQuery, especially for large datasets. • Optimize DAX calculations and model design for efficient query performance.
Security and Permissions	• Ensure proper security and permissions are set for both Import and DirectQuery sources. • Consider how security settings affect data access for different users
Renaming	• Renaming the semantic models that are used by composite models or renaming their workspaces is not recommended. • Renaming a semantic model or workspaces may result in connection breaks used in composite models
Governance	• Using a single version of the truth model as the composite model is not recommended given its dependency on other data sources or models. Any updates to these models can break the composite model.
Data Lineage	• Ensure that you have thoroughly used the data lineage and semantic model impact analysis before publishing the changes to composite models.
Updates to Schema	• Consider refreshing your Power BI composite models for every change made in the schema in your upstream data sources post which can be republished to the Power BI service.

Use cases

Composite models can be considered useful in the below scenarios.

• If your model is a DirectQuery model and the requirement is to improve performance, you can improve it by configuring relevant storage for each applicable table using the composite model as well as adding user-defined aggregations.
• If the requirement is to combine a DirectQuery model with additional data, then that dataset is required to be imported into the data model. The imported data can be loaded from a different dataset or even from the calculated tables.
• Composite models can be used to combine 2 or more DirectQuery data sources (could be relational databases or other tabular models) into a single model

Important Things to Note

• Though Power BI composite models can be effective in solving design problems, they can result in slow performance. In certain cases, the calculation results may also be unexpected or inaccurate
• While import mode can be a good option for developing a model for best performance and design flexibility, it’s ineffective for large volumes of data or reporting requirements on near real-time data. DirectQuery can be an effective solution for such scenarios
• Carefully evaluate the implementation of composite models to your modeling requirements. While it supports the model-level integration across different data sources, the biggest challenge it faces is design complexity
• Check the compatibility of your data sources with composite models.
• Thoroughly test performance and regularly monitor queries to identify and address any issues.
• Document the model structure, relationships, and storage mode choices for future reference.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Composite model in Power BI. Here we learn how to build composite model in power BI, its factors, use cases, relationships and calculations. You can learn more from the following articles –

• Power BI Dataflows
• Power BI Paginated Report
• Power BI Calculated Table