Tag: Power BI and SSAS

BISM Multidimensional, BISM Tabular, On-premises Data Gateway Enterprise Mode, Power BI, Power BI Desktop, SQL Server Analysis Services - SSAS

Power BI Enterprise Gateway, Everything You Need to Know

On-premises Data Gateway (aka Power BI Enterprise Gateway) is release a while ago (2 Dec. 2015), but, with the latest release on 22 Dec. 2015 Power BI Enterprise Gateway now supports live connections to both SQL Server Analysis Services Multidimensional and Tabular models as well as SAP HANA. In this post I’ll explain lots of important aspects of the Power BI Enterprise Gateway including installation,  configuration for different data sources including SSAS Multidimensional, Tabular and SQL Server Database and much more. If you need to have the lowest possible latency then you need DirectQuery/Explore Live feature on top of your on-premises data sources. The good news is that Power BI Enterprise Gateway now supports all following data sources:

  • SQL Server Database
  • SQL Server Analysis Services Multidimensional
  • SQL Server Analysis Services Tabular
  • SAP HANA

    In this article you’ll learn how to install and configure Power BI Enterprise Gate Way, how to manage different live data sources, how to create reports on top of live data sources and more.

  • Note 1: If you want to use DirectQuery to connect to your on-prem SQL Server Database OR Explore Live your SQL Server Analysis Services Tabular model then you might not need to install and use the Power BI Enterprise Gateway. In those cases you can install Power BI Personal Gateway to connect to an instance of SQL Server OR install Power BI Analysis Services Connector to connect to your on-prem instance of SQL Server Analysis Services Tabular model rather than installing the Power BI Enterprise Gateway. But, bear in mind that selecting the best gateway is really depending on your use cases, your data sources and the environment you’re working on.

  • Note 2: The Power BI Enterprise Gateway and Power BI Personal Gateway CAN be installed on the same machine.

    Downloading and Installing Power BI Enterprise Gateway

    You can download the gateway from the dedicated download page for On-premises Data Gateway on Microsoft website or after logging into Power BI Service with your account, click on “Data  Gateway” from the download menu:
    downloading on-premises data gateway from Power BI service

    OR you can go straight to the gateway page then download the Power BI Gateway – Enterprise (Preview):

    Direct Link to Download Power BI Enterprise Gateway

Continue reading “Power BI Enterprise Gateway, Everything You Need to Know” →

On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power BI Web App, Power Query

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

Continue reading “Power BI and SSAS Multidimensional” →