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.
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.
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.
Now let’s check out some pros and cons of the Import method.
As a personal note, I generally use Import while building out reports unless there is a specific instance where Direct Query makes more sense.
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 email@example.com.