Connect Live Power BI to SSAS Multidimensional

Connect liv Power BI SSAS Multidimensional

In one of my previous posts I explained how to use Power BI on top of your SSAS Multidimensional using Data Import Scenario. You can also find definitive explanation about “Managing Analysis Services Multidimensional Model” here. In this post I show you how to connect live your SSAS Multidimensional model with Power BI. “Connect Live SSAS Multidimensional” means making a Direct Connection from Power BI Desktop to your SSAS Multidimensional instance.

As it is a direct connection you’ll be able to see/use the following SSAS Multidimensional objects:

  • Cubes
  • Perspectives
  • Measure Groups
  • Measures
  • Dimensions
  • Dimension Attributes
  • Hierarchies including Parent Child

In this post you’ll learn:

  • How to connect live from Power BI Desktop to SSAS Multidimensional
  • Creating reports using SSAS objects like hierarchies
  • Publishing your reports from Power BI Desktop to Power BI Service

Requirements

To be able to successfully create and publish your reports using Power BI Desktop on top of SSAS Multidimensional you will require:

  • The latest version of Power BI Desktop (Current version is 2.31.4280.361 64-bit (January 2016))

Power BI Desktop Version

Connect Live Power BI Desktop to SSAS Multidimensional

Please remember that it is important to have the latest version of Power BI Desktop. The current version at the date of publishing this article is 2.31.4280.361 64-bit (January 2016).

  • Open Power BI Desktop
  • Click Get Data
  • Click “SQL Server Analysis Services Database”

Connect liv Power BI SSAS Multidimensional 01

  • Click Connect
  • Enter “Server” and “Database” names

Note: As I mentioned earlier you need to have SQL Server 2012 Analysis Services SP1 CU4 or later. So if you’re trying to connect to an older version of SQL Server you’ll get the following warning:

“The data source does not support a live connection. Exploring multidimensional models with a live connection requires SQL Server Analysis Services 2012 SP1 CU4 or later.”

Power BI Desktop SSAS Multidimensional 2008

  • Make sure “Connect live” option is selected then click OK

Connect liv Power BI SSAS Multidimensional 02

  • SSAS cubes and perspectives are available to select in the Navigator window

SSAS Perspectives Power BI

  • Click “Adventure Works” cube and click OK

Connect liv Power BI SSAS Multidimensional 03

Note: Remember that you need to have an Enterprise or Business Intelligence edition of SQL Server Analysis Services otherwise you’ll get the following error:

“Errors related to feature availability and configuration: The ‘Tabular View’ feature is not included in the ’64 Bit Standard’ edition of Analysis Services.”

Error Message: Couldn't load the model schema

  • You should see a list of “Measure Groups” and “Dimensions” in the “Fields” pane

Connect liv Power BI SSAS Multidimensional 04

Creating reports using SSAS objects like hierarchies

  • Expand “Internet Sales” measure group
  • Tick “Internet Sales Amount” measure
  • Expand “Sales Territory” dimension
  • Tick “Sales Territory” hierarchy

Note: It is really awesome that SSAS Hierarchies are supported in Power BI Desktop (when using Connect live scenario) so you should see that drill down is enabled in your column chart.

SSAS Hierarchy Power BI

  • Click the “Drill down” to enable drill down action
  • Now if you click on each column on the column chart, it will drill down to the next levels

Note: “Parent Child” hierarchies are also supported. To see how it works:

        • Add a Matrix into the report
        • Expand “Resellers Orders” measure group
        • Select “Resellers Orders Count” measure
        • Expand “Employee” dimension
        • Select “Employees”
        • Remove “Employee Level 06” from “Rows”

SSAS Parent Child Power BI

Publishing your reports from Power BI Desktop to Power BI Service

Now that we created a very simple report we can publish it to Power BI Service. I’d like to remind you again that you need to install and configure “Power BI Enterprise Gateway” on a machine in your network first. Then you need to “Managing SSAS Multidimensional Data Source from Power BI Service”. You also need to have a Power BI Pro Account.

  • Save the current Power BI Desktop report
  • Click “Publish” button form ribbon
  • Click “Sign in”

SSAS Multidimensional Power BI Publish

  • Enter your Power BI Service account
  • You’ll prompt to select your Microsoft account type immediately after entering your Power BI account

Note: It is important to click “Work or School account”.Power BI Microsoft Account Type

Otherwise you’ll get “Sorry, you don’t seem to have access to Power BI.” error.

Power BI Microsoft Account Type 01

      • Enter your password then click “Sign in”
      • You have successfully published your report into Power BI Service

SSAS Multidimensional Power BI Successful Publish

Troubleshooting

To make sure your report is published correctly login to your Power BI Service account. You should be able to open the report.

SSAS Multidimensional Power BI Service

If you cannot see the report and see the following message on top of the report then

  • you didn’t configure your Enterprise Gateway correctly
  • you didn’t grant access right to the user
  • or you published the reports to a wrong Power BI Account

Report could not access the data source because you do not have access to your data source via an Enterprise Gateway

SSAS Multidimensional Power BI Service Failed

Check if the Enterprise Gateway configured correctly

Power BI Gateway Management 02

  • If your connection to the gateway fails you’ll see a message like “The gateway is either offline or could not be reached”

Power BI Enterprise Gateway Management 01

  • In this case you need to open Power BI Enterprise Gateway and reconfigure it

Check if the user has access to the data source

  • Login to your Power BI Service Account
  • Click “Manage Gateways” form Settings menu

Power BI Gateway Management 01

Power BI Enterprise Gateway Management 03

  • Now open the report from Power BI Service and it should work

SSAS Multidimensional Power BI Service Report

Leave a Reply