How to Create a Composite Model in Power BI

Building reports in Power BI often involves a choice: import all your data for top speed, or use DirectQuery for real-time access to massive datasets. But what if you need both at the same time? That’s where composite models come in. This guide will walk you through exactly what composite models are, why they are so powerful, and how to create your first one step-by-step.

What is a Composite Model in Power BI?

A composite model is a feature in Power BI that allows you to combine data tables from different storage modes within a single data model. In simple terms, you can have one table working in high-performance Import mode and another table connected live to a database in DirectQuery mode, all within the same report.

Think of it like this: your report is a car.

• Import mode: It's like having a full tank of gas. It's fast and self-contained, but the information (the fuel) is only as current as the last time you filled up (refreshed the data).
• DirectQuery mode: It's like being directly connected to the gas pump at all times. You always have the most current fuel level, but your performance is limited by the speed of the pump (the source database).

A composite model lets you build a hybrid car. You can have a small, high-octane "pony tank" (Import) for your critical or static data and stay connected to the main pump (DirectQuery) for your real-time, high-volume data. It breaks the "all-or-nothing" rule, giving you a flexible way to balance performance with data freshness.

Key Benefits of Using a Composite Model

So why go through the trouble of creating a composite model? The benefits are significant, especially for complex or large-scale reporting projects.

1. Combine Multiple Data Sources Seamlessly

The most obvious benefit is the ability to merge data sources that were previously incompatible. Imagine your company’s massive transaction log lives in an Azure SQL database and is far too large to import. But your sales team’s monthly targets are stored in a simple Excel file. A composite model allows you to bring both into Power BI, create a relationship between them, and visualize your actual sales versus your targets in a single chart — with the sales data remaining live at the source.

2. Optimize Report Performance

DirectQuery reports connected to slow data sources can be painfully sluggish. With composite models, you can adopt a "star schema" approach to boost performance. You can set your large, frequently updated fact tables (like a sales transaction table) to DirectQuery, while your smaller, more static dimension tables (like product lists, customer details, or calendar tables) can be set to Import mode.

When a user interacts with a report slicer that uses an imported dimension table, the filtering happens almost instantly from Power BI's in-memory cache. This makes your reports feel much snappier, as Power BI only needs to send a pre-filtered query to the slow DirectQuery source.

3. Add Columns and Data to DirectQuery Sources

A limitation of traditional DirectQuery is that you often can't add bespoke data to it. For instance, you can't just mash up an Excel sheet with your live SQL view. Composite models solve this. You can create a "product details" table in Excel, import it, and create a relationship to your DirectQuery "sales" table on the Product ID column. This enriches your live data without needing to beg a database administrator to add new columns to the main database.

Before You Begin: Important Considerations

Before diving in, there are a couple of things you need to know to ensure a smooth process.

• Enabling the Feature: In some older versions of Power BI Desktop, composite models might be a preview feature that needs to be manually enabled. Go to File > Options and settings > Options > Global > Preview features and ensure "DirectQuery for Power BI datasets and Analysis Services" is checked.
• Understanding Storage Modes: You must be comfortable with the three main storage modes.

Step-by-Step Guide: Building a Composite Model

Let's build a simple composite model. Our goal is to analyze real-time sales data from a SQL database against a static list of product categories managed in a local CSV file.

Step 1: Connect to a DirectQuery Source

First, connect to your large, primary data source which you don't want to import completely.

1. Open Power BI Desktop and click Get Data. For this example, we’ll select SQL Server.
2. Enter the server and database details.
3. In the connection dialog, crucially, you must select DirectQuery as the "Data Connectivity mode" and click OK.
4. From the Navigator window, select the table you need, for instance, a Sales table, and click Load.

Power BI will now load the metadata for the Sales table, but not the data itself. In the bottom-right corner of the window, you'll see a message indicating the storage mode is DirectQuery.

Step 2: Add an Imported Data Source

Now, let's bring in our smaller dimension table directly into the Power BI model.

1. Click Get Data again, but this time select Text/CSV.
2. Navigate to your local CSV file, which might be named ProductCategories.csv.
3. Power BI will show you a preview. Click Load. Because this is a file source, Power BI automatically uses Import mode. No option to choose will be presented.

At this point, Power BI might show a "Potential security risk" warning. This is because it needs to send data from your imported file to the DirectQuery source for filtering. This is a necessary part of the process, so click OK.

Step 3: Define the Relationship

With both tables in your model, you need to tell Power BI how they relate to each other.

1. Go to the Model view on the left-hand panel. You will see both your Sales (DirectQuery) and ProductCategories (Import) tables.
2. Identify the common column that links them, for example, ProductCategoryKey.
3. Click and drag the ProductCategoryKey column from the ProductCategories table and drop it onto the ProductCategoryKey column in the Sales table.
4. Power BI will create a relationship. Double-click the line connecting the tables to inspect it. You’ll see that you've formed a connection between an imported table and a DirectQuery table – this is the core of a composite model!

Step 4: Use the 'Dual' Storage Mode for Flexibility

Look at your ProductCategories table in the Model view. By default, its storage mode is Import. Let’s change it to Dual to optimize how it works with our DirectQuery table and any future imported tables we might add.

1. In the Model view, select the ProductCategories table.
2. Go to the Properties pane on the right. If it's not visible, go to the View tab and check the 'Properties' box.
3. Expand the Advanced section.
4. Click the Storage mode dropdown and change it from Import to Dual.

Why do this? When ProductCategories is set to Dual, Power BI can treat it more efficiently. If you use a slicer with the "Category Name" field from this table, Power BI can use its in-memory cache to filter a visual showing imported data. When that same slicer filters your live Sales table, Power BI understands that it needs to behave as a DirectQuery source instead.

Step 5: Build Your Report

Go back to the Report view. You can now build reports using fields from both tables seamlessly. You could create:

• A slicer using the CategoryName field from your imported ProductCategories table.
• A bar chart showing SalesAmount from your DirectQuery Sales table.

When you click on a category in the slicer, Power BI will instantly filter the bar chart by sending a refined, efficient query to your SQL Server database. Your report will be fast, and the data will be live.

Best Practices for Composite Models

While powerful, composite models can introduce complexity. Follow these tips to keep things running smoothly:

• Keep Dimensions Small: The main benefit comes from importing small dimension tables. Only import what’s necessary to avoid slowing down your model.
• Mind the Relationships: The relationship between an imported and DirectQuery table can be complex. Be mindful of "weak" relationships, indicated by a dotted line, as they have certain DAX limitations.
• Test Performance Thoroughly: Use Power BI's Performance Analyzer to see how your visuals behave. A poorly designed composite model can sometimes perform worse than a pure DirectQuery model, so always test.
• Use Dual Mode Wisely: Dual mode is perfect for shared dimension tables (like Date, Product, Customer) that need to filter both imported and DirectQuery fact tables. Using it correctly helps Power BI generate the most efficient query plans.

Final Thoughts

Power BI composite models represent a major leap in flexibility, allowing you to blend the blazing speed of Import mode with the real-time capabilities of DirectQuery. This opens up new possibilities for creating sophisticated, high-performance reports that combine massive datasets with local files, giving you the best of both worlds.

Building models and wrangling data from different sources is a huge part of analytics. We built Graphed to simplify this process for marketing and sales data. Instead of configuring storage modes and relationships, you just connect your platforms like Google Analytics, Shopify, and Salesforce. From there, you can ask for the dashboard you need in plain English — like "create a dashboard showing Facebook Ads spend versus Shopify revenue by campaign for the last month" — and get a live, automated report in about 30 seconds.