Composite Model; DirectQuery and Import Data Combined; Evolution Begins in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

With the new announcement of Power BI Desktop release of July 2018 at Business Applications Summit, the break-through feature is the ability to create a composite model. Now you can have part of your Power BI model to come directly from a data source, and another part to be imported. Along with this change, you can also choose the storage for each entity. Along with these two great features, you also get the many-to-many relationship type. From my point of view; this is a significant change in Power BI modeling and needs to be discussed thoroughly in this post, so let’s go through it in details. If you want to learn more about Power BI; read Power BI book from Rookie to Rock Star.

Composite Model; What it means?

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.

Let’s take a look at this feature in an example;

Enable Preview Feature

As this is an early preview feature release, you have to enable the Composite model preview feature in the Power BI Desktop options. Your Power BI file would need a restart after this action;

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

I have selected FactInternetSales table from this database.

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;

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

And select DimCustomer from Excel file (this excel file is the Excel version of 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.

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.

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 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.

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

That is pretty exciting.

You can have the massive part of the model, coming from a DirectQuery source, and it doesn’t matter if it is billions of rows, and then small tables to be loaded into Power BI.

Storage Mode: Dual

Considering the Composite mode explained above, you probably ask yourself this question that 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. Also, the new change in Power BI Desktop has another exciting feature for you; Dual storage mode.

If you right click on the table in Power BI Desktop, you can click on Properties.

In the properties section, you will get the option to choose the Storage Mode. There are three options: Import, DirectQuery, and Dual.

Import and DirectQuery in the storage modes above are understandable enough. Import means, 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 get an error explaining this is not possible.

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.

Many-to-Many Relationship in Power BI

Alongside with features mentioned above, you can now create a many-to-many relationship in Power BI. This feature is also a preview feature, so you need to make sure that you have the Composite model option selected as mentioned earlier in this post. This feature is a mandatory option that you need to choose if you are creating a relationship between an Import Data table, and a DirectQuery table.

The many-to-many relationship comes with some limitations and challenges though. Using filter functions may give you strange behavior for this type of relationship. ALL DAX function, for example, will not remove filtering from tables on the two sides of a many-to-many relationship. Also, you cannot use RELATED DAX function in a relationship with a many-to-many type in it.

A many-to-many relationship can make the process of building the Power BI model much easier, you may not need to create dimension tables, and the fact tables. The many-to-many relationship will make things faster and easier, however, it may not be the right way to do it. Using many to many relationships in a real-world solution is something that needs careful thoughts. If you design your model is the right way, you may not need this option. I always recommend everyone to create a proper model and that way you never need a relationship that causes any performance issue or weird behavior.

Summary

In summary the Power BI Desktop July 2018 comes with a significant feature in the modeling, combining DirectQuery tables and Import Data tables in one Power BI model. 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. Also, the many-to-many relationship is the next addition of Power BI modeling features in the new release.

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 now with these changes and features, I believe DirectQuery should get up from the ground. What do you think about it? please let me know in the comments below.

All of these features are only available in Power BI Desktop at the moment. You cannot publish a model using any of these features into Power BI service.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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.

25 thoughts on “Composite Model; DirectQuery and Import Data Combined; Evolution Begins in Power BI

  • 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: