Power BI Import vs Direct Query Feature Comparison & Limitations

In this article, we will be comparing Power BI Import vs Direct Query methods for creating data models. You can either use PowerBI desktop or Excel to create data models using either of the two ways.

However, there are essential key considerations before choosing one over the other. Read along to know more on this topic.

Power BI Import vs Direct Query

For ease of understanding, refer to the following table below to know exactly which method to choose under different scenarios.

	Import (cached mode)	Direct query or Live connection
Refresh frequency	Scheduled Async Jobs – hourly or daily	(near) Real-time
Performance	No noticeable delay since the data model is already cached (Point in time data)	Depends on how fast the network connectivity and data source is as queries are executed in real-time. Only metadata and schema structure is stored on the Data model
Data storage in Power BI	Since it is a cached mode, data is stored in the PowerBI Service (cloud)	Data will not be stored in the Power BI cloud service. Data resides on-premises
Dataset size (Max)	Hard limit of 1 GB (Columnar data compression) per model	The on-premises database storage is the limit; no Power BI service limitation
Security	Can create row-level security on the PBI dataset (import only)	Re-use on-prem row-level security for Analysis Services Tabular using DAX expressions
Target Audience	Small and Medium Datasets	Large Datasets (Greater than 1 GB)
Datasource Support	Supports all Data Sources and Transformations Supports multiple data sources	Supports only 1 data source per model Supports only Single directional filtering Bi-directional filtering Support is not available
Max Size	1 GB after Columnar Level Compression	1 million rows max returned per dataset

Power BI Import Method:

When you select “Import,” the data is imported into the XLSX (Excel) or PBIX file (PBI Desktop) and is stored in an SSAS Tabular model. Both the Import method in Excel and Power BI desktop uses the ‘Vertipaq‘ engine, also known as ‘xVelocity.’

When users create and interact with a data visualization, Power BI Desktop connects to the imported data embedded into the file. Since the data is ‘Point in Time‘ you will have to set up data refresh on the PowerBI cloud service.

Each time when the data is refreshed, it imports the complete data set again, to see any changes that could have occurred to the underlying dataset since the initial import or after the most recent refresh.

Advantages of Import Method:

Offers excellent performance, since the data resides inside the data model.
Avoids throttling due to network latency or data source bottlenecks

Disadvantages:

Data is not real-time.
Updated data is only reflected after scheduled data refreshes are completed.
1 GB size limitation (per dataset)
8 refreshes per day for Pro & 48 times for Premium accounts

Power BI Direct Query Method:

In this method, at the time of creating the data model, only the schema, tables and columns are defined. The data is not imported or embedded into the xlsx or pbix file. So, for every visual interaction Data is refreshed.

The user selected tables and columns in the data model appear in the Fields list. As you create or interact with data visualization, Power BI Desktop will query the embedded data source (odc file), which means you’re always viewing the current version of the data. The time taken to refresh the visualization is mostly dependent on the performance of the underlying network performance, data source, etc.

So, the tradeoff to real-time data is performance, and if you refer to the table above, the use cases are clear.

Direct Query Mode Advantages:

Unlimited dataset size, since it is not stored locally.
Real-time data is available with each visual interactions
Only metadata, i.e., Schema, tables, and columns are visible (Type of XMLA)
All transformations are supported
No 1 GB limitation (per dataset)

Limitations

It does not support the PowerBI Q&A feature.
Bi-Directional filtering is not supported
Multiple data sources per dataset are not supported. (See Composite Models)
Time Intelligence is not supported
1 Million-rows limitation for each query
Quick Insights not supported on the PowerBI service.
Complete queries using M expressions sometimes does not work.

From the security perspective, the embedded security credentials are always used to query the backend data source.

Conclusion

I hope that this article helped to compare the Power BI Import vs Direct Query features and key considerations. Do let us know your thoughts and suggestions by leaving us a comment below. Thanks for visiting.

Also Read:

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.