Import Mode Vs DirectQuery

• August 12, 2019
• Sameer Mohammed
• no comments

Power BI is a powerful business intelligence tool for analyzing your data and presenting it visually.

Power BI is best known for the impressive data visualizations and dashboard capabilities it has. However, before you can begin building reports, you first need to connect to the necessary data sources. When establishing a connection to a data source, you may be presented with one of two different options on how your data should be treated: Import or DirectQuery. This blog will help you determine when to use Import Mode or DirectQueryMode for a project.

Understanding Import Mode Vs DirectQuery Mode
Power BI offers two types of connections to build a report.
• Import Mode
• DirectQuery Mode

To decide which connection to use do a preliminary check by asking yourself the following questions, before loading the data:

1. Is your requirement to load the data daily or weekly?
2. If your requirement to load data is daily, how frequently should it be refreshed?
3. What is the size of your dataset?
4. Do you always need fresh data?

The following image pictorially describes the best option to be used as per the answers you get to the preliminary check questions.

Fig 1: Preliminary Check Questions For Decision Making

Getting to know the thumb rules for decision making
This section enumerates a few thumb rules for easy decision making to choose between Import Mode Vs DirectQuery.
• Import Mode is most suitable and recommended if the requirement to load data is weekly or daily.
• If however, the requirement to load data is many times in a day – like daily with a 3-hour frequency – then Import Mode rather than DirectQuery Mode is recommended.
• If the objective is to always have fresh data, then DirectQuery Mode is required.
• If your dataset is less than 1 GB, then the Import mode is suitable. For larger datasets, it is better to connect with DirectQuery mode.

Understanding how DirectQuery works
This section lists the initial steps while using DirectQuery and also highlights some key facts.
Following are the steps while using DirectQuery:

• Whenever the user interacts with any Power BI visualization, a query is sent to the actual data source.
• The performance can be checked using SQL Profiler if there is a connected SQL Server database.
• If the page has 10 visualizations, then a database query is called for whenever a visual is clicked.
• An independent query to the original data source is created for each and every visualization.

Following are some key facts about DirectQuery
• DirectQuery performs much slower than Import mode.
• DirectQuery Mode only supports Single data source unless a composite model is being used.
• DirectQuery Mode has some limitations related to Power Query. It does not support all transformations within the Power Query. It completely depends on the data source that is being used.
• There are many limitations with regards to DAX in DirectQuery. These are listed below:
• Calculated Tables are not supported with DirectQuery Mode.
• Built-in Date Hierarchy is not supported.
• Calculated columns and measures can be written, but all DAX functions cannot be used.
• The limited number of DAX functions are supported.
• It restricts us from using OOTB Time Intelligence functions.
• Any DAX expression we apply gets executed as T-SQL script on the data source.
• There is no need to refresh data with DirectQuery. It refreshes data every 15 minutes.

Understanding how Import Mode works
This section explains a few key facts about the ImportMode. These facts will help you for better decision making.
Following are the key facts about Import Mode:
• Import mode loads all the data in Power BI Desktop.
• The performance is much faster than a DirectQuery Mode.
• It supports multiple data source.
• There are no limitations of the DAX and ETL operations.
• With Import mode, 8 schedule refreshes in a day can be set with Power BI Pro license.

Helping you choose the best option
Now that you have learned about the two different ways to connect to your data, you’re left to wonder which option is best for you. It’s fair to say that the choice you make will really depend on the requirements of each individual project you. However here are few recommendations for quick decision-making.

Use of DirectQuery Mode is recommended when:
• Reflection of the data is needed frequently, near to “real-time data” processing.
• Size of the dataset is very large (petabytes).

Use of ImportMode is recommended when:
• Reflection of the data is needed once in a day or week or month or frequent interval of hours.
• Size of the dataset is less than 1 GB.
• When there is a need to connect different multiple data sources.

Conclusion
In this blog, we learned about the benefits and disadvantages of each query mode and understood methods for weighing the right options for decision making.
If you’d like to learn more about data transformations and Power BI, subscribe to our blog now or write to us at ReachIT AQL <reachit@aqltech.com>. To know more about Office 365 and Microsoft Power BI log on to our website www.aqltech.com