Once you start working with Power BI Desktop, in order to build your reports and dashboards, you have to specify a dataset. Simply said, a dataset represents a collection of data that will serve as the source for feeding your visuals. Datasets can be based on SQL database tables, Excel or CSV files, Power BI Dataflows, SSAS Cubes, and so on…And, guess what? You can combine all these sources and create a so-called “golden” dataset, that can later be reused and shared between multiple different reports, between multiple different Workspaces!
In most cases, reports and datasets go hand in hand, but you can think of the dataset as the superset of the report – report can’t exist without the dataset, while dataset can in theory “live” without the report.
Power BI modes – Datasets vs Tables
Power BI offers you a choice between the three dataset modes: Import, DirectQuery, and Composite. Now, watch out, here is the first huge point of confusion – you’ve heard that you can set storage mode on a single table level, not only on the whole dataset itself… That’s correct! So, let’s clarify what is the difference between Import mode on a dataset vs table level.
As you can see in the illustration above, while it is true that you can choose between one of the three modes for each of your tables (Import, DirectQuery, or Dual), when you take a look from the dataset perspective, things look similar but still different. To cut the story short, if you’re using Import mode on a table level for all your tables, your Dataset will also be treated as Import mode. The same applies to the DirectQuery scenario. But, once you decide to mix things and set some tables in DirectQuery, while keeping others in-memory within the Power BI VertiPaq database, your dataset is then in Composite mode.
I’ll not go into details about the advantages and limitations of each of the table storage modes, as you can find some key considerations about using DirectQuery mode here.
Power BI Desktop mode
I’ve intentionally spent some time elaborating on different modes, both for datasets and tables. Now, going back to our starting point and question from the title: what is a Power BI Desktop mode and what does it tell you? Well, let’s walk through different scenarios before we get an answer…
The “answer” is in the bottom right corner of your Power BI Desktop, so let’s try to understand each of the possible messages you can see there.
#1 Import mode
The first scenario is the most common – all our tables are loaded into Power BI – in other words, we are using Import mode for every single table in the model.
And, if we take a look into the bottom right corner, there is nothing to be seen:
#2 DirectQuery mode
Let’s now turn on the flip side and set storage mode for all of our tables to DirectQuery.
Whenever you see this blue line on the top of the table, you know that this table has DirectQuery mode turned on. And, now, if I switch back to Report view in Power BI Desktop, I can see an information about the Storage Mode:
#3 Composite mode with Import + DirectQuery
Let’s now spice things up a little bit, and switch the storage mode for two of our tables (DimProduct and DimGeography) to Import. The first thing you should see is the warning – Power BI tells you that it’s an irreversible operation. You CAN switch from DirectQuery to Import, but NOT vice-versa! Keep that in mind…By default, the box next to “Set affected tables to dual” is checked, but I will uncheck this, as I’m sure I want pure Import mode.
Now my Model view looks slightly different. As you may notice, blue headers disappeared from DimProduct and DimGeography tables, as they are now in Import mode.
Let’s check the Storage Mode in Power BI Desktop:
Oh, it’s Mixed now! So, as soon as we changed the underlying tables storage mode, Power BI Desktop adjusted the message in the bottom right corner!
#3.1. Composite mode with Dual + DirectQuery
This one is interesting. Let’s say that I want to keep some tables in DirectQuery mode, but for one table, I plan to use Dual mode.
Dashed blue line on top of my DimDate table means that it’s going to use Dual storage mode. And, if I move to Report view in Power BI Desktop, I can spot that Storage Mode is again defined as Mixed:
#4 Live Connection
Without going deep into the details, Live Connection represents the scenario when Power BI directly connects to a data model that is hosted externally – most often in Azure Analysis Services or SQL Server Analysis Services (on-premises). You can also establish Live Connection to a Power BI Dataset, but that’s the whole different story, and I plan to dedicate a separate blog post to it.
So, what message will we get from Power BI Desktop, once we use Live Connection to an on-prem SSAS Cube (Multidimensional, just to be clear:))?
As you may noticed, Power BI flagged all the tables with the blue color, like they were all in DirectQuery mode. However, you can immediately spot that a lot of properties are missing that are available in regular DirectQuery mode. Anyway, once I switch to Power BI Desktop to check what is the mode of my dataset in this scenario, I will see that it is completely different than with DirectQuery:
This time, I see that I’m live connected to a dataset.
Conclusion
You may think that this is a trivial feature, without a practical use case. But, stop for a moment and imagine that you opened a Power BI Desktop and have a data model containing hundreds of tables, and you quickly need to identify the mode of your dataset, without wasting the time investigating the model in the Model view. Using this feature, you’ll be immediately aware of it!
To wrap-up, let’s draw some conclusions about how to “read” the messages provided by the Power BI Desktop:
- Blank message = Import mode: 1 and only 1 Source group (group contains only tables set to Import storage mode)
- DirectQuery = DirectQuery mode: 1 and only 1 Source group (group contains only tables set to DirectQuery mode)
- Mixed = Composite mode: 2 or more Source groups (every possible combination, for example: Import + DirectQuery, or Dual + DirectQuery)
- Connected = Live Connection: unknown number of Source groups, as the data model is being externally hosted
Thanks for reading!
Last Updated on June 22, 2021 by Nikola
