All Storage Modes in Power BI

The most common way to use data in Power BI is by bringing it into a Power BI semantic model. Importing the data means the information is stored in the Power BI file and is shared when you publish Power BI reports. This makes it convenient for you to interact directly with your data. However, keep in mind that this method might not be suitable for every organization.

Table of Contents

Sometimes, there are rules about keeping your data secure, and it’s not possible to bring in a copy directly. Or your models may be really big and would take a long time to load into Power BI, causing a slowdown. To fix these issues, Power BI has something called DirectQuery storage mode. This means you can ask questions directly to the data source without bringing a copy into Power BI. DirectQuery is handy because it makes sure you’re always looking at the latest version of the data.

Types of Storage Modes

The four different types of storage modes you can choose from:

Import
DirectQuery
Live Connection
Dual (Composite)

You can access storage modes by switching to the Model view, selecting a data table, and in the resulting Properties pane, selecting which mode that you want to use from the Storage mode drop-down list, as shown in the following visual.

Let’s take a closer look at the different types of Storage Modes.

Import Mode

In Import mode, you can make a copy of your data in Power BI, so it’s like having your own version of the information. This way, you can use all the cool features in Power BI, like asking questions (Q&A) and getting quick insights. You can set up times to refresh your data regularly or do it whenever you want. Import mode is the usual way people start making new Power BI reports.

All the sources in ‘Get Data‘ option are comes under import mode by default.

DirectQuery Mode

With DirectQuery, you don’t store copies of your data locally; it’s like having a direct link to your information. You can ask for specific tables using Power BI, and the data you need will be fetched from the main source. It’s like having a live connection to your data. This way, you always see the most recent information, and it meets all security rules. If you have big models with a lot of data, instead of making Power BI slow by loading all of it, you can use DirectQuery to stay connected to the source and avoid delays in data updates.

DirectQuery use Cases

Connecting with DirectQuery can be useful in the following scenarios. In several of these cases, leaving the data in its original source location is necessary or beneficial.

DirectQuery in Power BI offers the greatest benefits in the following scenarios:

The data changes frequently, and you need near real-time reporting.
You need to handle large data without having to pre-aggregate.
The underlying source defines and applies security rules.
Data sovereignty restrictions apply.
The source is a multidimensional source containing measures, such as SAP BW.

Live Connection

It is specific for connect to ‘Analysis Services‘ or ‘Power BI Dataset‘. Here what we actually have is just a connection string between Power BI and these analysis services, and everything else is kept on the model itself. We don’t make copies and do not bring anything to Power BI desktop. And by default we can’t make changes to that model, because that model is kept separate.

For example, when you use import to connect to SQL Server Analysis Services, you define a query against the external SQL Server Analysis Services source, and import the data. If you connect live, you don’t define a query, and the entire external model shows in the field list.

Dual (Composite Mode)

Composite models let you mix and match all connection types. In Dual mode, you can decide which data to bring in directly and which data to query. When you add a table to your report, it’s a mix of both Import and DirectQuery. Dual mode lets Power BI pick the best way to get the data efficiently.