When connecting to data in Power BI, you need to choose whether you want to use Import or Direct Query mode for connecting to your data. Using the Import method, Power BI captures a snapshot of your data and caches it in Power BI Desktop. A Direct Query connection will run queries directly to your source at run time.Â
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 how to use both of these 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 dashboard 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 imported into 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 cache 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.Â
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 data source so you are getting the most up to date data. The report refreshes occur every 15 minutes.
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 not having 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
Because you’re querying the data source at run time, you might be competing with other users for bandwidth. You’re also not taking advantage of the compression of the Vertipaq performance engine.
Limited Transformations
You are not able to use all of the normal Power Query transformation features. Particular DAX functions are not available in this method as well. So if your data is poorly structured or needing lots of transformation, sometimes Direct Query is not a viable option.
Less Storage
No cache means not having to store that compressed data on Power BI Service, so you can increase storage capacity on Service.
Advantages of using Power BI Import
Now let’s check out some pros of the Import method.
Performance
When you cache your data you are able to take full advantage of the Vertipaq performance engine. Normally your report performance will be better using this method.
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, and there are no limitations to data modeling.
Multiple Data Sources
Using Import you are able to combine data sources 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 amount of reports you’re maintaining and how big the data sets are that you’re refreshing.
Data Size Limits
 Import caches are limited to 1GB per dataset (can be increased in Premium). While the Vertipaq engine does a great job a 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 switch back to using Direct Query. So make sure you want Import before making the switch, or else you’ll have more work ahead of you!
As a personal note, I generally use Import while building out reports unless there is a specific instance where Direct Query makes more sense.Â
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 what method to use.Â
Beyond the pros and cons listed above, there are a couple of factors that need to be considered when deciding your method. Lots of cloud based services like Snowflake will bill you based upon 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 they’re price is for said services, but you need to take that into consideration.Â
On the flip side, Microsoft is going to charge you for the capacity that 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 out with the requirements for data transformation, data sources, and performance as well the element of cost.
BONUS: Composite Models
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 for connecting 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 via both Direct Query and Import, or Direct Query from multiple sources. 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.
Do you have more questions about Power BI? Talk to our expert consultants and have all your questions answered!
FAQs
It certainly can be, and more often than not, it is. There are a ton of different factors that determine load time including the size of your data set, but generally Import is going to have a better load time than Direct Query.Â
You can use DAX in Direct Query, although some of the functions that you are able to use are limited. Using Import mode ensures that you are able to use all DAX functions.Â
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.
