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.

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

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;

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:

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;

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)

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

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

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

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

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.