SQL Chick

View Original

What You Need to Set Up the SSAS Connector in the Power BI Preview (Part 2)

Melissa Coates

***Note this post applies to the SSAS Connector which has been deprecated as of mid-2016. Refer instead to the Power BI Enterprise Gateway.***

Welcome to part 2 of a 4-part series on using the SSAS Connector with the new Power BI Preview. This information is as of early March 2015 and is subject to change as the Power BI Preview evolves.

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview (you are here)

Part III: Monitoring the SSAS Connector in the Power BI Preview

Part IV:  Connecting to Analysis Services From Power BI Designer and Excel

Prerequisites for Using the SSAS Connector with Power BI

1. An SSAS Tabular Model

In order to use the SSAS Connector for Power BI, you need a SQL Server Analysis Services database defined, deployed, and processed on a SQL Server instance configured for Analysis Services in Tabular mode. Power BI is not yet able to support a Multidimensional (OLAP) model - at this time SSAS needs to be configured as a Tabular instance. SQL Server 2012 or 2014 is supported with the SSAS Connector (since Tabular mode was introduced in SQL Server 2012).

More information: Tabular Model Projects

2. Roles Defined in the SSAS Tabular Model

Within your SSAS Tabular model, at least one security role must be created to grant Read permissions to the appropriate Windows users and/or groups. Within each role, you may restrict data elements as necessary (using row filters) to enforce row-level security.

More information:  Roles (SSAS Tabular)

3. Power BI Pro License

With the overhauled Power BI platform, there's two types of licenses: Free and Power BI Pro. As you can imagine, accessing Analysis Services data is a "corporate" type of feature and therefore requires a Power BI Pro license.

More information: Power BI Pricing

4. SSAS Connector Installed on a Domain Machine

In order for your Power BI cloud service tenant to communicate with your SSAS server(s), an SSAS Connector must be installed. The SSAS Connector acts as a proxy between the Power BI service and your Analysis Services server. Following are the pertinent details about the SSAS Connector:

  • The person installing the SSAS Connector must have possession of an ID and password with administrator privileges on the SSAS server. It's highly recommended that you create a specific Power BI service account, with administrator privileges on the appropriate SSAS DBs, rather than use an individual person's account. This account does not need to be a Power BI administrator (this is one area where the new Power BI Preview is very different than Power BI for Office 365). Things will be easiest if the password for the Power BI service account does *not* expire at frequent intervals; if the password does expire, in this initial release you will need to go back into the SSAS Connector and update the password in there since it stores those credentials.
  • The SSAS Connector needs to be installed on a domain-joined machine, and not be part of a workgroup.
  • The SSAS Connector doesn't have to be installed directly on the SSAS server though it can be if you prefer (generally if the user base is low, the same server is ok). The SSAS Connector should be installed on a machine in the same domain as the SSAS server, or in another domain that's trusted, and in the same vicinity as the SSAS server to minimize network latency. It should not be your personal laptop or desktop.
  • The SSAS Connector should be installed on a machine remains on, accessible at all times, with Internet access. If the SSAS Connector is unavailable, live queries from Power BI will fail with an "oops, something went wrong" message.
  • If you have more than one SSAS server you wish for Power BI to be aware of, then one connector needs to be set up for each server. 
  • .NET Framework 4.5 is a prerequisite on the computer where the SSAS Connector will be installed.
  • Configuring the server name and domain\user name for the SSAS Connector are case-sensitive.

All SSAS connectors that have been registered by any user show up after you do Get Data > SSAS. This could be a long list if you're in a big company. If a user tries to connect to an SSAS database that has been registered and doesn't have at least Read permissions, Power BI will then show a "No Databases Found" message.

Current limitations of the SSAS Connector:

  • At the moment a single SSAS Connector cannot register multiple SSAS servers.
  • The SSAS Connector cannot currently function properly on the same machine as where another Data Management Gateway has been installed.
  • The SSAS Connector doesn't currently have any scale-out functionality like the Data Management Gateway does. 
  • ID and Password for the Administrator are stored in the SSAS Connector; if the password expires without being manually changed in the SSAS Connector, connectivity will be down.
  • The SSAS Connector issues queries back to the SSAS Connector every 10 minutes to keep dashboards up to date. This frequency cannot currently be configured.

More information: Configure a Power BI Analysis Services Connector

      and Analysis Services Connector will not work in a One Way Trust - Possible workaround

5. Work ID Integrated with Active Directory

A requirement for all of this to work is that the ID/PW you use for Power BI is the same one used to access SSAS data (SSAS only supports Windows authentication). For most organizations that have Active Directory implemented, this won't be an obstacle. 

If you have signed up for Power BI with a personal account that is not integrated with an organizational account, the ability to connect to SSAS data is a bit more difficult to pull off. Greg Galloway has posted a really useful workaround for this in his blog: Setting Up a Demo of the Power BI Analysis Services Connector. I used Greg's approach of defining an alternate UPN for my personal/playground SharePoint Farm in Azure which has a virtual network of 3 machines: a domain controller, a SQL server, and a SharePoint server.

More information: Active Directory Domain Services

6. Only for users who sign into Power BI with an ".onmicrosoft.com" address: Directory Sync (AAD DirSync) with Azure Active Directory

If you log onto a cloud service such as Power BI with an ".onmicrosoft.com" account, and this is different than your regular work ID (i.e., it's not single sign-on), you can still facilitate usage of the SSAS Connector but it takes additional configuration.  Use of the SSAS Connector with two different logins requires the installation of Directory Sync (DirSync) on your domain controller to handle syncing up your Active Directory (AD) with Azure Active Directory (AAD). The DirSync process permits a cloud service (like Power BI) to communicate with Active Directory and thus reach Analysis Services on your on-premises server. Of course, this needs to be set up for the same domain that the SSAS database resides in.

Why do you want to bother syncing? It's convenient for users to minimize the # of IDs and PWs for them, definitely true, but it's also far more secure. If an employee leaves the company, the ID only needs to be disabled in one place then it'll propagate. That minimizes the risk that a former employee may still have access after they're no longer employed.

More information:  Why You Might Need DirSync to Connect to an On-Premises Analysis Services Server 

             and Power BI Analysis Services Connector Deep Dive

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview (you are here)

Part III: Monitoring the SSAS Connector in the Power BI Preview

Part IV:  Connecting to Analysis Services From Power BI Designer and Excel