Power BI Connection Types

The most common way to use Power BI is to import the data into Power BI and define the structure of the data model there. In many situations, this is the best way to use Power BI. However, it is not the only way to use Power BI.

Power BI offers three different methods for connecting to a data source. Each of these connection types offers distinct advantages. Each has situations where it is the right solution.

This blog will give you information on each of the three connection types and when they can be used to best advantage.

Import

The Import connection type loads and stores the data within the Power BI model at the time the model is created. The original source for that data is not accessed again until we need to update the data contained in the model. This allows Power BI to keep the model data in memory when it is being queried so we get great performance.

Once we have the data loaded into the Power BI model, additional organization and structure can be added. Names of tables and columns can be changed to be more user-friendly. Hierarchies can be defined. Relationships can be created. Business definitions can be added in the form of measures and KPIs. These additions make up the structure of the data model. With the Import connection type, all of this structure is stored as part of the Power BI model itself.

To summarize, when using Import mode, the data and the model structure are both stored within the Power BI model. This is represented by the bottom row of the Power BI Connection Types diagram. The big advantage of Import mode is the speed gained from use of the in-memory model verses the query performance of whatever might be providing the underlying data. The major disadvantage of Import mode is the data is refreshed from the data source on a periodic basis. That means the data can get a bit stale (out-of-date). Also, there is some model downtime required each time the data is being reloaded.

Live Connection

The Live Connection mode knows how to take advantage of the inherent capabilities of a Tabular or multidimensional model hosted by SQL Server Analysis Services. None of the data is loaded into Power BI. Instead, Power BI interacts with the SQL Server Analysis Services model to get the data needed each time a user makes a request.

Further, we do not create any data model structure within Power BI. Any definition as to how we calculate values or what tables are related to each other, etc. comes from the enterprise data model we are connected to. With Live Connection mode, we allow Power BI to serve as a highly capable data visualization and exploration tool for data residing in enterprise-level data models.

In short, the Live Connection mode depends entirely on the data and the model structure stored in the data source. This is represented by the top row of the Power BI Connection Types diagram. The advantage of Live Connection mode is we can quickly take advantage of existing enterprise-level models. We don’t have to duplicate the effort that went into creating that enterprise model. Also, multiple users can access the same data model, again saving on duplication of effort. The disadvantage of Live Connection is we do not have the opportunity to add our own content to the model definition. We can perform self-service data exploration using the existing data model, but we can’t create truly self-service business intelligence.

Live Connection is only available when connecting to Tabular and multidimensional models on SQL Server Analysis Services. Live Connection can be used on a limited basis with Tabular models in SQL Server Analysis Services 2016 Standard Edition. It is fully supported in both Tabular and multidimensional models in SQL Server Analysis Services 2016 Enterprise Edition.

Direct Query

As alluded to earlier, there is a third path when it comes to Power BI connection types. This is Direct Query. As you might have guessed (or as you might have seen in the Power BI Connection Types diagram), Direct Query is in between the functionality of Live Connection and Import.

Like Live Connection mode, Direct Query leaves the data residing in the data source. It does not import the data into the Power BI model. Instead, it queries the data from the source when it is needed to respond to a user request. In this case, T-SQL queries are constructed behind the scenes to pull the required data from the database. This means we are always getting live, up-to-date data for our analysis. The disadvantage is we may experience poorer performance, especially if we are querying and aggregating large amounts of data.

Like Import mode, the model structure is maintained within the Power BI model. That means we have control and can shape that structure however we like. We can do truly self-service business intelligence.

We can use Direct Query mode with:

• SQL Server relational databases
• Azure SQL Database
• Azure SQL Data Warehouse
• SAP HANA

Setting the Connection Type

The connection type is set as you define your connections in Power BI. If you choose to create a connection to a SQL Server database or other data source type supported by Direct Query, you will have the option to choose between the Import connection type and the Direct Query connection type as shown here.

If you choose to create a connection to a SQL Server Analysis Services Server, you will have the option to choose between the Import connection type and a live connection as shown here.

To avoid duplication of effort and to get the desired data freshness and query performance, choose the Power BI connection type that is right for you each time you work with Power BI.