Live Connection; When Power BI comes Hybrid

Live Connection is another type of connection in Power BI. This type of connection is somehow similar to DirectQuery because it won’t store data into memory. However, it is different from DirectQuery, because it will give you the analytical engine of SQL Server Analysis Services Tabular. With this method, you get benefits of both worlds; large-scale model size, and the analytical power of Analysis Services. In this post, you’ll learn the details of this type of connection, things that you need to consider, and how to set it up working with gateway.

What is Live Connection?

Live Connection is a type of connection only to three types of data sources. This type of connection does not store a second copy of the data into the memory. Data will be kept in the data source, and visualizations will query the data source every time from Power BI. The only three types of data sources supported by this type of connection are:

  • SQL Server Analysis Services Tabular
  • SQL Server Analysis Services Multi-Dimensional
  • Power BI Service Dataset

These three types are SQL Server Analysis Services (SSAS) technology. You cannot have a Live connection to SQL Server database engine itself. However, the SSAS technology can be cloud based (Azure Analysis Services), or on-premises (SSAS on-premises).

How to create a report with Live Connection

To create a Live Connection example, you need to have SQL Server Analysis Services Tabular model installed. You can download SQL Server trial 180 days edition from this link:

After installing SSAS Tabular instance, then you can restore AdventureWorks Tabular model on it. The database can be accessed and downloaded from here:

In this post, we are not going into details of how to set up or install SQL Server, or SSAS tabular model.

Open a Power BI Desktop, and go through Get Data, from Analysis Services.

When connecting to SQL Server Analysis Services database, you can choose “Connect live”;

The SSAS installed on my machine is in the tabular instance name, that is why I put the server name as “.\tabular”. For your machine, the set up might be different. In the Navigator window, then you can select the Adventure Works Internet Sales model.

Note that you just choose a model, not tables. And the model includes multiple tables, with their relationships, hierarchies, and calculations will be connected to Power BI. You can see on the right bottom side of Power BI Desktop that it is mentioned Live Connection.

How Live Connection Works behind the scene?

Consider that we have a Power BI report like the screenshot below;

You can run SQL Profiler on Analysis Services, to capture all DAX queries sent to the database. Here are queries posted to the database for above report;

Like the DirectQuery mode, Power BI Desktop will send a query for every single visualization to the database. However, because these queries run on an analytical engine, usually, you can expect faster result (this might be different depends on many factors).


Live Connection can work faster than DirectQuery in many scenarios. However, in every implementation, many factors affect the performance, and there are always exceptions. In general, because Live connection connects to an analytical engine, then calculations and analytical results come faster than a query from the database.

Using live connection to SSAS tabular is usually faster because the data is stored in the memory of the machine that runs SSAS Tabular. However, depends on the network bandwidth, how calculations and model are implemented, it might change.

Live Connection, however, is not as fast as Import Data. Import Data is the fastest possible option regarding performance in Power BI. A live connection is the second in the list, and DirectQuery is the last one, and the slowest one.

Although the performance of the Live Connection is usually better than the DirectQuery, however, I highly recommend performance tuning of SSAS Model. SSAS Model can perform fast if the server specification is at the right scale, the data model is designed well, and calculations are written a proper way. If any of the items mentioned above doesn’t apply, then the performance of SSAS server and the Live connection will decrease.

Performance Tuning in Live Connection is a Must to do.

Single Data Source

Like the DirectQuery mode, Live Connection is only supporting one source for the connection. If you want to have more than one source for a connection, then you need to bring them all into the SSAS model.

No Power Query

With Live Connection, Power Query transformations are not available at all. In fact, the Edit Queries and all related options of that are disabled in the Live Connection mode.

All data transformation needs must be handled before loading data into SSAS model. Because SSAS is not a data transformation tool, you can leverage SSIS to do the data transformation before loading data into the data warehouse, and then process data from the data warehouse into an SSAS data model. Here is a sample scenario;

With the Live Connection, you don’t have the relationship tab as well. Power BI becomes a mere visualization tool in this mode:

Modeling and Report Level Measure

With the Live connection, your modeling in Power BI is very limited. You can only create measures. The type of measure that you create a Live Connection is called Report Level Measures. Report Level Measures as the name of it explains, is only for this Power BI report. If you create another Power BI report connected live to the same data source, you cannot use the report level measures built in previous Power BI report.

To create a report level measure, click on Add New Measure to the Modeling tab.

As you can see in the above example, I have created a report level measure for calculating sales year to date. The code is as below;

    [Internet Total Sales],

Same code, when sent to SSAS, can be fetched with SQL Profiler as below;

DEFINE MEASURE 'Customer'[Sales YTD] =
    [Internet Total Sales],
      ROLLUPADDISSUBTOTAL('Date'[Date], "IsGrandTotalRowTotal"),
      "Internet_Total_Sales", 'Internet Sales'[Internet Total Sales],
      "Sales YTD", 'Customer'[Sales YTD]
  [IsGrandTotalRowTotal] DESC, 'Date'[Date]

