Power BI and SSAS Multidimensional

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”

Power BI and SSAS Multidimensional 01

  • 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

Power BI and SSAS Multidimensional 02

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

Power BI and SSAS Multidimensional 03

  • 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

Power BI and SSAS Multidimensional 04

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

Power BI and SSAS Multidimensional 05

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

Power BI and SSAS Multidimensional 06

Power BI and SSAS Multidimensional 07

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”

Power BI and SSAS Multidimensional 08

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

Power BI and SSAS Multidimensional 09

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

Power BI and SSAS Multidimensional 10

Power BI and SSAS Multidimensional 11

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

Power BI and SSAS Multidimensional 12

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

Power BI and SSAS Multidimensional 13

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

Power BI and SSAS Multidimensional 14

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

Power BI and SSAS Multidimensional 15

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

Power BI and SSAS Multidimensional 16

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

Power BI and SSAS Multidimensional 17

  • Click OK then click Finish

Power BI and SSAS Multidimensional 18

  • After data import is completed click Close

Power BI and SSAS Multidimensional 19

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

Power BI and SSAS Multidimensional 20

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

Power BI and SSAS Multidimensional 21

  • Click Local File and import the Excel file

Power BI and SSAS Multidimensional 22

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

Power BI and SSAS Multidimensional 23

  • Click on the ellipsis then click Schedule Refresh

Power BI and SSAS Multidimensional 24

  • Now you can setup schedule refresh as desired

Power BI and SSAS Multidimensional 25

SSAS Multidimensional Data Import Scenario Through Power Query:

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

Power BI and SSAS Multidimensional 26

  • Enter the SSAS server name then click OK

Power BI and SSAS Multidimensional 27

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

Power BI and SSAS Multidimensional 28

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

Power BI and SSAS Multidimensional 29

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

Power BI and SSAS Multidimensional 30

  • After your data loaded save the Excel file

Power BI and SSAS Multidimensional 31

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

Power BI and SSAS Multidimensional 32

All done!

