Comparisons of Import Data, DirectQuery, Live Connection modes in Power BI

Power BI has three connection modes to data sources, including "Import", "DirectQuery", and "Live Connection". Depending on types of data sources, you have different available options on connection modes. Sometimes you might get confused about which mode to choose from. This article will help you understand what are advantages and limitations for each mode.

What are different connection modes for Power BI?

• Import connections: to copy data from sources using cache, store and compress data in PBIX file, then present visual reports in Power BI desktop. 
• DirectQuery connections: to extract data directly from data sources and do data modeling to refine and enrich data. No data copy involved.
• Live connections: to extract data directly from existing data models either in SSAS tabular or SSAS Multidimensional.

Types of data sources supported

Below is the list of of sources supported by each connection modes as of November 2018. For those data sources supported by DirectQuery, they are also supported by Import. You can refer to Data sources supported by DirectQuery for more details about sources supported for DirectQuery.

We can see that Live Connection only support sources from SSAS Tabular, SSAS Multi-Dimensional.

Comparisons of connection modes of Power BI

Besides different types of data sources that each connection mode can support in earlier section, below is a brief summary showing major differences among these three modes to help you understand Pros and Cons for each mode.

Comparisons of Modes	Import Data	DirectQuery	Live Connection
Data Loaded To Memory?	Y	N	N
Direct Connection To Data Sources / Real-Time Data?	N	Y	Y
Multiple Data Sources Support?	Y	N	N
Modeling Supported	Fully	Limited	None
Power Query Supported	Fully	Limited	None
Scalability	1 GB dataset limitation.	No dataset size limit but 1 million row limit for returning data.	None
Reporting Limitations	No limit.	Not support Quick Insights and Q&A.	None

From comparisons above, you know that you need to choose "Import" connection mode if you need to extract data and build reports from multiple sources. On the other hand, if you have very large datasets that it would be unfeasible to import or near "real-time" reporting requires for frequent changing data, you need to choose "DirectQuery" connection mode.

Report performance considerations

For reports performance considerations, it really depends on underlying data sources and user cases as mentioned in Best Practice for Building Fast and Reliable Power BI Reports.

Note that for live connection and direct query modes, you need to use On-premises data gateway for data sources other than Azure SQL Database, Azure SQL Data Warehouse and Redshift.
Updated on Nov 07, 2018: Power BI Premium also introduced dataflows as another option to help unify data from various sources and prepare data for modeling by using Linked Entities and computed entities.

Difference Between COALESCE and ISNULL

ISNULL is a T-SQL function to replace NULL values as COALESCE does. At some cases these two functions get the same results but there are some differences. Below is a summary of their differences and I hope that it will help you decide which function to use when you need to determine the one with better performance or better concurrency control.

	COALESCE	ISNULL
Standard ANSI SQL?	Yes	No (Only for T-SQL)
Numbers of Parameters	Two or more parameters.	Two parameters only.
Data type determination of the resulting expression	Determined by the data type of parameters with the highest precedence	Determined by data type of the first parameter
Allow both inputs to be untyped NULLs?	No. For SELECT COALESCE(NULL, NULL); , it will return an error. You need to have at least one of the null values be a typed NULL.  SELECT COALESCE(CAST(NULL AS INT), NULL);	Yes. For SELECT ISNULL(NULL, NULL); it will return a NULL typed as an integer.
NULLability of the resulting expression	Considered to be NULL with non-null parameters.	Always considered NOT NULLable
Working with subquery as input parameters	When executing COALESCE((subquery), 1), the subquery is evaluated twice.  you can get different results depending on the isolation level of the query.	Evaluated only once.