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.
Table of contents
- What is Composite Model in Power BI?
- Composite model Power BI datasets provide flexibility by allowing a mix of Import and DirectQuery sources in the same report.
- Optimize your model and DAX calculations for better performance, especially when dealing with DirectQuery.
- Evaluate your specific use case and data source characteristics to determine if a composite model is the right choice.
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.
|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.
|• 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 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
|• 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.
|• 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.
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)
As of now, Power BI doesn’t have a specific feature to switch off composite models. Once you have turned on the composite models, it will be an irreversible process to switch it off. Power BI has a monthly update process and hence, it’s always recommended that you keep referring to the latest Power BI documentation to ensure that you can use any new features such as switching off if available.
Some of the limitations of composite models in Power BI include:
• Incremental refresh support for composite models is limited to SQL, Oracle, and Teradata connections only
• Currently, Live connect tabular sources for sources such as SAP HANA, SAP Business Warehouse, SQL Server Analysis Services (prior version of 2022), and Usage metrics are not supported with composite models
• You can’t use streaming semantic models in composite models
• If you are using DirectQuery, note that any calculated column on a DirectQuery table can only refer to other columns on the same table.
• Other limitations include limited or no features availability such as QuickInsights on a model if the table has a storage mode of DirectQuery
• Any poorly optimized models may experience performance issues.
• Cross-filtering between Import and DirectQuery tables is limited. Some operations might not be fully supported when crossing storage modes.
• Building and maintaining composite models can be more complex than traditional models, especially for users new to Power BI.
The importance of composite models in Power BI includes:
• Flexibility: Composite models allow users to leverage the advantages of both Import and DirectQuery modes, providing flexibility in handling large datasets.
• Optimized Performance: By using DirectQuery for certain tables and Import for others, users can optimize the performance of their reports, particularly for large datasets.
• Real-Time Data: DirectQuery enables real-time access to data, allowing users to analyze the most up-to-date information.
• Data Source Variety: Composite model Power BI datasets support combining data from various sources, giving users the ability to work with diverse datasets within a single report.
Key differences between composite models and regular models are highlighted below:
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 –