19 thoughts on “Power BI and SSAS Multidimensional”

  1. Great, this is just what I’ve been looking for. We’re on Multidimensional on premise so have been struggling to get power bi progressed. Thanks for posting!

  2. Hi !

    I tried to replicate your first scenario but the refresh fails with the following message:

    [Expression.Error] The import AnalysisServices.Database matches no exports. Did you miss a module reference?

    Any idea what I might do wrong ?

    1. Hi Paul.

      Did you install the latest version of Power BI Personal Gateway?
      Remember, the previous version doesn’t support SSAS Multidimensional refresh.
      If you already installed the latest gateway then you need to check the data source credentials by clicking “Edit Credentials” from “Schedule Refresh” configuration.
      Make sure you’re using “Windows Authentication”.
      Hope you don’t have any more problems.

      Cheers

  3. I think you forgot to talk about one thing though: the creation of the dashboard. If you want to share the report or simply acces it from a device like an iPad/iPhone you need to create a dashboard.

    And unfortunately I haven’t seen a way so far to create a dashboard with Reports based on SSAS cubes. If it’s the case, then all of this is useless unfortunately… You have a report but you cannot share it or access it from mobile devices.

    1. Hi Alexandre,

      My intention was to explain all possible Power BI data import scenarios on top of SSAS Multidimentional.
      About creating dashboards, if you already created your reports on Power BI Desktop then there are only a few steps you need to take to create your dashboards.
      First of all you need to publish your reports to Power BI website and then you create and share your dashboards online.
      I encourage you to have a look at this article, I think you’ll like it:

      http://biinsight.com/data-visualisation-with-power-bi-desktop/

      Cheers

  4. Hi, nice article!

    for MOLAP Import Data scenario, do you know if data refresh also works if the spreadsheet with data model is stored in OneDrive for Business? if yes, then I can build Excel Online reports off the PowerPivot data model and still manage data refresh via Power BI website.

    thanks much,
    JQ

    1. Hi JQ,

      Well, it depends.
      As data import scenario literally imports your data into your Power BI storage on the cloud when we are talking about data refresh we are actually talking about refreshing the imported model on the cloud from the source storage.
      Depending on the data sources you used in your Excel file the was data refresh works is different regardless of the location you stored your Excel file. For instance, if you’re using one of the data sources supported in Power BI Personal Gateway like MySQL, SQL Server and so on then you can refresh the model in the cloud without needing to refresh the original Excel file. I encourage you to have a look at here and here.
      But, Power BI treats Excel files stored in OneDrive differently.
      SO that you can just import Excel data into Power BI or you can bring the whole Excel workbook into Power BI and manage and view data, charts, pivot tables etc.
      You can also keep your workbooks uptodate with schedule refresh.
      I already have “Power BI and OneDrive Business” in my list and I’ll post an article about this in near future.
      So you’re most welcome to subscribe and get the newest update of my website.

      Cheers

    1. Hi Paddu,

      Welcome to biinsight.
      If you mean “does Power BI Desktop supports SSAS 2008R2” then the answer is yes.
      You can connect to and import data from SSAS 2008 R2 into Power BI Desktop model as I explained in this article.
      But, bear in mind that Analysis Services Connector is a gateway which allows you to directly connect to your on-prem Analysis Services Tabular Model instance via Power BI Desktop while Power BI Desktop itself is a report authoring tool indeed. So they are two different things.

      Cheers

  5. Hey,

    When I connect to my SQL Server 2008 SSAS services , the option to “load in to data model” is greyed out…

    Per the below link it says “… it seems that you import data from Analysis Services database. However, this option is not available for Analysis Services database. So in you want to made this option, you can import data from relational database.”
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fcf163c5-b7df-40ca-9f49-720b90e49894/powerview-add-this-to-the-data-model-checkbox-is-greyed-out?forum=sqlanalysisservices

    any other ideas?

    1. Hi Mohammad,

      Welcome to biinsight.
      Are you trying to connect from Power BI Desktop to SSAS MD 2008 or from Excel?
      I’m asking as the link you referred is about PowerView on Excel.
      Please elaborate .

      Cheers

      1. initially Power BI desktop to SSAS SQL SERVER 2008…but per the guide above i was trying to do it from Excel 2016 to SQL Server 2008 SSAS but I could not utilize the “load into data model” option as it was grayed out…

        1. You cannot load a multidimensional structure into a PowerPivot model. PowerPivot understands relational structure not multidimensional.
          However, you can import SSAS Multidimensional data using PowerPivot itself. In that case you’ll need to write MDX statements and load data.
          Then you can create relationships in PowerPivot.
          But, keep in mind that you are creating relational structure over a multidimensional structure.

          1. Thank you Soheil! Do you know of a way to get Power BI to directly connect to my Sql server 2008 SSAS service without upgrading to Sql server 2012?

          2. Power BI Desktop uses DAXMD to deal with SSAS Multidimensional.
            DAXMD is not available in SSAS 2008.
            You need to have SQL Server 2012 Analysis Services SP1 CU4 or later to be able to direct connect to an instance of SSAS MD.
            So if you want to connect to older versions then you need to import data into the model.

  6. Hi Soheil,

    Does Power BI support dynamic date ranges when using a live connection to a multidimensional cube through the gateway? I have MDX queries that calculate date ranges such as ‘This Year’ or ‘Last Month’ within my cube, but they don’t come across in my Power BI report.

    If Power BI does not support such ranges when connected to a multidimensional cube, have you seen any workarounds to achieve this functionality?

    Thanks for your help.

    1. Hi Jordi,

      Named sets are not available in Power BI SSAS Multidimensional Live connection.
      Unfortunately, you cannot modify the model in Direct Connect mode at all.
      So no, sadly I don’t have any workarounds for it.

      Cheers

Leave a Reply