Import Data vs. DirectQuery vs. Live Connection

Import Data

Imports whole dataset into Memory. This memory will be the memory of machine hosting your Power BI report. With the span of time this data becomes stale so needs to be updated that you can schedule and that’s why this technique is also called as Scheduled Refresh.

Import Data is supported by almost all connection types
Excel Data Source supports only Import Data connection.

This is the fastest mode of connection types. It is always faster to query the data stored in memory rather than querying it from disk (DirectQuery)

DirectQuery

When the dataset is very large, it may not be possible to load/fit all data into memory so there it is where DirectQuery comes into picture. In this connection type, data is not loaded into memory, only metadata is loaded into PowerBI report. Your report is directly connected to your data source. Anytime, you are seeing your report, you are looking at the most recent state of data from your data source.

Not all data sources supports this connection type. Generally, relational databases or data sources that have a modelling engines supports DirectQuery e.g. Azure SQL Database, Azure SQL DWH, Amazon RedShift, IBM Netezza etc. Any data source that supports DirectQuery also supports Import Data.

Since, no data is brought into memory, Data tab of PowerBI is not visible.

Whenver, you decide to use DirectQuery connection type, immediately start thinking about performance tuning as you are going to directly deal with the data at source i.e. you need to have appropriate indexes in source tables. In addition to traditional indexes, consider having ColumnStore indexes too.

This should be your choice only when Import Data or Live Connection doesn’t fit your need/circumstances.

Live Connection

This connection has similarity with DirectQuery connection type that it also doesn’t store any data in memory. This connection type can be used with Power BI Service dataset and SSAS technology, both tabular and multi-dimensional, Azure or on-premise.

Like DirectQuery mode, this also sends a query to data source for every visualization but is faster than DirectQuery because queries runs on an analytical engine though it largely depends on your modelling and calculations defined in the analytical engine. That’s why, this connection type is also a candidate to consider performance tuning at source.

With LiveConnection, you don’t have the Relationship tab as well since all this is defined at source so PowerBI merely act as a visualization tool though you still can create the measure in report which are called as Report Level measures. While report level measures gives flexibility to the report but reduce the governance and centralized feature of Live connection.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s