Update: If you are here to learn how to browse your SSAS Multidimensional model in Power BI please refer to this article. The current article shows you how to refresh data on Power BI service on top of a SSAS Multidimensional instance based on data import scenario.

It’s been a while that lots of us are waiting for seeing improvements on Power BI and SSAS Multidimensional. The good news is that Microsoft released a new version of Power BI Personal Gateway last week on 3 Sep 2015. One of the new features added to this release is that we can now refresh an on-prem SSAS Multidimensional model (data import scenario) after we published it to Power BI website. But, what data import scenario means? That means we cannot create mashups with data we already have in an existing SSAS Multidimensional database/cube through the SQL Server Analysis Services connector which is available on Power BI website. So we need to connect to a SSAS multidimensional instance through Power BI Desktop and load the cube’s data into the Power BI model. Indeed we will create a relational model on top a multidimensional model from SSAS.

Then we can create reports and publish them to Power BI website and finally we’ll be able to schedule data refresh on the Power BI website.

We can also connect to a SSAS Multidimensional instance through Power Pivot AND/OR Power Query from Excel then load the Excel file into Power BI website.

Note: We can do the same through Power Query, but, we won’t able to setup a data refresh schedule on Power BI website if we didn’t load

It’s just awesome isn’t it?

In this post I show you how to implement all the data import scenarios using Power BI Desktop, Power Pivot and Power Query from Excel.

First of all you need to download the gateway from here. Then you need to uninstall the existing version of Power BI Personal Gateway from your machine and install the new version. The whole gateway installation and process of refreshing an on-prem SSAS database is pretty much the same as what I explained in this post so I leave the installation part to you.  However, I explain the data refresh part again.

SSAS Multidimensional Data Import Scenario Through Power BI Desktop:

Get Data

• Open Power BI Desktop
• Click Get Data
• Select “SQL Server Analysis Services Database” from the list and click “Connect”

• Enter the SQL Server Analysis Services instance name
• The database name is optional, but, I put “AdventureWorksDW2012”
• Click “Select items and get data from Multidimensional or Tabular model”
• As you can see you can also put your MDX or DAX custom queries, but, we leave it blank in our sample
• Click OK

• Expand the cubes and underlying measure groups  then tick the measures you need. In this sample I selected “Internet Sales Amount”

• Scroll down and tick the desired dimensions. I selected “Date” dimension
• As you can see you can expand a dimension and select the desired members or hierarchies
• Click Load

• After your model loaded you can see a table with date dimension members and “Internet Sales Amount” measure on it. As you might noticed there is no hierarchies in the table as hierarchies are not supported in Power BI.

Creating/Publishing a Simple Report

On the report view tick “Internet Sales Amount” and “Date.Calendar Year” to create a simple column chart.

Now click “Publish” from the ribbon to publish the report to Power BI Website.

Schedule Data Refresh Setup

• Open your internet browser and browse Power BI website
• Sign in to your account
• Find the newly published dataset from Datasets pane
• Click on the ellipsis button then click “Schedule Refresh”

• Expand “Gateway Status” to make sure the personal gateway is online

• Expand “Data Source Credentials” then click “Edit Credentials” and make sure Authentication Method is set to “Windows” then click “Sign In”

• Expand “Schedule Refresh” and switch the “Keep up-to-date- option to Yes. Then set desired refresh frequency, time zone and time. Tick “Send refresh failure notification email to me” if you want to get informed when a scheduled data refresh fails. Click Apply.

All done!

SSAS Multidimensional Data Import Scenario Through Power Pivot:

• Open Microsoft Excel then go to Power Pivot
• On the ribbon click on “From Data” from “Get External Data” section
• Click “From Analysis Services or Power Pivot

• Enter the SSAS server name then select the database from dropdown list then click Next

• Type a friendly name for the query and click “Design” button

• Select a desired measure group
• Expand measures, then drag and drop a desired measure to the grid area

• Scroll down and expand a desired dimension
• Drag and drop a member or hierarchy to the grid

• Click OK then click Finish

• After data import is completed click Close

• Now that we imported the data we should save the Excel file.

• Login to your Power BI website
• Click on Get Data
• Click on Get Files

• Click Local File and import the Excel file

• Now you should see the Excel dataset in the Datasets list

• Click on the ellipsis then click Schedule Refresh

• Now you can setup schedule refresh as desired

SSAS Multidimensional Data Import Scenario Through Power Query:

• Open Excel then from Power Query get external data From Database
• Click “From Analysis Services Database”

• Enter the SSAS server name then click OK

• From the Navigator expand database, expand a desired cube, expand measure group then select a desired measure

• Scroll down and select a desired dimension, dimension member or hierarchy
• IMPORTANT: Click on the Load button option and select “Load To”

• Tick “Add this data to the Data Model”. This is very important to load data into the model otherwise you WON’T be able to setup a Schedule Refresh on Power BI website for an Excel dataset which doesn’t have any data in the data model.

• After your data loaded save the Excel file

• Now you need to import the Excel file into your Power BI website and setup a schedule refresh as I explained previously

All done!