Import vs Direct Query: Choosing the Right Connection Strategy

Power BI Import vs. DirectQuery: Choosing the Right Connection Strategy 

When venturing into Power BI Desktop and connecting to data, one of the initial decisions every Power BI developer encounters is whether to opt for the Import or Direct Query approach. Before settling on the ideal strategy, several factors warrant consideration. While this choice may be made on a per-report basis, it’s essential for your organization to establish a clear strategy for utilizing both these connection methods. Let’s explore each connection method individually and discern the better option in the Power BI Import vs. Direct Query dilemma. 

Figure 1: Connection settings (Import or DirectQuery) on PowerBI 

 Understanding Direct Query and Import Mode in Power BI 

 Direct Query: When you employ Direct Query, your dashboard queries the data source in real-time during runtime. Every filter application and interaction with the report triggers fresh queries directly to the data source, typically a SQL database, retrieving data directly into Power BI. 

Figure 2: A block diagram of Direct Query mode 

Import: In Import mode, Power BI caches the linked data, creating a snapshot at a specific point in time. All data interactions and filters are applied to this cached source rather than the original data source. 

Figure 3: A block diagram of Import mode 

Direct Query or Import Mode in terms of performance speed  

In scenarios where data is less than 1 GB and doesn’t undergo frequent updates, Import mode prevails as the faster choice. Since all data originates from the Power BI Desktop Cache, it outpaces Direct Query mode when dealing with smaller datasets under 1 GB in size. 

 Import Mode Overview 

• Broad Data Source Support: Import mode accommodates various data sources, including Excel, CSV, social media feeds, web URLs, CRM data, and more. 
• Enhanced Functionality: Users can harness all M and DAX functions, format fields extensively, and have unrestricted data modeling capabilities. 

Direct Query Overview 

•  Data Scheduling: Direct Query fetches data directly from the source, ensuring real-time information. Reports refresh every 15 minutes. 

•  Compact File Sizes: Power BI Desktop files remain compact and manageable as data isn’t cached, leading to quicker saving and publishing processes. 
•  Reduced Storage Demand: With no need to store compressed data on Power BI Service, storage requirements diminish. 

  Changing Storage Modes in Power BI 

 To change storage modes, you can follow these steps: 

•  If you are in “Direct Query” Storage Mode, you will usually see “Direct Query (click to change)” in the bottom right corner of the Desktop Application 
•  Alternatively, you might be in mixed mode, indicated as “Storage Mode: Mixed (click to change).” 
•  You can also check the Storage Mode of a table by right-clicking on it and selecting Properties. 
•  If you decide to switch all tables to Import mode, you’ll be presented with this option when you visit the Edit Query page. A popup will appear, indicating that the change is irreversible. Similarly, when switching to Import, a warning emphasizes the permanence of the action. 
• You can also switch to a different storage mode for individual tables by right-clicking on them and selecting the desired storage mode. 

Limitations of Import vs. Direct Query 

Direct Query 

•   Timeliness of Data: Data refreshes every 15 minutes. 
•   File Size Reduction: Compact Power BI Desktop files due to the absence of data caching. 

•  Storage Requirements: Reduced storage demand as no compressed data is stored on Power BI Service. 

 Import 

•  Delayed Data: Supports up to 8 daily refreshes (more with Premium SKUs), considering the number of reports and dataset sizes. 
•  Data Size Restrictions: Import cache size limited to 1 GB (expandable in Premium). 
•  Inability to Revert: Once you opt for Import, reverting to Direct Query is not possible. 

Distinguishing Import Mode & Direct Query  

Table 1: Difference between Import Mode and Direct Query 

Aspect	Import Mode	Direct Query (Live Connection)
Refresh frequency	Hourly or Daily Scheduled Jobs	Real Time
Performance	Minimal Latency due to Caching	Dependent on Network and Source Performance
Data Storage	Cloud-Based (Power BI Service)	On-Premises (Source Data Remains)
Dataset Size (Max)	Up to 1 GB (Expandable in Premium)	No Limit for On-Premises Database
Security	Row-Level Security (Import Only)	Utilizes DAX Expressions for On-Prem Row-Level Security
Target Audience	Small and Medium Datasets	Massive Datasets (>1 GB)
Datasource Support	All Data Sources Supported	Limited to One Data Source
Bidirectional Filtering	Supported	Supported in Both Directions

When to Use Direct Query? 

Below are the scenarios where it’s best to implement direct query in your report development: 

• When “real-time” or “near real-time” data is essential. 
• In cases where data exceeds the 1 GB limit for .pbix file size. 
• When aggregations and calculations should be performed at the source, not within Power BI. 

 Why (Not) to Use Direct Query? 

Below are the cases where the use of direct query will not be a good fit. in your data connection: 

• Performance heavily relies on the data source’s optimization for analytic workloads. 
• Concurrent user engagement can impact performance. 

• Factors beyond your control, such as network delays and source server performance, play a role. 

How to Connect Using Direct Query? 

•  When using Get Data to connect to a Direct Query-supported data source, you can choose your connection method. In a case where the user needs to connect directly to a SQL server, selecting “Get Data > SQL Server” from the Power BI Desktop ribbon displays Import and Direct Query options. Select the Direct Query option as shown in the figure below:  

Figure 4: A connection dialog box of SQL Server database on Power BI   

  Which Method is Best & Fastest – Direct vs. Import? 

•   Import Data: Offers access to all Power BI features, including Power Query transformations, DAX calculations, and comprehensive visualizations. 
•    Direct Query: Excels in scalability, accommodating vast datasets and real-time or near-real-time BI solutions. 

Conclusion 

 In the Power BI Import vs. Direct Query dilemma, both connection options, Import Mode and DirectQuery, have distinct features and limitations. Import Mode shines when dealing with smaller datasets under 1 GB that don’t change frequently. It allows scheduled refreshes for up-to-date data while providing extensive data modeling capabilities. On the other hand, Direct Query excels in real-time scenarios, massive datasets, and scalability. The choice ultimately hinges on your organization’s specific needs and data characteristics.