Composite Model in Power BI; DirectQuery and Import Data Combined

In the early days of developing Power BI solutions, you could choose DirectQuery or Import Data connection types, but not both. In 2018, Power BI added a breakthrough feature called Composite Model. Now, using this feature, you can have part of your Power BI dataset connected directly from a data source, and another part to be imported. Along with this change, you can also choose the storage mode for each entity. In this post, you will learn about the composite model; what it is and how it works. If you want to learn more about Power BI; read the Power BI book from Rookie to Rock Star.

Composite Model; What does it mean?

A composite model in Power BI means part of your model can be a DirectQuery connection to a data source (for example, SQL Server database), and another part as Import Data (for example, an Excel file). Previously, when you used DirectQuery, you couldn’t even add another data source into the model.

Composite Model means now you can have a model, that very large tables of that are coming from the DirectQuery connection, without the need for importing, and small tables to be imported to be accessible quickly.

Composite Model combines DirectQuery and Import Data

Why Composite Model?

The important question to ask is: “Why should you use the Composite Model?” To answer this question, you first need to review the benefits of Import and DirectQuery modes. Import Data is good for super-fast data analysis and reporting and flexibility, while the DirectQuery is good for big data tables and the data freshness.

DirectQuery and Import Data have different advantages

Now the Composite Model combines the good things of both Import and DirectQuery into one model. Using the Composite Model, you can work with big data tables using DirectQuery, and still import smaller tables using Import Data.

The composite model improves the performance and works with big data tables at the same time

Using the Composite model, you can have tables with different connection types, some Import Data and some DirectQuery.

In the composite model, some of the tables can be Import Data mode, and some others DirectQuery

Having big data tables with the DirectQuery and smaller tables with Import Data is much better than doing everything pure DirectQuery, because in the pure DirectQuery mode, even for querying data from small tables, the performance would be still slow. In the composite model, the smaller tables can be used as Import Data to improve the performance. This also comes with a new storage mode; Dual, which you will learn about it a bit later in this post.

How does the composite model work?

Here is a Power BI file using a DirectQuery connection to a SQL Server database (AdventureWorksDW in this example);

DirectQuery Source

I have selected the FactInternetSales table from this database.

Selecting DirectQuery tables

In Power BI Desktop, after loading the table, you can see on the right-hand side bottom corner, that we are using the DirectQuery storage mode;

DirectQuery mode

Now for the second table, I get data from Excel,

Get data from Excel workbook

And select DimCustomer from the Excel file (this excel file is the Excel version of the AdventureWorksDW database, you can download it from the link in my other blog posts). As soon as you bring a table from another data source, you will be notified that there is a potential privacy risk that the data from one data source, may be used to pass parameters into another data source.

Potential security risk warning when combining data from different sources

After saying OK to the above warning, you will get the new data table loaded into Power BI, and you can see that the mode changes to Mixed Mode.

Mixed-mode

The mixed-mode of the composite model means that the fact table in our example is NOT LOADED into Power BI. It will be queried each time from the SQL Server database table. The dimension, however, is already imported into the Power BI model and needs to be refreshed time by time. If you refresh your model, you will see that the only table(s) refreshing the data are Import Data tables.

Only Import Data tables will be loaded into Power BI

Using this option, you will be able to see the Data tab (which you couldn’t have done in mere DirectQuery mode previously), and you can see only Import Data tables there.

The Data tab shows Import tables only

You can have the big data tables of the model, coming from a DirectQuery source, and it doesn’t matter if it is billions of rows, because they are not loading into the Power BI. Small tables can be imported into Power BI for better performance.

Storage Mode: Dual

Considering the Composite mode explained above, you probably ask yourself this question where the data is stored in every table, and how they are working behind the scene? Can you select a table, and understand what is the storage mode for that, the answer is yes. Each table in Power BI has a storage mode. The storage mode can be; Import, DirectQuery, or Dual.

