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))

On-premises Data Gateway

Managing SSAS Multidimensional Data Source from Power BI Service

Power BI Service Pro Account

SQL Server 2012 Analysis Services SP1 CU4 or later (Enterprise or Business Intelligence editions only)

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”

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.”

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

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

Click “Adventure Works” cube and click OK

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.”

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

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.

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”



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 “On-premises Data 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”

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”.

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

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



Troubleshooting

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

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”

Check if the Enterprise Gateway configured correctly

Login to your Power BI Service Account

Click “Manage Gateways” form Settings menu

Your user account should be gateway administrator

You should see the gateway you created before. Have a look at here to see how to use On-premises Data Gateway.

Click “Test all connections”

You should see the status as Online

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

In this case you need to open On-premises Data 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

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

Like this: Like Loading...