Views:

Introduction

In this document we will answer the following questions:

  • What is an Analysis Services LiveConnection?
  • The difference(s) between LiveConnection and Import/DirectQuery?
  • How to configure Azure Analysis Service?
  • How to configure On-Premise Analysis Service?

What is Analysis Services LiveConnection

LiveConnection is a type of connection to a data source. This type of connection does not store a second copy of the data into the memory. Instead, data remains in the data source, and visualizations will continuously query the data source from Power BI.
This is the fastest way to connect Power BI to a source. It also gives extra benefits: your model can be stored in source control, you can use partitioning and scaling on your model, and easily connect tools like Excel to it.
There are 3 flavors:

  • Azure Analysis Services – Tabular (AAS).
    This is a PAAS environment in Azure, where you have a pay-per-use model. Power BI Datasets are also based on Tabular models. So the same syntax (DAX) is used to describe measures. You can even load a Power BI Dataset in Analysis Services. All security is managed through Azure Active Directory (AAD).
     
  • SQL Server Analysis Services – Tabular (SSAS-T).
    The big difference with AAS is that it’s an ‘on-premise’ solution, where you have to manage SQL Server and the OS yourself. The security is set through Active Directory. This gives some complications because Power BI works with AAD.
     
  • SQL Server Analysis Services – Dimensional Cude (SSAS-DC).
    The difference here is that the model language is in MDX, whereas the Power BI language is in DAX. Because of this, importing Power BI models is not possible. Plus, you would also have to learn another syntax.

Therefore, the first option, Azure Analysis services, works best with Power BI and Webdashboard. The on-premise variants will need some extra work.

LiveConnection vs Import/DirectQuery

There is a big technical difference between LiveConnection and Import/DirectQuery. With LiveConnection you connect directly to the Model Structure (dataset) on Analysis Services. You also go through Power BI with the same authorization token. The dataset you see in Power BI, isn’t used at runtime, only at design time.
For both Import and DirectQuery, you always only connect to Power BI, because the Model Structure is located in Power BI.

Configuring Azure Analysis Services


Webdashboard in a Standard environment

Webdashboard creates a Publish Account for you which includes a Power BI Pro license. When connecting to your datasets (Model Structure), Webdashboard will use that Publish Account to gain access. This works for Direct Query and Import because the model structure is inside Power BI (see figure on the previous page). In the case of a LiveConnection the Model Structure is located in a different Azure Active Directory. To make this work, 2 steps are needed:

  1. Add the Publish Account as a guest to your Azure Active Directory (and accept the invite).
    You can find your Publish Account under Gear icon -> Settings -> Publish Account.

    Guests’ accounts can be added through your Azure Portal
    Quickstart: Add guest users in the Azure portal - Azure AD | Microsoft Docs
 
  1. Give the Publish account Read permissions on your Azure Analysis Service.
    You can do this by adding a new role, or just adding it as a server admin in the Azure portal.
    https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-manage-users


Now your LiveConnection is ready to go. Please note that you configure the read permissions in the Tabular model Role. This Role needs to be configured in Webdashboard. For more information: How to: Configure Row Level Security (RLS) – Webdashboard.

Webdashboard in a Hybrid/Enterprise environment

In a Hybrid/Enterprise Environment the connection is made through a Service Principal. This can’t be done in a Standard Environment, because a Service Principal can only live in 1 Azure Active Directory Tenant. It’s the most secure way to connect to Power BI Embedded, because no User Account is needed.
For more information please read this: https://docs.microsoft.com/en-us/power-bi/developer/embedded/embed-sample-for-customers?tabs=net-core

Adding Webdashboard to your Hybrid/Enterprise environment is done with a Service Principal. All you have to do is give this Service Principal Read permissions to your Azure Analysis Server and you are ready to go.

Row Level Security (RLS)

Normally (for Import and DirectQuery) Webdashboard sends the users email property that is logged in to Webdashboard in the connection to the dataset. In your RLS implementation you can filter your data with the expression USERNAME().
LiveConnection defines it’s RLS roles in Analysis Services and not in Power BI. Next to that, the security is not only checked in Power BI, but also in Analysis Services. Which means that the Username must be an account that has direct access to the Analysis Model.
Solution: Webdashboard sends the Publish Account as the username and the email property of the user through CustomData. Now you can access the email of the logged in user with the DAX expression CUSTOMDATA().
Using row-level security with embedded content in Power BI embedded analytics - Power BI | Microsoft Docs

Configuring SQL Service Analysis Services

This requires a few steps. To summarize:

  • An on-premises data gateway needs to be configured between Webdashboard and your own environment.
  • A ‘Directory Synced’ (Account that is known in your Azure Active Directory and in your on-premise Active Directory) needs to be present.
  • This account needs to be known by Webdashboard and has access to your Analysis Service

To connect the data gateway you can follow the steps in the Wizard available in Webdashboard. The most important things to remember while configuring:
  • Use the Webdashboard Publish Account to sign-in to the Gateway. This is how the connection is made to the correct Webdashboard Power BI Workspace.
  • Install the gateway in a location that/which can also access the SQL Server Analysis Service.

The wizard can be found here:


After completing the wizard 3 more steps need to be taken:
Add your Analysis Service to the gateway:

Create a Service Account in your On-premise Active directory. Sync it with your Azure Active Directory and:
Add it to the Webdashboard Power BI Workspace

Add it as a Gateway admin and Gateway user on the datasource

Figure 1 - gateway admin
                       
Figure 2 - Gateway user
Add it as Analysis Services Admin on your Analysis Service












 

 

Check customdata in you data gateway datasource. This is a hidden button.




Now everything is configured, but Webdashboard doesn’t know yet it needs to use your Service Account to load the On-Premises LiveConnection. For this, we need the Username and the password for this account known in Webdashboard.
You naviagte to Settings -> Settings -> Power BI and scroll down to the bottom. Here you fill in the account you configured everything with. Webdashboard will use this account to gain access to the data.

Standard vs Pro

In this case there isn’t much of a difference between Standard and Pro Webdashboard environments. Webdashboard will connect to all your Model structures with a Service Principal. These are not known in your on-premises environment. So all these steps need to be done in both situations. With one exception:
The Power BI Gateway must be installed with your Service Account and not with your Publish Account.

Row Level Security (RLS)

Normally (for Import and DirectQuery) Webdashboard sends the users email property that is logged in to Webdashboard in the connection to the dataset. In your RLS implementation you can filter your data with the expression USERNAME().
LiveConnection defines it’s RLS roles in Analysis Services and not in Power BI. Next to that, the security is not only checked in Power BI, but also in Analysis Services. Which means that the Username must be an account that has direct access to the Analysis Model.
Solution: Webdashboard sends the Service Account as the username and the email property of the user through CustomData. Now you can access the email of the logged in user with the DAX expression CUSTOMDATA().
Using row-level security with embedded content in Power BI embedded analytics - Power BI | Microsoft Docs

Keywords: Live Connection, Analysis Services, SSAS, AAS, SQL Server Analysis Services, Azure Analysis Services, Power BI