Demystifying “DirectQuery” and “Connect Live”

The terms “DirectQuery” and “Connect Live” are somehow confusing. I saw lots of people are using both terminologies as alternatives. But, the context of “DirectQuery” and “Connect Live” are very different indeed. Therefore, if use a a terminology when we’re talking about a different context then the whole situation might get quite confusing. in this post I try to explain the differences and make it more clear to prevent using a wrong terminology and make sure everyone is on the same page when we’re referring to “DirectQuery” or “Connect Live”.

When we use the “DirectQuery” terminology we are actually talking about connecting from Power BI Desktop instance to an RDBMS type of data source like SQL Server DB or Oracle DB.

There are two types of data connections when we’re connecting to RDBMS like SQL Server or Oracle DB from Power BI Desktop:

• Import Data: which literally loads data into the underlying model to make it available in memory
• DirectQuery: which doesn’t load data into model. Instead, it runs multiple concurrent queries on the RDBMS side (data source side) and gets the results. This is good to support real-time data processing.

Note: The same principal applies to SSAS Tabular.

On the other hand, when talk about “Connect Live”, we are referring to the data connection type from a reporting tool like Power BI Desktop OR Excel to an instance of SSAS, either SSAS Multidimensional or SSAS Tabular.

There are also two types of data connection when you connect from Power BI Desktop to an instance of SSAS:

• Data Import: This is when you want to load data from an SSAS Cube or an SSAS Tabular model. Therefore, you may write MDX or DAX queries depending on the underlying technology to load a portion of data from SSAS to your reporting tool. This is only useful if a business logic hasn’t been implemented in the semantic model (either SSAS cube or Tabular model) and you need to quickly produce a report. For sure the business logic have to be implemented in SSAS later.
• Connect Live: The recommended type of connection used for reporting in an Enterprise BI solution. In this type, all business logics are captured in the semantic model and made available for all reporting tools. Therefore, Power BI Desktop turns to a report authoring tool only and you cannot create any new business logics in the report. For sure you can create Report Level measures, but, basically all the business logic sits in SSAS side.

Here is a generic architecture for an enterprise level design in terms of data load in different contexts:

We almost always use “Import Data Mode” to load data into SSAS to make it available for reporting tools, unless data latency requirement dictates using DirectQuery to support real-time data processing.

And we always tend to “Connect Live Mode” to make reports on top of an instance of SSAS (either Multidimensional or Tabular Models), unless we need to urgently create a report with a specific business logic that hasn’t been implemented in SSAS yet.

Now that you know the differences between “DirectQuery” and “Connect Live” if someone says “there are some DAX limitations if we use “Connect Live” you should immediately ask them to elaborate as the DAX limitations only apply to the connections with “DirectQuery” mode and NOT “Connect Live”.