Power BI Analysis Services Connector Deep Dive
Update: Power BI Analysis Services Connector is now GA. You can download the latest version of the Connector clicking the download icon on the Power BI service. If you already installed a preview version, you can upgrade to the latest version without uninstalling the preview version. Also, you do not need to reconfigure the Connector.
One of the most exciting features of the new Power BI Preview is the ability to connect live to Analysis Services tabular models that live on-premise though the SSAS connector. In this article we will show you step by step how to securely connect an on-premises Analysis Services Tabular mode instance to use it as a data source in the Power BI Preview. You can alternatively use a virtual environment in Azure (IaaS). The process is similar.
Unlike caching the data in the Power BI service, with the new Analysis Services Connector you can connect from Power BI to your on-premises Analysis Services Tabular model & build dashboards, reports. The DAX queries issued by Power BI are sent to the Analysis Services Connector which in turn sends it to the tabular model. The DAX query is executed according to the role based permissions defined in the model. The connector will interactively query the on-premises cube to fetch the data using your Active Directory credentials. With this new option, you can continue to manage and secure your data on-premises, removing the need to have data reside in the cloud.
Power BI customers can now roll out self-service BI with enhanced user level data security Analysis Services provides. The Power BI user name gets passed from your Power BI site through to your on-premises Analysis Services. Analysis Services then resolves the user name to an authorized user via Azure Active Directory and applies appropriate role-based security to restrict data access.
Secure data transfer between Power BI and the Analysis Services Connector happens through a Service Bus which uses a secure SSL channel. Service Bus in turn uses a shared secret mechanism to create a secure channel between the Power BI site and your on-premises data source through an outbound port. It does not require an inbound port to be opened on the on-premises firewall.
In this post
Setting Up the Analysis Services Connector
Things to be aware of when configuring the Analysis Services Connector
Live Connecting to Analysis Service from Power BI Preview
Using an existing Excel Power View workbook
Prerequisites
Currently this feature is only available Power BI accounts created in the U.S. Additional locations around the world will be added in the future.
To configure the new Analysis Services Connector, you will need Analysis Services server administrator permissions. Once configured, users will need to have at least Read permissions.
Note .NET Framework 4.5.1 or higher also needs to be installed. The current version of Power BI Data Management Gateway cannot be installed on the same machine as the new Analysis Services Connector. If you already have the Data Management Gateway installed, uninstall it before installing the Analysis Services Connector, or install the new connector on different computer.
We currently only support connecting to Tabular models.
You might need to configure Azure Active Directory DirSync. See the support article at https://support.powerbi.com/knowledgebase/articles/505323-why-you-need-dirsync-to-connect-to-on-premises-ana. For organizations that use Office 365, this is often already set up. If you do not have a federated domain to Azure, Greg Galloway shared a tip on setting up a demo Azure domain that you can refer to for setting up a demo or test environment.
Setting Up the Analysis Services Connector
Now the fun begins! Log into your Power BI Preview account and download the latest Analysis Services Connector from the top right DOWNLOAD menu under the gear icon.
You can also download the Analysis Services Connector from here – https://www.microsoft.com/en-us/download/details.aspx?id=45333
Double-click on the PowerBIASConnector.exe file or click Run on the downloaded file pop up me to start running the installation wizard. After installing, click Launch to open the Power BI Analysis Services Connector Wizard. The wizard will guide you through configuring a connector to your server instance.
Once Set Up completes, click next to continue to log into Power BI.
Next you will enter the Analysis Services Server name & the credentials that you want to use for Power BI connectivity to your Analysis Services server.
Enter the Windows user name in the form of username@company.com or domain/username
Keep in mind:
- You need to be an admin on the Analysis Services Server to wish to connect to
- If this password expires, you will need to update it in the connector for connectivity to remain functional.
Next: enter the friendly name – the name of the connection that shows up in the Power BI site. A nice friendly name makes it easier for users to find the server they want to connect to.
Description – provide a nice description for the server
Friendly error message – when Power BI cannot connect to the Connector due to the Connector going offline or not accessible, this error message will be shown to the users in Power BI
If the connection has been successfully configured, you should see “Success! Your Power BI Analysis Services Connector (Preview) is configured” shown.
Also the Analysis Services service and Analysis Services Connector service needs to remain running. The Analysis Services Connector service has the label Data Management Gateway Host Services in your Services panel.
You can now close the Analysis Services Connector window and give it a whirl from the Power BI Preview site.
Things to be aware of when configuring the Analysis Services Connector
- The Analysis Services server must be domain joined. We rely on Active Directory to translate the Power BI username to the on-premises user name.
- The Analysis Services Server and the Analysis Services Connector should be joined to the same domain
- It is not necessary to install the Analysis Services Connector on the same computer as the Analysis Services Server, but from a performance standpoint it is good to do so. This will avoid an extra hop from the Connector to the Analysis Services Server.
Live Connecting to Analysis Service from Power BI Preview
In the Power BI Preview site, click Get Data and navigate to the Analysis Services connection type. Click on the Connect button.
A lists of available Analysis Services data sources will be displayed. This list shows the friendly name & description you defined in the Connector.
Choose your newly configured server and the available cubes will be shown. Click on the cube you want to use.
Your Analysis Services connection should now be displayed under the Datasets list. To use it create reports, click on the three dots (…) then click EXPLORE on the shown menu. Or click on the name of the dataset to get into report authoring view.
Now you should see the report authoring user interface for your self-service BI community to create reports from your blessed Analysis Services single version of the truth. Simply drag and drop dimensions and measures onto the report canvas. To add a chart to your dashboard, simply choose the Pin icon next to the visual.
Using an existing Excel Power View workbook
You can also publish Excel 2013 Power View reports to the Power BI Preview site that use the exact same Analysis Services Tabular connection and they just work. You do need to make sure that the Analysis Services connection string in your Excel file matches the Analysis Services connection that you published to your Power BI Preview site when you configured direct connectivity.
To set up a connection in Excel 2013 to an Analysis Services Tabular model, use the Data tab in Excel. Once connected, then create your Power View reports.
To publish your Excel 2013 Power View report to the Power BI Preview site, you will upload the Excel 2013 file. On the Power BI Preview site, click on Get Data and then choose the option to upload an Excel file.
After your Excel file is uploaded, the Power BI site service will automatically match your defined Analysis Services connection embedded in the Excel 2013 Power View report with the already published Analysis Services data source connection on the Power BI Preview site for live interactive querying. How cool is that?
So how does this all work?
The whole story relies on two key things:
- The EffectiveUserName connection string property supported in Analysis Services Server
- A domain joined Analysis Services Server can get from Active Directory the on-premises user name equivalent of the Power BI user name in the cloud
- When user interacts with a report connected to on-premises Analysis Services server, a DAX query is generated & is sent to the connector along with the effectiveusername of the user.
A quick inspection of the SQL profiler show this:
<RestrictionList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<CATALOG_NAME>AdventureWorks_prod</CATALOG_NAME>
<VERSION>2.0</VERSION>
</RestrictionList>
<PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<Catalog>AdventureWorks_prod</Catalog>
<Cube>InternetSales</Cube>
<EffectiveUserName>anna@contoso.com</EffectiveUserName>
<SspropInitAppName>PowerBI</SspropInitAppName>
<LocaleIdentifier>1033</LocaleIdentifier>
<ClientProcessID>3408</ClientProcessID>
<Content>SchemaData</Content>
<Format>Tabular</Format>
<DbpropMsmdActivityID>381f4fdf-48cf-eb13-a5bd-f585cd05dc5d</DbpropMsmdActivityID>
<DbpropMsmdRequestID>ed28257d-b516-427e-a299-be5d60f14427</DbpropMsmdRequestID>
</PropertyList>
Notice the EffectiveUserName property?
- The Connector sends the DAX query along with effectiveusername property to the Analysis Services Server.
- The Analysis Services Server queries Active Directory (AD) to get the on-premises equivalent of ‘anna@contoso.com’
- Since anna@contoso.com is also the on-premises domain username AD can resolve the email ‘anna@contoso.com’ to ‘<domain/username>’ and returns this to Analysis Services.
- Analysis Services then executes the DAX query with user set as ‘<domain/username>’
- The result set of the query is returned to Power BI via the Connector
Additional FAQs
1. What if I am using .onmicrosoft.com email address?
You will need to configure Azure Active Directory DirSync. See the support article at https://support.powerbi.com/knowledgebase/articles/505323-why-you-need-dirsync-to-connect-to-on-premises-ana. For groups that use Office 365, this is often already set up. If you do not have a federated domain to Azure, Greg Galloway shared a tip on setting up a demo Azure domain that you can refer to for setting up a demo or test environment.
2. What if the Connector and the Analysis Services Server are not on the same domain?
We cannot guarantee this will work. It all depends on the trust relationship between the two domains. If the two different domains are in a trusted system model, then the Connector can connect to the Analysis Services Server and the effective user name can be resolved. If not, either the connection or the effective user name resolution may fail.
3. How do I remove the connector from showing up under ‘SQL Server Analysis Services Server’ in the Get Data list?
Simply uninstall the connector & this will remove the entry from Power BI
4. My reports have suddenly started failing & connector is not working too. What should I do?
Most common reason for this is the invalid password in the Connector. When the password of the Windows account used to configure the Connector expires, the Connector will stop to function. To update the password, launch the Connector, click on the Edit option & update the password.
An easy way to diagnose the Connector issues is to look in the Event Viewer for errors or warnings:
5. When I share with another user my dashboard connected to on-premises Analysis Services Server, the recipient continues to see the same data as I see. Why isn’t the role based permission applied here?
This is the current behavior in Power BI. Sharing means “share your own data”. Having said this, we are working on changing this experience in the case of on premises SSAS.
6. I am using my work email address, but it still doesn’t work
OR
I see this below error in the event viewer, what does it mean?
This means login using EffectiveUserName is not working. This will happen for one of the following reasons:
- You are using a onmicrosoft.com email & you don’t have AD synced with AAD via Dir Sync
- You are using a work email address but the username returned by AAD does not match the username AD.
- You are using a work email address but the domain names in AAD & AD don’t match
You can test this locally from the machine that the connector is running on:
- Using SSMS: In SSMS for the Analysis Services database set AdditionalConnectionProperties (set EffectiveUserName=<your work email address used in Power BI>). Then execute a DAX query. If this does not work, the connector is not going to work either.
- Using whoami/UPN: In command prompt, type ‘whoami /upn’ (without quotes). If the result does not match with the email address used with Power BI account, the connector is not going to work
There are many reasons why this does not work: multiple domains, asymmetric trust-relationships between domains, etc. In these cases, if the SSAS server is not able to resolve the username of the cloud identity, the connector will not work.
7. How do I keep my tiles connected to my on-premises AS server up-to-date?
Well, you don’t have to! We do it for you. We refresh these tiles every 10 minutes. You do not have to worry about tile refreshes.
- Preview what’s next for Power BI
- Watch our Power BI SSAS Connector Webinar on-demand
- Follow @MSPowerBI on Twitter
- Sign up for the Power BI Newsletter
