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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

13 Comments

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *