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.

Stairway to SQL Server Reporting Service: Decision Functions

As part of expressions, decision functions can be used in calculated fields, filters, formatting to dynamically control related properties. They can be used with SQL, MDX, DAX query to query data from database, cube, SSAS tabular model respectively. This article will give a brief introduction and comparison of three decision functions: IIf, Switch, Choose. Also examples of their usage are given at the end.

IIf Function

IIf function returns one of two values depending on whether the expression is true or not. The syntax is as below:

IIf( <Boolean Expression>, [Return if True], [Return if False])

It likes conditional logic in SSIS. The first parameter is the expression to be evaluated. If it is true, then return the value specified in [Return if True]; If it is false, then return the later value specified in [Return if False]. You can have nested multiple IIf functions and the later condition is dependent on previous conditions' evaluation.

Switch Function

Switch function returns the value associated with the first expression in a series that evaluates to true. The statements will be evaluated in the order in which they appear and the condition expression can be unrelated with each other. The syntax is as following:

=Switch(
       <Boolean Expression 1>, [Return if True 1],
       [Boolean Expression 2], [Return if True 2],
    ...
       [Boolean Expression n], [Return if True n],
      )

Choose Function

Choose function uses a single numeric evaluation to determine which of return values to return. The syntax is as below:

Choose(
     <Numeric Expression>, 
     [Return Expression 1],
     [Return Expression 2], 
     …, 
     [Return Expression n],
)

Comparisons of Three Decision Functions

As you can see, Choose function is quite different from the other two. Since the return only depends on the numeric evaluation specified. IIf and Switch function can be used interchangeably for a certain condition, as example of dynamic text font color below shown. For me, for case conditions are more than three, Switch function has clearer logic thus is easy to follow. However, Switch and Choose functions may have possible undefined results while IIf function won't since IIf function requires that a “return if true” value and a “return if false” value be provided in the expression.  One way to avoid this is to specify the last condition in Switch function to be TRUE as the following example.

 =Switch(Fields!OrderNumber.Value >= 10, "Blue", 
        Fields!OrderNumber.Value >= 5, "Yellow", 
 Fields!OrderNumber.Value >= 2, "Orange", 
 TRUE, "Red")

 

Problems	Expressions
To set filter expression depending on Country Code from MDX query.	Solution 1: =IIF(Parameters!CountryCode.Value.ToString() <> "[Location].[Country Code].&[USA]", "4", "6")  Solution 2: =IIF(InStr(Parameters!CountryCode.Value .ToString(),"USA")>0, "6","4")
To format the report category based on input date parameters ReportDate.	=IIF( InStr("January, February, April, May,July,August,October,November", Monthname(Datepart("m", Parameters!ReportDate.Label)) ) > 0, "Monthly","Quarterly" )+ Space(1) + "Reports"
To dynamically change the color of a text box, go to properties, and set the following expression for font/Color Property by Switch function.	=Switch(  Fields!OrderNumber.Value >= 10, "Blue",  Fields!OrderNumber.Value >= 5, "Yellow",  Fields!OrderNumber.Value >= 2, "Orange",  TRUE, "Red")
To dynamically change the color of a text box, go to properties, and set the following expression for font/Color Property by IIf function.	=IIF( Fields!OrderNumber.Value >= 10, "Blue", IIF(Fields!OrderNumber.Value>= 5, "Yellow", IIF(Fields!OrderNumber.Value>= 2,"Orange", "Red" ))))

Decoding Errors with Error Column Names and in SQL Server 2016 Integration Service

Problem: When SSIS packages failed at the step of loading files, sometimes it doesn't tell which column is the one that caused failure. How to find out the troublemaker?

Solution: 

If you have SQL Server 2016 Integration Service or later version, you can retrieve error column names by using the new GetIdentificationStringByID method in SSIS 2016.

Below is an example showing how to use IDTSComponentMetaData130.GetIdentificationStringByID method to decode error column names and error descriptions by using Script Component.

Step 1: Choose script component and connect it with error output from upstream.
Step 2: Select Transformation as script component type.

Step 3: After selecting C# as script language to use, check ErrorCode and ErrorColumn as Input Columns as shown below.

Step 4: Add output columns ErrorColumnName and ErrorDesc to represent error column names and descriptions respectively, then specify the column type and length.

Step 5: Edit script and added this following to Input0_ProcessInputRow function:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        
        IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
        Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
        Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    };

Then you can add a flat file output to write the errors with detailed information about error column names and descriptions.

From the following output, you can tell that the loading failure is because input column "OLE DB Destination.Inputs[OLE DB Destination Input].Columns[OrderDate]". The error description is "The data value cannot be converted for reasons other than sign mismatch or data overflow."