Power BI Table Storage Modes and Model Types

I still get a lot of questions from various Power BI developers about table storage modes and how table storage modes affect an entire model’s type. Here is a post to summarize all table storage modes / model types.

The following table storage options apply when creating a Power BI model.

Table Storage Mode	Data Stays in	Pro	Con	Use Case	Note
Direct Query	Backend data source	Near real time	May not perform for large amounts of data.	Near real time or large data that does not fit in memory.	Can be converted to Dual or Import.
Import	PBI’s memory (Desktop or Service)	Great performance	Have to refresh the data. Large amounts of data may take a long time to refresh or not fit in memory.	For best performance for data that does not need to be near real time.	Cannot be converted to Dual or DirectQuery.
Dual	A copy in the backend data source AND A copy in the PBI’s memory (Desktop or Service)	PBI can use the copy in memory for better performance when possible. When DirectQuery tables are used in a query, PBI can use the copy of the Dual table in the backend (DirectQuery) for better performance.	NA	Used for dimension tables that connect to fact tables with DirectQuery storage mode or a Hybrid Table which has DirecQuery partitions.	Can be converted to both DirectQuery and Import.
Import Table with Incremental Refresh	PBI’s memory (Desktop or Service) Data will be partitioned based on a predefined policy	After the initial full load of data, only the most recent time periods need to be updated/processed. As a result, the subsequent refreshes go much faster and put a lot less pressure on the backend and system resources.	A pbix file with a table with Incremental refresh cannot be downloaded from PBI Service.	Large fact tables where historical data does not change. A full load may not fit in the 5 hours refresh limit for premium capacities.	Cannot be converted to Dual or DirectQuery.
Hybrid Table (one table with some partitions in Import mode and some partitions in Direct Query)	The Import partitions’ data stays in PBI’s memory and the DirectQuery partitions’ data stays in the backend.	Makes it possible to combine the best performance of Import tables with the near real time data benefit of Direct Query tables.	A pbix file with a table with Incremental refresh with Hybrid option cannot be downloaded from PBI Service.	Historical data in Import, newer data in DirectQuery. Historical data in DirectQuery, newer data in Import.	Cannot be converted to Dual or DirectQuery

Here is a summary of Power BI model types:

Table Storage Modes	Model Type
All tables in Import (w or w/o Incremental refresh)	Import
All tables in Direct Query	Direct Query
Some tables in Import, some in Direct Query or Dual	Mixed*
Some tables in Import with Incremental Refresh and Hybrid option	Mixed
Some tables defined with DirectQuery for PBI/Analysis Services datasets. Some tables from other data sources.	Composite

*Mixed models used to be called Composite models. Since the introduction of DirectQuery for PBI/Analysis Services option, Composite models refer to models where a developer enriches existing organizational models with other data, for example from a small database or a spreadsheet.

Note: When you Live connect to an Azure Analysis Services database or Power BI dataset, you are not creating a model, you are consuming a model and creating reports (visuals). In this scenario, the table storage modes are controlled by the remote model; however, you can combine data from the remote model with other data sources and when you do that, you can make choices for the storage mode of the tables you add. To do this, you must convert from LiveConnect to DirectQuery for Analysis Services / Power BI Dataset. You can read more here: Using DirectQuery for datasets and Azure Analysis Services (preview) – Power BI | Microsoft Docs

Here are some images that show you how each of the table import modes and models look like in Power BI Desktop

DirectQuery Model

Currently DirectQuery tables have a solid blue top line and an icon with two arrows. When all tables are in DirectQuery mode, the data tab does not show in Power BI Desktop and the model type can be seen in the right bottom corner of Power BI Desktop when you are in the report tab.

Import Model

When all tables are in Import, there is no visual indication of the model type in Power BI Desktop (other than the fact that you can see the data tab)

Mixed Model

When some tables are in DirectQuery or Dual and some tables are in Import, the model is called a Mixed model. Currently Dual tables have a dashed blue top line and an icon with two arrows. In a Mixed model, the data tab in Power BI Desktop shows data for the tables that are imported. The model type can be seen at the bottom right corner of the report tab in Power BI Desktop.

Live Connect to a Power BI dataset

This in itself is not modeling but can be changed into modeling if you combine the data with some other data.

Even though you connect to a remote dataset, you can see the model. The icons on the tables look different than the icons in previous examples. The globe with two arrows indicates LiveConnect.

At the bottom right corner of Power BI Desktop in the report tab, you can see the message that shows the report is currently live connected to a Power BI dataset; meaning a remote model is being used and so far no local modeling has been done.

In order to combine this model with other data, you can click on Make Changes to this model which will prompt you to switch to DriectQuery storage mode. Note this is DirectQuery for Power BI datasets not the same as DirectQuery.

Once you do that, the storage mode, shows as DirectQuery. Note how this time, the message does not show you the option of click here to change.

The table icons look exactly like how tables in DirectQuery looked like in the first example, the only difference is that the storage mode for these tables cannot be changed. The option does not exist under Advanced properties.

Composite Model (Mixed Model)

The following image shows previous example with the addition of an import table (DimCustomer).

The DirectQuery and import tables have the same icon as in previous examples. The relationship arrow between the table from the PBI Dataset (vFactIntersetSalesBig) and the imported table (DimCustomer) looks visually different than other relationship arrows.

Interestingly, the storage mode shows as Mixed now.

The “Composite model” phrase itself is not shown in Power BI Desktop.