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
- 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)
- 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.
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.