Import vs Direct Query: Here's What You Need to Know

This article was originally written by Spencer Baucke and updated by Dave Ovitt.

When connecting to data in Power BI, you need to choose whether you want to use Import or Direct Query mode to connect to your data. Using the Import method, Power BI captures and caches a snapshot of your data in Power BI. A Direct Query connection will run queries directly to your source whenever a page is navigated to, or an update is made to displayed visuals.

There are several things that you need to consider before deciding on what method is best for you. This decision can be report-specific, but your organization should also have a strategy around using both methods. Let’s start by walking through each connection method.

What is Power BI Direct Query?

When using the Direct Query method of connection, your semantic model will be directly querying the data source at run time. Every filter and interaction with the report will kick off further queries. No data is stored in Power BI, so you are always querying the data that is present in the data source itself. We’ll cover how this could be beneficial later in the article.

What is Power BI Import?

The Import method of connection means that Power BI will store an image of the data that you’re connected to, creating a point-in-time snapshot of your data. All of the interactions and filters applied to your data will be done to this compressed (courtesy of the Vertipaq storage engine) cache source instead of the actual data source itself.

Content Overview

Pros and Cons of Direct Query
Pros and Cons of Import
Organizational Strategies
Composite Models

Advantages of using Power BI Direct Query

Now that you have a basic understanding of the two types of connection, let’s discuss some of the pros of the Direct Query method.

Data Timing

Data is queried from the source, so you get the most up-to-date data. The report refreshes occur every 15 minutes or whenever a change to the filters or interactions requires new data to be pulled in.

Smaller File Footprint

Since you are not caching your data when using Direct Query, your Power BI Desktop files are much smaller and easier to work with (faster saving, publishing, etc.)

Less Storage

No cache means you don’t have to store that compressed data on Power BI Service, so you don’t need as much storage capacity on Service.

Disadvantages of using Power BI Direct Query

Slower Performance

You might be competing with other users for bandwidth because you’re querying the data source at run time. You’re also not taking advantage of the compression of the Vertipaq performance engine. Your queries will need to be transmitted to the source, and depending on the source, this can add additional time to display results.

Limited Transformations

You are not able to use all of the normal Power Query transformation features. Particular DAX functions are not available for this method either. So, if your data is poorly structured or needs lots of transformation, sometimes Direct Query is not viable.

Consumption Considerations

If your data source is on a consumption-based model, or you are using a Fabric SKU based on consumption for Power BI Premium, you may consume more depending on the frequency of report usage.

Advantages of using Power BI Import

Now let’s check out some pros of the Import method.

Performance

When you cache your data, you can take full advantage of the Vertipaq performance engine. Normally, your report performance would be better using this method. Because the data is also local to Power BI, there is less time needed for reports to query the data needed in visuals.

Greater Functionality

Unlike in Direct Query, you are able to use all M and DAX functions (notably all-time intelligence functions), format fields however you desire, add calculated columns, and there are no limitations to data modeling.

Multiple Data Sources

Using Import, you are able to combine data from various data sources (data flows, databases, CSV)

Disadvantages of using Power BI Import

Delayed Data

You can schedule up to 8 refreshes a day (Premium SKUs allow more), but you also need to consider the number of reports you’re maintaining and how big the data sets that you’re refreshing are.

Data Size Limits

Import caches are limited to 1GB per Semantic Model (increased with Power BI Premium). While the Vertipaq engine does a great job of compression, you will still need to consider this when choosing your connection method

Can't Switch Back!

Crazy enough, once you’ve selected Import, you cannot easily switch back to using Direct Query. So make sure you want to import before making the switch, or you’ll have more work ahead of you!

If you do switch to Direct Query and need to switch back, you have to disable the source in Power Query, Save and Apply the Data, then go back and enable the source, and select Import as the storage option, but any relationships will need to be re-established, so consider this prior to making any changes to storage mode.

Most business needs I have observed typically call for semantic models to use Import mode when possible. We’ll discuss how and why to choose next.

How to Choose a Method Based on Organizational Strategy

Now that we understand some of the differentiators between Direct Query and Import let’s talk about how your organization can game plan and what method to use.

Beyond the pros and cons listed above, a couple of factors need to be considered when deciding your method. Lots of cloud-based services like Snowflake will bill you based on usage, so if you’re using Direct Query with lots of users, you’re going to be using quite a bit of their services. Add in multiple reports and teams, and then you might start running into a big price tag from the service provider. All of this depends on what service you’re querying and what their price is for said services, but you need to consider that.

On the flip side, Microsoft will charge you for the capacity you have on Power BI Service. If you are going to be using Import for every report and every team, then you’re bound to be storing more data on your Service instance. Depending on how much capacity you need, this need for storage could drive your cost up, but you also aren’t going to be charged for usage.

Both of the previous points will need to be balanced with the requirements for data transformation, data sources, performance, and the element of cost.

Composite Models

Some semantic models will benefit from storing dimension tables as Import mode and fact tables using Direct Query, allowing for a composite model to be built. This allows the fact table not to have to be refreshed daily or when an incremental refresh isn’t appropriate.

In the December 2020 release of Power BI Desktop, Microsoft released the ability to use Composite models in Power BI. Previously, if you used Direct Query to connect to data, you were not allowed to bring in data from any other sources. Now, a lot of the restrictions around data connection types have been lifted. With composite models, you can bring in data from multiple sources via both Direct Query and Import or Direct Query. You can read more about these types of models here.

Choosing how to bring data into your Power BI report can be an important decision for the success of your project. As you can tell from this blog post, there are a variety of factors to choose from when deciding what method to use, but hopefully, you now have a much clearer idea of what route you want to take.

Have more questions about Power BI?

Talk to phData’s expert consultants and have all your questions answered!

FAQs

Is Import faster than direct query?

It certainly can be, and more often than not, it is. Many factors determine load time, including the size of your data set, but generally, Import is going to have a better load time than Direct Query.

Can you use DAX in a direct query?

You can use DAX in Direct Query, although some of the functions you can use are limited. Using Import mode ensures that you are able to use all DAX functions, including time intelligence measures and calculated columns.

What are some Power BI import vs direct query limitations?

A limitation to Import is that your data is only as fresh as your latest refresh. Sometimes, refreshes can fail and cause delays in getting updated data. On the other hand, while Direct Query will provide you with the most current data, this method can also mean slower performance and more limited DAX capabilities.