If you go to the model tab in the Power BI Desktop, and then click on a table, in the Properties pane, under the Advanced section, you can see the Storage mode. There are three options: Import, DirectQuery, and Dual.

Storage modes

Import and DirectQuery in the storage modes above are understandable enough. Import means, that every time you refresh the data, it will be imported from the data source, DirectQuery means it will be every time queried directly from the source no matter what visual you use. So what is the Dual mode?

If you try to change the mode of an Import table to Dual, you see that this change is not possible.

Storage mode cannot be changed for Import tables

Dual Mode is only an option for DirectQuery data sourced tables.

When you change the storage mode of a DirectQuery table to Dual, it will create a copy of that table in the Power BI model. But unlike Import Data, there is a copy in the memory, and there is still the main table in the DirectQuery source.

Changing to Dual storage mode

The Dual storage mode, works as DirectQuery or Import Data, depending on what other tables related to it are used in a visualization. If you have a visual that has columns from Import tables and a dual table, then the dual table will act like an Import. If you have a visual that has columns from DirectQuery tables and a dual table, then the dual table will act like a DirectQuery table. This ensures that you get the best performance when working with smaller tables (such as dimension tables) in a dual storage mode.

Modeling for Power BI Composite model

Here are some of the things to consider when doing data modeling with a Power BI composite model

Relationship in Power BI Composite model

You can create relationships simply between tables coming from different data sources and using different storage modes. The diagram below shows how DimCustomer (Import) is connected to FactInternetSales (DirectQuery) and also DimDate (Dual).

Relationship in a Power BI composite model

The cardinality of the relationship can be anything you want; One-to-many, Many-to-one, One-to-one, and Many-to-many.

Calculations and DAX

Calculated tables are available in the composite model. These tables will be in Import mode, and their expression can be anything that the DAX allows. You can also create calculated tables using Power Query Editor.

Calculated tables will be refreshed at the scheduled refresh time of the Power BI dataset.

Depending on where you create the calculated column; they can have some limitations. If you create a calculated column in an Import table or a calculated table, then there will be no limitation. However, if you create a calculated column in a DirectQuery table, then in the expression for the column only columns from the same table can be used.

DirectQuery to Power BI Datasets

In the composite model, you cannot only use DirectQuery to SQL Server, Oracle, and some other DirectQuery sources, But you can also create a DirectQuery connection to a Power BI Dataset. This is different from the Live connection to the Power BI Dataset. The DirectQuery to the Power BI Dataset will enable you to bring the Power BI dataset’s data into a composite model, which gives you the ability to do further data modeling in the chained dataset. This concept is explained in this post.

DirectQuery to Power BI Dataset in a composite model

Summary

In summary, combining DirectQuery tables and Import Data tables in one Power BI model is called Composite Model or mixed-mode. With this feature, you can also leverage the option for using Dual storage mode to keep a copy of a DirectQuery table in memory for answering quick slicing and dicing questions.

DirectQuery was not a common method when it comes to working with data in Power BI, especially because with this mode you couldn’t combine multiple data sources, but with the composite model, the DirectQuery becomes faster. If you have big data tables, then I definitely recommend exploring the composite model.

The composite model can even go one step further in the performance when aggregations are used. You will learn more about that in this post.

The composite model is better than a pure-DirectQuery approach. This option gives you performance and big data both in one data model.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 yearsโ€™ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Rezaโ€™s passion is to help you find the best data solution, he is Data enthusiast.