Report Level Measures are not bound to the SSAS model. Report level measures are only for the current Power BI file, in other files they must be created again.

Report Level measures are useful for flexibility and giving the user, self-service functionality. However, they will reduce the governance and centralized modeling feature of Live connection.

Publishing the Report and Gateway Configuration

Let’s now have a look at how to publish and configure this report in the Power BI service. Publishing the report is like publishing any other reports. You also know from previous posts, how to set up the gateway. So let’s go straight to the point of adding data sources.

Create Data Source

Now Let’s create Data Sources. You might think that one gateway is enough for connecting to all data sources in a domain. That is right. However, you still need to add a data source to that gateway per each source. Each source can be a SQL Server database, Analysis Services database, etc. For this example, we are building a data source for SQL Server Analysis Tabular on premises. Before going through this step; I have installed AW Internet Sales Tabular Model 2014 on my SSAS Tabular, and want to connect to it.

For creating a data source, click on Add Data Source in manage gateways window (you have to select the right gateway first)

Then enter details for the data source. I named this data source as AW Internet Sales Tabular Model 2014, I enter my server name, and database name. Then I use Windows authentication with my domain user <domain>\username and the password. You should see a successful message after clicking on Apply. The domain name that I use is BIRADACAD (my SSAS Tabular domain), and the user is PBIgateway, which is a user of BIRADACAD domain (username: BIRADACAD\PBIgateway) and is an administrator for SSAS Tabular (explained in next few paragraphs).


Note that the user account that you are using here should meet these conditions:

  • It should be a Domain User
  • the domain user should be an administrator in SSAS Tabular.

You can set administrator for SSAS Tabular by right-clicking on SSAS Tabular instance in SSMS and Properties window,


in the Security settings tab, add the user to administrators list.


Effective User Name

Gateway account used for accessing Power BI cloud service to on-premises SSAS Tabular. However, this account by itself isn’t enough for the data retrieval. The gateway then passes the EffectiveUserName from Power BI to on-premises SSAS Tabular, and the result of the query will be returned based on the access of EffectiveUserName account to SSAS Tabular database and model.

By default, EffectiveUserName is the username of logged in user to Power BI, or in other words EffectiveUserName is the Power BI account. The Power BI account should have enough access to SSAS Tabular database to fetch required data. If Power BI account is the account from the same domain as SSAS Tabular, then there is no problem, and security configuration can be set in SSAS Tabular. However, if domains are different, then you have to do UPN mapping.


UPN Mapping

Your SSAS Tabular is part of a domain (it should be actually because that’s how Live connection works), and that domain might be the domain that your Power BI user account is. If you are using same domain user for Power BI account, then you can skip this step. If you have separate Power BI user account than the domain account for SSAS Tabular, then you have to set the UPN Mapping.


UPN Mapping in simple definition will map Power BI accounts to your local on-premises SSAS Tabular domain accounts. Because in my example I don’t use the same domain account for my Power BI account, so I set up UPN as below.


Then in Mapping pane, I create a new mapping that map my Power BI user account to, which is my local domain for SSAS Tabular server.


Now with this username mapping, will be passed as EffectiveUserName to the SSAS Tabular.

Live Connection to Power BI Service

Another type of Live connection is to connect to a dataset published in Power BI service. This dataset will be treated as an SSAS instance. The dataset in Power BI service is hosted in shared SSAS cloud environment. When you connect to it from Power BI Desktop, it is like connecting to an instance of SSAS with a live connection.

For this option, you need to be logged in to the Power BI Desktop. You will see list of all datasets that you have Edit access on those, and then you can choose one of them;

After connecting to the dataset, then you will see the Live Connection message at the bottom right-hand-side of the Power BI Desktop. This method will work precisely similar to connection to SSAS with the Live connection.

Connecting to another dataset in Power BI service with Live Connection is a right approach for multi-tenancy development. One user can build the model, and another user can work on another Power BI file for visualization part of it. I’ll explain more about this in future posts.

Live Connection is different from DirectQuery

One of the mistakes that a lot of Power BI developers make is to consider DirectQuery and Live Connection the same. These two types of connection are different in many aspects. Here is a list of differences between these two;

  • DirectQuery is a connection mainly to a non-Microsoft database or analytical engines, or to relational databases. (such as SQL Server, Teradata, Oracle, SAP Business Warehouse, )
  • Live Connections is a connection to three sources: SSAS tabular, SSAS multi-dimensional, and Power BI dataset.
  • DirectQuery for some of the data sources (such as SQL Server database) still has a limited Power Query functionality available.
  • Live Connection has no Power Query features in it.
  • You can create simply calculated columns in DirectQuery; these will be converted to T-SQL scripts behind the scene.
  • You cannot create calculated columns in Live Connection.
  • You can use Report Level measures with the ability to leverage all DAX functions in Live connection.
  • In the DirectQuery mode, you can have limited measure abilities. For more complex measures, you must use the option to allow unrestricted measures in DirectQuery mode, which is not recommended, because it will slow down the performance for some expressions significantly.
  • DirectQuery mode usually is slower than Live connection.
  • A live connection is usually less flexible than the DirectQuery.

