Live Connection; When Power BI comes Hybrid

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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:

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://msftdbprodsamples.codeplex.com/downloads/get/353143

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

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

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

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

2016-07-03_23h15_36

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

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

2016-07-03_22h59_12

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.

2016-07-04_00h04_39

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.

2016-07-04_00h10_06

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

Then in 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

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

Summary

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *