Power BI Connection Types

I’ve noticed quite a few questions lately on Power BI Community forum related to how Power BI Desktop works with data sources. The questions aren’t specific to particular data sources or being able to connect to them, but how Power BI connects to them. There are different ways to connect to various sources, but there are also some limitations depending on what method you choose. As a result, I wanted to take a quick moment to outline the different connection types, and in what scenarios you can use them. I’m going to list these in order based on the number of data sources you can connect to from least to greatest.

The first connection type is the “Live Connection”. This option is only available if you use SQL Server Analysis Services (SSAS), which comes in two flavors, Multidimensional or Tabular. With live connection, you can directly access an already built model/cube. This method allows you to plug Power BI in as the front end visual component to your already developed solution. It allows you to leave your data on premises and for much larger model/cube solutions the added benefit is having that dedicated hardware to power your BI project. It offers the most secure and robust method for building and maintaining a Power BI solution.

Use With

SSAS Multidimensional

SSAS Tabular

The second connection type is “Direct Query”. It differs from the live connection in that it provides a method to directly access your data from your databases instead of a model/cube. Direct Query allows you to access even larger repositories of data, and also keeps your data on premises. Unlike “Live Connection” you can leverage the Power BI Desktop to build your business layer within Power BI. This means that you can extend the data in your database by adding additional calculations in the form of measures or calculated columns within Power BI Desktop.

Use With

SQL Server

Azure SQL Database

Azure SQL Data Warehouse

SAP HANA

The third and final option is “Import”. In fact, if you don’t use one of the above sources for data, you may not even know that there is this option since it is the default for all other data sources. This connection does not pass queries back and forth to your model or database, but rather, it consumes the data from the data source into the Power BI Desktop file. It is the only method that allows you to fully shape, transform and manipulate your data within Power BI Desktop itself.

Use With

              Any available supported data source in Power BI Desktop

Can you mix connection types?

No. Once you secure a connection to a data source with one type, you cannot connect to another data source with a different connection type in the same PBI Desktop file.

Can I change connection types?

Yes and No. You can change from a Direct Query or Live Connection to Import if you want to connect to another data source, but you cannot change from Import to Direct Query or Live Connection. The switch to Import is permanent in a file, so be sure that is what you want to do if you start with either of them.

Is Direct Query the same thing as Live Connection?

No. As outlined above, these are two different types of connections and should be referenced as such. Direct Query transforms your DAX calculations into the language of the database. Live Connection directly accesses a model/cube searching for Tabular metadata and querying in DAX.

What are some of the drawbacks of each connection (on a high level)?

Importing has a few drawbacks. There is a 1GB limit to the file, so it only scales so high. It could take a while to load or refresh larger data sets, or data sets that have been heavily manipulated. You are only able to refresh a dataset 8 times a day in the Service.

Direct Query can be a bit slower than imported data, there are a few DAX limitations, and if it isn’t a dedicated reporting database you are impacting the database and server with “x” number of queries from users.

Live Connection requires Enterprise level SQL (up until SQL 2016). SQL 20016 offers limited Tabular SSAS usage in Standard edition.

I hope this high-level intro to connection types in Power BI guides you in the right direction. If there are any other questions related to these connection types that you have? Ask in the Comments, and I’ll be sure to add them above for future reference by others.