29 thoughts on “Composite Model in Power BI; DirectQuery and Import Data Combined

  • Awesome! Thanks for taking the time to share the news. I wish I was at the summit, sigh, maybe next time.

  • I have recently discovered the benefit of using Direct Query mode. Where you really need Direct Query is with Mobile Apps. And if the new mixed mode model cannot be published to the Web Service, then it renders the facility largely redundant. Mixed mode needs to be able to Publish to Powe BI service.

  • I was on the lookout for this option just a few days ago. The rate at which Power BI is evolving to deliver is simply amazing.

  • Hi Reza, in your opinnion what should be some business use cases to design some tables in Import mode and others in DirectQuery mode for the same data model.

    • Hi Brian
      There is a use case. Consider you have a huge table, billions of rows, if you import that table into Power BI it may exceed 1GB file size. so you have to use DirectQuery. However, using DirectQuery will limit your ability to add other data sources into the model. with this new Composite model, you can do that.

  • Hi Reza
    Great walk through of Composite Models.
    Any idea when it will be fully implemented? Currently not able to publish to service which unfortunately makes it unusable in a lot of cases.

  • Hi,
    Nice walkthrough! But i have query about the composite model.
    I can import multiple direct query data at the same time like multiple SQL Service Analysis Services / Power BI Datasets / Individual File (Excel, Flat File).

    By enabling composite mode I imported two power bi dataset into one Power BI desktop file.
    Both have geography table which contain State column.

    How can i create a State filter which should filter both datasets at once?
    Thanks,
    Ankur

    • Hi Ankur
      At the moment, with the Composite model, you cannot have it on SSAS (or Power BI dataset) connections, because those are Live Connections. Composite model is only supported on the DirectQuery connection. It MAY become later available for live connection too, but we don’t have it yet.
      Cheers
      Reza

  • Very good blog. Thanks a lot.
    One question considering the dual mode (and storing a copy of the direct query in the power bi model). This requires the same resources as directly using import and is also more slowly. So if we have the option between dual and import we would always choose import. So I do not understand the use case? For Big data we also would not be able to use dual right? Could you help me here?
    Thanks again and please keep writing ๐Ÿ˜‰

    • Hi Klaas
      There are much more details about how the Dual storage mode works, it is quite different with Import. I am writing a blog series next week about aggregations and one of those is all about Dual storage mode. you will learn how important it is through that in full details. stay tuned for that.
      Cheers
      Reza

  • Nice topic. Thanks.

    Power BI dashboard tiles that are based on direct query will be refreshed automatically every 15 minutes. Power BI dashboard tiles that are based on import will be refreshed whenever the dataset is refreshed. For me that is a reason to work with mixed mode. For some values I force the dashboard tile to refresh by basing it on direct query data. For other tiles (where refreshing with the dataset refresh is enough) the values are based on import data.

    • Do you mean having Power BI dataflows as the DirectQuery source? that is not an option yet, but I believe it is in the backlog and will come sometimes in the future.
      Cheers
      Reza

  • Composite model is not available in ‘power bi desktop(2019) optimized for reporting server’, which makes it impossible to get data from 2 different database in direct query mode, as my tables are huge so cant use import mode.

    • Hi Susant,
      I don’t think the composite model is available for the report server version yet. we might need to wait for a few more months
      Cheers
      Reza

  • I am connecting to a Power BI dataset. That is a live connection. That works fine. However I cannot import anything as all the external data options are greyed out (manage queries only shows data source settings). I thought this was possible with composite mode. Any suggestions as to what I am doing wrong?

    • Hi Carel
      Composite Mode at the moment is only supported using DirectQuery (not Live connection)
      However, the same ability is underway to come for the live connection, just wait a few more months ๐Ÿ™‚
      Cheers
      Reza

  • Hi Reza,

    I have one business use case which is currently going to implement in my project. i have some tables having huge data(2,11,36,789). some of tables are having (50 lakhs) of records and some thousands of records. as you explain in blog i tried to implement composite model . But in version of power bi desktop July 2020, i didn’t find the composite mode option in preview features in file –> options. And reports need to run on daily base schedules. by using import connection taking long time to load data into power bi . by using direct query we have lot of limitations.so could you please suggest better approach to do this.

    Thanks ,
    Raja

    • Hi Raja
      the composite model is not in preview anymore.
      That means you don’t need to enable it anymore in the options. it is enabled by default
      you need to bring some tables DirectQuery and some import

      Cheers
      Reza

Leave a Reply

%d bloggers like this: