HomeBlogsPower BIPower BI Tips: Import vs Direct Query

Power BI Tips: Import vs Direct Query

  • October 16, 2018
  • Posted by: Siva Mani
  • Category: Power BI
7 Comments

Microsoft Power BI is a self-service Business Intelligence tool that lets you connect to multiple different data sources – Power BI offers 92 different data connectors to connect the data sources. With these connectors, you can connect to different data sources. Power BI offers two different Data Connectivity modes to connect the data sources – Import and DirectQuery.

How to choose the right Data Connectivity mode?

Both Data Connectivity modes are having their own unique capabilities and some limitations as well.

Import Connection – Import Data Connectivity mode lets you import data into Power BI cache. Highly recommended to use Import connection when the data size is less than 1 GB and the data is not continually changing. You can import data with schedule refresh to get the latest data. With Import connection, you can take full advantages of the high-performance query engine.

DirectQuery – DirectQuery Connectivity mode lets you connect directly to data. DirectQuery mode used to build Real-time or near real-time BI solutions when the data is changing frequently, and Data volume is very large. No data will be imported into Power BI. Instead Power BI will send queries to the data source upon building visual/interacting with visuals. Each query is restricted to return less than or equal to 1 Million rows.

Here is the comparison between Import and DirectQuery,

Capability Import DirectQuery
Size Up to 1 GB per dataset No limitation
Data Source Import data from Multiple sources Data must come from a single Source
Performance High-performance query engine Depends on the data source response for each query
Data Change in the underlying data Not Reflected. Required to do a Manual refresh in Power BI Desktop and republish the report or Schedule Refresh Power BI caches the data for better performance. So, it is necessary to Refresh to ensure the latest data
Schedule Refresh Maximum 8 schedules per day Schedule often as every 15 mins
Power BI Gateway Only required to get latest data from On-premise data sources Must require to get data from On-premise data sources
Data Transformations Supports all transformations Supports many data transformations with some limitations
Data Modelling No limitation Some limitations such as auto-detect relationships between tables and relationships are limited to a single direction.
Built-in Date Hierarchy Available Not available
DAX expressions Supports all DAX functions Restricted to use complex DAX functions such as Time Intelligence functions. However, if there is a Date table available in the underlying source then it supports
Clustering Available Not available
Calculated Tables Available Not supported
Quick Insights Available Not available
Q&A Available Not available
Change Data Connectivity mode Not possible to change Import to DirectQuery Possible to change DirectQuery to Import

Hope this blog helps you to understand the Data Connectivity modes. Please reach out Cittabase for more information.



7 Comments

Comments are closed.