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

• 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.
• Expand the gateway to see the underlying data sources
• Click a desired SSAS Multidimensional data source. Look at this to see how to manage an SSAS Multidimensional data source.
• Click “Users” tab
• Enter the users’ email addresses then click Add (you can add more than one users at the same time)

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