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 in 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 a 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 in the memory. Data will be kept in the data source, and visualizations will query the data source every time from Power BI. The only four types of data sources supported by this type of connection are:

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

These four types are SQL Server Analysis Services (SSAS) technology. You cannot have a Live connection to the 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 the SQL Server Analysis Services Tabular model installed. You can download the SQL Server trial 180 days edition from this link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

After installing SSAS Tabular instance, then you can restore AdventureWorks Tabular model on it. The database can be accessed and downloaded from here: https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks-analysis-services/adventure-works-tabular-model-1200-full-database-backup.zip

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.

Get data from Analysis Services

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

Connect Live to Analysis Services

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.

Select the model from Analysis Services

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.

Live Connection enabled

How Live Connection Works behind the scene?

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

Sample Power BI report

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;

DAX codes sent to the Analysis Services database

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 results (this might be different depending on many factors).

Performance

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 the 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

Live Connection is only supporting one source for the connection. If you want to have more than one source for a connection, then you can either Import Data from the Analysis Services, or create a Composite mode using DirectQuery to Analysis Services (which is explained here).

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.

No Power Query in the Live Connection mode

All the data transformations 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;

Enterprise usage of Live Connection with a Data Warehouse and ETL

With the Live Connection, you don’t need to select each table separately. All the tables in the model will be available, and you can see the model diagram:

Model diagram for the Live Connection

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.

Report Level Measure

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;

Sales YTD = TOTALYTD(
    [Internet Total Sales],
    'Date'[Date])

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

DEFINE MEASURE 'Customer'[Sales YTD] =
  (/* USER DAX BEGIN */
TOTALYTD(
    [Internet Total Sales],
    'Date'[Date])
/* USER DAX END */)
EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Date'[Date], "IsGrandTotalRowTotal"),
      "Internet_Total_Sales", 'Internet Sales'[Internet Total Sales],
      "Sales YTD", 'Customer'[Sales YTD]
    ),
    [IsGrandTotalRowTotal],
    0,
    'Date'[Date],
    1
  )
ORDER BY
  [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. One good use case for the report level measure is the measures created for conditional formatting in the visualization.

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 report. 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 the manage gateways window (you have to select the right gateway first)

Creating a data source under the Gateway

Then enter details for the data source. I named this data source 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 the BIRADACAD domain (username: BIRADACAD\PBIgateway) and is an administrator for the SSAS Tabular (explained in next few paragraphs).

2016-07-03_23h15_36
Data source configuration

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,

2016-07-03_22h58_14
Properties of SSAS Server

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

2016-07-03_22h59_12
Security settings in SSAS Server

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 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 the EffectiveUserName account to the SSAS Tabular database and model.

By default, EffectiveUserName is the username of the user logged in 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 the required data. If the 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.

EffectiveUserName

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 the same domain user for the Power BI account, then you can skip this step. If you have a separate Power BI user account than the domain account for SSAS Tabular, then you have to set the UPN Mapping.

UPN Mapping Table

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.

2016-07-03_23h19_37
Map user names in the data source

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

2016-07-03_23h20_45
UPN mapping setting in the Power BI Service

Now with this username mapping, reza@biradacad.com 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 the Power BI service. This dataset will be treated as an SSAS instance. The dataset in the Power BI service is hosted in a shared Azure Analysis Services (AAS) 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 a list of all datasets that you have Build 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 the connection to SSAS with the Live connection.

Connecting to another dataset in Power BI service with Live Connection is a recommended approach for multi-developer environment. 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 connections 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, etc)
  • Live Connections is a connection to four sources: SSAS tabular, SSAS multi-dimensional, Azure Analysis Services, 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 some simple 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 a Live connection.
  • In the DirectQuery mode, you can have limited measure abilities. For more complex measures, you must check the performance (and some of the functions such as parent-child functions are not available), some complex measures might slow down the performance significantly.
  • DirectQuery mode usually is slower than Live connection.
  • A live connection is usually less flexible than DirectQuery.

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

Summary

In this post, you’ve learned about Live Connection. This type of connection is only available for four data sources; Azure Analysis Services, 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 Editor.

However, the Live connection, is providing a better solution than the DirectQuery, because the ability to write DAX code is fully possible if the SSAS tabular is used as a data source. With Live Connection, you get the benefit of both worlds; 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 cases. However, it can be still slower than Import Data in the performance (Depending on the SSAS server specification and the connection between the report and the server). 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 a huge amount of data, and the SSAS is not available because the Microsoft toolset is not used for analytics in your company), then you can choose the last option which is DirectQuery (However, always remember to use DirectQuery through a Composite Mode).

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

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?
      Cheers
      Reza

  • 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
      Cheers
      Reza

  • 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

    Thanks,

    • 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.
      Cheers
      Reza

  • 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.
      Cheers
      Reza

  • 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
      Cheers
      Reza

  • 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.

      Cheers
      Reza

Leave a Reply