Power BI supports several different connection modes to connect to data. We’ll explain the differences between the them and discuss the pros and cons of each one.

When creating a Power BI Dashboard it’s important to select an appropriate data connection. Data connection types will impact the features available to a Power BI developer, and can create a poor user experience for business users consuming the report.

Power BI has several connectivity options. Direct Query for real-time analytics, Import mode for faster, in-memory visualizations and Live Connections to leverage pre-existing data models. A hybrid model can be deployed to blend Direct Query and Import data sources for maximum flexibility.

There are a number of pros and cons to using each of the different connection types.

The Power BI Data Connection Type will have a big impact on how developers create and users interact with yoru reports

You should decide on a data connection type to use in Power BI prior to starting to build out your report or dashboard. While you can update the data source at a future point in time, there are some limitations that you need to be aware of.

Let’s jump in!

Key Considerations for Determining a Connection Method

Before you are being your report, you should determine which type of data connection to use. These are some of the most important things to keep in mind while building a report.

How Fresh Does the Data Need to Be? – Some users will want data that is up to date by the minute, this might be a manufacturing floor tracking variances or an accounting group interested in financials throughout the month end close process to see where net income lands. Other reports can be refreshed daily or semi-weekly for things like historic payroll trends.
Dashboard Performance – Report users love the flexibility of exploring data in Power BI. They do not love waiting for visuals to refresh after changing a slicer or filter. The connection mode has a large impact on user experience especially when large datasets are involved.
Data Source System – Not all source data supports Direct Query. Other times it would not make sense. If sales data is coming in from Oracle or SAP you may want the most up to date information. If sales are being consolidated from Excel Spreadsheets saved on OneDrive there would be no point to update them in real time.
Cost – Some cloud hosted data storage solutions charge businesses based on how much data flows in and out of a database. Direct Queries will cause more data to be pulled more often than scheduling a single import once a day of a dataset reducing user costs.
Data Availability – If you work for a company that has centrally curated a data warehouse or is very mature in their deployment of Power BI there may be data sources available to you that you can connect to and mitigate the need to build your own data model. Using something that

You should also explore if there are data sources already available within your company. If a business has central control over datasets, they may have already created a data warehouse, or Power BI data source that you can connect to.

Power BI Data Connection Types Explained

Let’s take a look at the different types of connections that Power BI Supports.

Once a Power BI report or dashboard is published to the Power BI Service, the data connection type is transparent to the end user, meaning that the report will be presented in the same way regardless. However, there can be speed and responsiveness concerns that impact the end user experience.

Power BI Import Mode

Import mode is the most common method of bringing data into Power BI. When utilizing import mode, Power BI will import and store the data in the .pbix file that Power BI desktop creates.

Data is compressed and stored in a database format called VertiPaq. The VertiPaq engine underlies the xVelocity in-memory analytics engine, which is at the heart of much of Power BI’s capabilities for data loading, transformation, and analysis. It is also used in Microsoft Analysis Services, and SQL Server’s Columnstore Index.

When the .pbix file is uploaded to the Power BI Service, the data in the file is stored in Azure Blob storage.

Power BI Direct Query Mode

Rather than importing data, Direct Query leaves the data in the source systems. When Power BI needs data to update a visual or bring into the report, it will send a query to pull in the new information. The biggest drawback with this method is that it typically takes longer for visuals to refresh because the data is stored in an outside system that has to be queried and brought into the report.

Power BI Live Connections

Live connections allow Power BI to connect directly to an Analysis Services database or to another Power BI data source. Refreshes are fairly quick, especially if it’s using a data source that already exists. Another benefit is the ability to leverage an pre-existing data model. The biggest drawback is that you cannot modify the data model in Power BI and several other features like text formatting are not available..

Power BI Composite Models

Composite models involve a blend of different data sources. This type of data model only exists when working with multiple data sources. You can setup some connections with Direct Query and other connections to Import. This method can give you some of the benefits of using each model. Importing more static data, while direct querying data that is refreshed more frequently.

For even more information on the differences between the available connection types, the guys at Guy in a Cube break it down even further.

Pros and Cons of Power BI Data Connection Types

Sure, here is a table of different features of Power BI Direct Query, Import, and Live Connections:

Feature	Direct Query	Import	Live Connections
Data is loaded	When a visual is created or interacted with	When the dataset is refreshed	When a visual is created or interacted with
Performance	Slower than Import with large datasets or slow connections.	Faster than Direct Query after scheduled data refreshes.	Can be slower than Direct Query, especially for large datasets
Flexibility	Can be used with any data source that supports DirectQuery	Can be used with any data source	Can only be used with data sources that support live connections
Cost	Can be more expensive than import depending on data source and query frequency.	Typically, less expensive than direct query due to fewer data transfers.	Can be the same as DirectQuery, depending on the data source
Recommendations	Use for data sources that are frequently updated and that require the latest data.	Use for data sources that are not frequently updated and that do not require the latest data.	Use for data sources that are hosted in Power BI and that are frequently updated.

Limitations of Direct Query and Live Connections

There are some very serious limitations to using Direct Query and Live Connections in Power BI. We want to highlight a few of the big ones as you’re deciding which import method to use.

The screenshot below is of Power BI Desktop after establishing a live connection to a Power BI Dataset that was previously published.

You’ll immediately notice two things.

No Power Query Editor Button on the Left
Column Tools are Not Available

Not having these features available means that there are a lot of features that you will not be able to work with. Just to name some of the features that you won’t have access to, here are some.

Cannot Change Text Formatting (Dollar Signs, Commas, Decimals etc.)
Cannot Sort By Another Column
Cannot Create New Columns
Cannot Modify Any Pre-Existing Columns

Pretty much everything you would normally do in Power Query to prep and blend data will not be available.

Example of the lack of options and Column Tools being greyed out in Power BI due to a live connection being enabled

With these severe limitations, you may find that you are able to create a number of workarounds using DAX formulas, but even then the amount of time it takes to set everything up may not be worth the effort.

Microsoft Learn has an excellent article outlining even more of the limitations of Direct Query

Summary of Differences Between Direct Query and Import Modes

While you cannot change a Direct Query data source to import, but you can change from importing data to direct querying.

Connection Type	Data is loaded	Edit data model
Direct Query	Not loaded into Power BI	No
Import	Loaded into Power BI	Yes
Live Connections	Not loaded into Power BI	No (but you can make changes to the data model in the underlying data source)
Composite mode	A combination of DirectQuery and Import	Yes

Note: If you need to update the data model of a previously published dataset, you can download the report or the data source from the Power BI Service (PowerBI.com) make the necessary changes and re-publish it. You can also use this method to start with an existing dataset, modify it for your needs and publish it under a separate name.

Conclusion

While there are a number of different ways to import data into Power BI there are two common groups.

Direct Query reach out directly to the underlying data source which returns the result of the query, and import mode which loads all of the data into memory or a .pbix file and returns the results of the query from the file.

While importing data is the best for many use cases and supports the most options for building reports it will always be limited by a maximum number of pre-scheduled refreshes.

Pros and Cons of the Different Power BI Connection Types