When connecting to data in Power BI Desktop, one of the first things each Power BI developer needs to know is whether to click the Import or Direct Query box. Before choosing on which strategy is ideal for you, you need to think about a few things. This decision can be made based on the report, but your company should have a strategy in place for how to employ both of these ways. Let’s go over each connection method one by one and find out what is better when it comes to Power BI Import vs Direct Query.
 

What is Direct Query and Import Mode in Power BI?

Option to Select Import & Direct Query within Power BI
 
Direct Query – When you connect a data source using the Direct Query technique, your dashboard will query the data source immediately during runtime. Each filter and interaction with the report will result in a series of new queries. Since no data is imported into Power BI, the user can always query the data that already exists in the data source.

Each & every single request goes straight to the data source (which is more often SQL database) and pulls the data from there into Power BI.

Import — Using the Import mode of connection, Power BI will cache the data you’re linked to, providing a snapshot of your data at a specific point in time. All of your data’s interactions and filters will be applied to this compressed cache source rather than the original data source.
 

Is Direct Query faster than Import Mode in Power BI?

Import mode is the best option if your data is less than 1 GB and isn’t constantly updating. Because all data comes from the Power BI Desktop Cache. So in such scenario, an import mode is faster than Direct Query mode when the data file size is below 1 GB. 
 
Import Mode Overview

• Import Mode supports numerous data sources like Excel, CSV, Social media,  Web URL, CRM data & more as shown below

Img source: pbiusergroup.com

• Improved functionality: Users can access all M and DAX functions, format fields as much as they want, and data modelling is completely unrestricted.

 
Direct Mode Overview
Direct query supports a number of data sources like SQL server, Azure data  bricks, Amazon Redshift & more as shown in below image.

Img source: pbiusergroup.com

• Data Scheduling: Data is queried from the data source, ensuring that you get the most up-to-date information. Every 15 minutes, the report is refreshed.
• Files with a smaller footprint: The Power BI Desktop files are much smaller and easier to work with because you are not caching your data when using Direct Query (faster saving, publishing etc.)
• Storage space is limited
• You don’t have to store that compressed data on Power BI Service if you don’t have a cache, thus you don’t need as much storage capacity on Service.

 

Power BI Changing Storage Modes (i.e. Power BI change from Direct Query to Import)

Here is how you can change storage modes: Power BI change from direct query to import

• If you are in “Direct Query “Storage Mode: DirectQuery (click to change)” is normally displayed in the bottom right corner of the Desktop Application.”
• You can also be in mixed mode, which will display the notification “Storage Mode: Mixed (click to change)”.
• Another approach to figure out what Storage Mode a table is in is to pick it, right-click, and select Properties.
• If you go to the Edit Query page and click “Switch all tables to import mode,” you’ll be given the choice to make the modification. A pop-up box will appear, notifying you that this is irreversible. If you switch to Import, the window warns you that there may be consequences, and that “setting storage is an irreversible procedure.” It will not be possible to revert to DirectQuery.”
• Simply choose OK at the bottom of the box if you’re moving ALL tables to import mode.

Another option to switch to a different storage mode is to go to the table, right-click on properties, and afterwards select the storage mode you want for that table.
 

Limitations of Import vs Direct Query 

 

DirectQuery

• Timeliness of Data

Data is queried directly from the data source, ensuring that you have the most recent information. Every 15 minutes, the report is refreshed. Each 15 minutes, the report is refreshed.

• File Size is Reduced

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

• Storage requirements are reduced

You won’t have to store the compressed data on Power BI Service if you don’t have a cache, so you won’t need as much storage on Service.

Import

• Data that has been delayed

You can schedule up to 8 refreshes each day (more with Premium SKUs), but keep in mind the number of reports you’re updating, and the size of the data sets you’re renewing.

• Data Size Restrictions

The size of each dataset’s import cache is limited to 1GB (can be increased in Premium). While the Vertipaq engine does an excellent job at compressing, you should keep this in mind while selecting your connection method.

• Unable to switch back

Surprisingly, once you’ve chosen Import, you can’t go back to Direct Query. So, before making the switch, make sure you want Import, or you’ll be in for a lot more work.
 

Difference between Import Mode & Direct Query

	Import Mode	Direct Query or Live Connection
Refresh frequency	Hourly or daily programmed async jobs	Real Time
Performance	Because the data model is already cached, there is no visible latency.	Because queries are processed in real-time, it is dependent on how quick the network link and data source are. The Data model just stores metadata and schema structure.
Data storage in Power BI	Data is stored in the Power BI Service because it is a cached mode (cloud)	The Power BI cloud service will not store any data. The data is kept on-site.
Dataset size (Max)	Per model, there is a hard restriction of 1 GB (columnar data compression).	There is no limit to the amount of data that can be stored in an on-premises database.
Security	On the PBI dataset, users can build row-level security (import only)	Using DAX expressions, reuse on-prem row-level security for Analysis Services Tabular.
Target Audience	Datasets of Small and Medium Size	Massive Datasets (Greater than 1 GB) Per model
Datasource Support	All Data Sources and Transformations are supported. Multiple data sources are supported.	Power BI Direct Query data sources: Only one data source is supported. Only supports Filtering in a single direction Filtering in both directions is not supported.
Max Size	1 GB after compression at the columnar level	Per dataset, a maximum of one million rows can be retrieved.

 

