In this article we will step through how to securely connect an on-premises Analysis Services Tabular mode instance for use as a data source in the Power BI Preview. You can alternatively use a virtual environment in Azure (IaaS). The process is similar.
Unlike the prior Data Management Gateway that copied data to Power BI sites, the new Analysis Services Connector issues DAX queries and returns results sets according to role-based permissions for rendering when you view and interact with dashboards and reports. 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 SQL Server Analysis Services 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.
Prerequisites
Currently this feature is only available for Power BI accounts created in the United States. 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. Note 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.
You might need to configure Azure AD DirSync. See the support article at http://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 top 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.
Double-click on the PowerBIASConnector.exe file or click Run on the downloaded file pop up 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 Setup completes, click Next to continue to log into Power BI.
Now, enter the credentials that you want to use for Power BI connectivity to your Analysis Services server. Keep in mind that if this password expires, you will need to update it in the connector for connectivity to remain functional.
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.
If the connection has been successfully configured, you should see “Success! Your Power BI Analysis Services Connector (Preview) is configured” shown.
You can now close the Analysis Services Connector window and give it a whirl from the Power BI Preview site.
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 list of available Analysis Services data sources will be displayed. 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.
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.
Using and Publishing Excel Power View Reports
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?
Additional Information and FAQs
For additional information and troubleshooting Analysis Services Tabular model connections in the Power BI Preview, check out the Power BI Preview Knowledgebase. Also don’t forget to vote for more direct connect data sources to be added to Power BI. You can do that in the User Voice section under the ? menu item.