As you see the list above explains that these two types of connection are entirely different.


In this post, you’ve learned about Live Connection. This type of connection is only available for three data sources; SSAS tabular, multi-dimensional, and Power BI dataset. This type of connection is much more limited than the DirectQuery because you have no access to Power Query or relationship tab.

However, the Live connection, is providing a better solution than the DirectQuery, because the ability to write DAX code is fully possible if SSAS tabular is used as a data source. With Live Connection, you get the benefit of both words; because data is not stored in the Power BI, the size limitation of Import Data does not apply here, and the data size can be big. Also, because SSAS tabular can leverage DAX, and the analytical power of Power BI is based on DAX, then the analytical engine of this solution is also very efficient.

A live connection is faster than DirectQuery in most of the cases. However, it is still slower than Import Data in the performance. The recommendation is to start with Import Data, if not, then the second option would be Live Connection. If none of them can be applied (for example if you are working with huge amount of data, and the SSAS licensing is not available because Microsoft toolset is not used for analytics in your company), then you can choose the last option which is DirectQuery.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

14 thoughts on “Live Connection; When Power BI comes Hybrid

  • Hi Reza,
    maybe you have experience with the following topic (SSAS + Excel
    via PBI service)

    My intention:
    1. Connect Excel to SSAS-Tabular (OnPrem) & Create Pivot Report (OK)
    2. Publish from Excel-Desktop to PBI Service (File -> Publish -> Workspace) (OK)
    3. Use of the Excel report within Power BI Service with a live connection (including RLS) via gateway. (FAILED)

    And that does not work.

    Any idea?

    • Hi Denis.
      I’m not sure I can follow exactly what you are saying here. How does Power BI connect to SSAS? or you don’t use Power BI at all?

  • Hi Reza,
    You said we can make Report Level measures in Connect Live mode.
    I’ve connected to my Multi Dimensional Cube via a Live connection but the “New Measure” button is still inactive.
    Is there any thing wrong there?

    • Hi.
      The report level measure is available for tabular edition of SSAS, and only from certain versions of SSAS onward

  • Hi Reza,
    I have several SSAS cubes. I already work with one of them using a live connection from Power BI.
    My question is :
    Is it possible to have one Dashboard composed of several reports, each report is connected through a live connection to a different cube ?

    Dashboard1 <== report1 <== liveconnection to SSAS CUBE 1
    report2 <== liveconnection to SSAS CUBE 2
    report3 <== liveconnection to SSAS CUBE 3


    • Hi Carine
      You have more limitations in the DirectQuery for your measures. These days a lot of limitations on writing DAX expressions on the DirectQuery is also lifted, It might, however, create some really bad performing SQL scripts behind the scene.

  • Hi Reza
    I am working with Power Bi report server and want to connect SSAS tabular model in Live connection mode. The problem is my servers are not in the same domain and I am not able to connect to Tabular with this domain windows credential. Is there any way to set the different domain windows credential in power bi RS ? Or if there is any other solution please advise.

    • if this was Power BI service, you had the UPN Mapping table, which you could configure and then you’d be fine.
      However, for the Power BI report server, I haven’t found that settings (never needed it though). Why the report server cannot be installed on the same domain as SSAS then? that seems to be another simple option to do.

  • Hi Reza, first, thank you for sharing so much of your knowledge. I love this website (and Power BI ;)). My question is whether I should be able to connect to my Power BI dataset through the Analysis Service connector. I would love to, because I would like to use Perspectives I created in Tabular Editor in my Power BI dataset. But I get errors concerning credentials and I can’t find a solution anywhere. Cheers Hanneke

    • Hi Hanneke
      first of all; perspective would come to Power BI eventually. that should not be your reason to move to SSAS live connection.
      If you have problems in credentials, check how you have set up the gateway. I have a blog article about that and setting up the UPN mapping

  • Hi Reza, thank you so much for a brilliant post. My question is for real time data with large dataset (azure synapse)would it be better to have direct query or having SSAS tabular which also needs to connect using direct query in turn. I am asking considering the pros and cons you mentioned above for both. Or I should go for composite model in power bi dataset. Many thanks for your help.

    • Hi Triparna

      I guess by real-time you mean “having up-to-date data in the report”. Because real-time in reality means as soon as a change happened in the data, the report gets notified, and refreshes. if you really mean real-time, then there is only one option which is using Power BI streaming dataset.
      but if you mean having up-to-date data, and also the size is reasonable, then I would suggest a composite mode with the big tables to be DirectQuery and smaller to be import, and with many aggregated tables for performance.


Leave a Reply

%d bloggers like this: