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

One of the first things any Power BI developer needs to know is whether to check that Import or Direct Query box while connecting to data in Power BI Desktop. 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. 

Let’s start by walking through each connection method. 

Connection Types

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 in just a second. 

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. 

Direct Query

Now that you have a basic understanding of the two types of connection, let’s discuss some of the pros and cons 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

  • 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

Import

Now let’s check out some pros and cons 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)

  • 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. 

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

This past year Microsoft came out with the ability to use Composite models in Power BI, or a mix of both Import and Direct Query connections in your data model. Previously, you were only allowed to choose either Import or Direct Query, but now you are able to choose a method for each source independently based upon what method is best for that data source. You can read more about these types of models here

Thanks so much for reading! If you have any comments, suggestions, or feedback make sure to email me at spencer.baucke@tessellationconsulting.com.

Subscribe to Our 'Data Cap' Newsletter!

Want a monthly insights from the Tessellation Team with tips, tricks, and secrets to improve your analytics?

Sign up below and we'll deliver articles, events, and how to's straight to your inbox.
Comments are closed.