How to Change Import Mode to Direct Query Mode in Power BI

Making the switch to DirectQuery from Import mode:

1. Click Edit Queries to open the Power Query Editor.
2. Double-check that the top query is selected.
3. Click on the bottom query while holding down the Shift key; this will select all questions.
4. Press Ctrl + C on your keyboard.
5. Open a text editor of your choice (like Notepad).
6. Hold down the Ctrl key and press the V key.

 

How to Change Direct Query Mode to Import Mode in Power BI

1. Make a csv file first.
2. Select csv file from the Get Data menu.
3. Select Load from the drop-down menu.
4. A notification stating that all queries must be switched to import mode would be displayed.
5. Select Switch.
6. Update the model.

 

Power Apps Visual for Power BI – CDS Direct Query Mode

To begin with, you should keep in mind the perquisites:

• Enable the Tabular Data Stream (TDS) endpoint (on by default).
• Power BI Desktop.

 

How to Setup CDS for Direct Query  

Follow the below step to understand the setup process –

1. Sign into Power Apps, and then select the appropriate environment from the top-right corner.
2. On the left navigation pane expand Data, select Tables, and then select Analyze in Power BI on the command bar.
3. The pbids file for your environment is downloaded to your browser’s default download folder.
4. Open the .pbids file to access it in Power BI Desktop.
5. The pbids file is loaded in Power BI Desktop. In the dialog box, select Organizational account, select Sign in, and then in the browser window that appears select or enter your credentials.
6. In the dialog box in Power BI Desktop, select Connect. The environment appears in the Power BI Desktop Navigator window. Expand it to view the tables available to analyze. Select a table to preview its data.
7. After you’re finished selecting the tables you want to analyze, select Load to build a report.

 

Advantages of Direct Query Mode in Power BI

Using DirectQuery has a number of advantages:

• DirectQuery allows you to create visualizations from very huge datasets, which would otherwise be impossible to do with pre-aggregation.
• Changes in the underlying data may necessitate a data refresh. The necessity to display current data in some reports can necessitate huge data transfers, making reimporting data impossible. DirectQuery reports, on the other hand, always use current data.
• DirectQuery is exempt from the 1-GB dataset limit.

 

WHEN to use DirectQuery?

“real-time” or “near real-time

If you require “real-time” or “near real-time” data, DirectQuery mode should be considered. As Import mode saves a snapshot of your data, it must be refreshed on a regular basis to acquire the most up-to-date information. Import mode is nearly impossible to use if you need data with a maximum 1-minute latency.

When the data size is so huge that you can’t fit it into a maximum .pbix file size, then you should use use DirectQuery. Data stays in the source with DirectQuery, and your aggregations/calculations are done well before the modified results are returned to your report.

WHY (not) to use DirectQuery?

Performance: When utilizing DirectQuery, the most important thing to remember is that the overall user experience is nearly entirely dependent on the performance of the actual data source. If the source database is not optimized for analytic workload, the performance of reports will suffer.

Too many people engaged with a report: In addition, the number of people who engage with the report at the same time will have an effect.

And, even if you think there’s a good chance you can improve those two by using different strategies, take into account that there are certain things you can’t really control, such as:

• Network delay
• Performance of the source server

 

How to connect using DirectQuery?

The connection dialogue box allows you choose how to connect to a data source supported by DirectQuery when you use Get data. Select Get data > SQL Server from the Home ribbon in Power BI Desktop. The Data Connectivity mode in the SQL Server Database dialogue box displays the Import and DirectQuery options:
 

Power BI Desktop as an Analytical Interface

1. Launch a new Power BI Desktop App.
2. Select SQL Server from the Power BI Desktop ribbon’s little triangle at the bottom of the Get Data button. A dialogue box for SQL Server Database will display.
3. In the Server text box, type the name of the server. This would be the name of your SQL Server or one of your organization’s SQL Server resources.
4. If you have one, enter the name of the database.
5. Make sure the Connect Live button is turned on.
6. Click the ‘OK’ button. After that, the Navigator dialogue box will display.
7. Click the ‘OK’ button. You’ll see the Power BI Desktop Report view.

 

Which method is best & fastest – Direct vs Import?

Import Data gives you access to all of Power BI’s features. Full Power Query transformations, DAX measurements, and visualizations are all available. DirectQuery will present you with a number of Powers Query alternatives.

The size limit for the Import Data method is 1GB per model. As a result, without Power BI Premium, this technique is not as Scale-able.

You get better scalability with DirectQuery and Live Connection. A vast amount of data can be stored in data sources.

As a result, Live Connection and DirectQuery are the winners when it comes to scalability.

 
Conclusion:
To summarize, when it comes to Power BI import vs direct query, both Data Connectivity options, Import Mode and DirectQuery have their own set of features as well as some limits. When the data size is less than 1 GB and the data is not constantly changing, Import Data works well. To acquire the most up-to-date data, you can import data with a scheduled refresh. You may use the high-performance query engine to its full potential with the Import connection.

DirectQuery Connectivity mode allows you to connect to data directly. When the data changes frequently and the data volume is big, DirectQuery mode is utilized to construct real-time or near real-time BI solutions. We’ve tried to include as much information as possible to assist you in making the best choice for your organization’s connection needs because in the end It all boils down to whatever approach best suits your organizational objectives.