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

  • After you downloaded EnterpriseGatewayInstaller.exe file double click it to install the gateway.Note: To reduce network latency it’s better to install the gateway on a server that hosts the data sources you are connecting to.Installing Power BI Enterprise Gateway
    • Click “Sign in to Power BI” and sign in with your Power BI account

    Installing Power BI Enterprise Gateway 02

    • Enter a name for the gateway, then enter and confirm the recovery. Make sure you record the recovery key in a safe place as you’ll need it for restoring the gateway then click “Configure”

    Installing Power BI Enterprise Gateway 03

    • The installation is completed now. You can either click “Close and Add data sources” or “Close”

    Installing Power BI Enterprise Gateway 04

    In the next section I explain how to manage Power BI Enterprise Gateway Data Sources.

    Managing Power BI Enterprise Gateway Data Sources

    So far so good. We have installed the Power BI Enterprise Gateway and we’re ready to manage our data sources. To do so follow the below steps:

    • Login to Power BI Service. This account should be the account you used when you installed the Power BI Enterprise Gateway.
    • Click on the Settings (gear icon) on the top right of the page and click “Manage gateways”

    Managing Gateways

    • You should see “My Enterprise Gateway” now

    Managing Power BI Enterprise Gateways

    Setup Gateway Administrators

    • From the Gateways page click a desired gateway
    • Click “Administrators” tab
    • Enter the users’ email addresses. You can add multiple emails at the same time
    • Click Add

    image_thumb11[1]

    Managing Power BI Enterprise Gateway Data Sources

    • Click “ADD DATA SOURCE”
    • Type a name for the data source
    • Select a desired data source type from the list

    Managing Power BI Enterprise Gateways 01

    Depending on the data source you different configurations will appear in this page.

    Managing a SQL Server data source

    • Select “SQL Server” from the list
    • Enter the Server Name and Database name
    • Select authentication method
    • Enter your Windows credentials (I select Windows authentication, but, depending on your case you might select Basic authentication here. In that case you need to enter a SQL user and password like SA)
    • Expand “Advanced Settings” and a privacy level then click “Apply”

    Managing Power BI Enterprise Gateways 02

    * Learn more about privacy levels here.

    You should see something like the screenshot below if your connection is successful

    Managing Power BI Enterprise Gateways 03

    Managing an Analysis Services Multidimensional data source

    • From the Gateways page click “ADD DATA SOURCE” again
    • This time select “Analysis Services” from the data source type list
    • Enter the server name and data base name
    • Enter Username and Password then click “Add”

    Managing Power BI Enterprise Gateways 04

    You should see something like below screenshot if your connection to the SSAS Multidimensional instance is successful:

    Managing Power BI Enterprise Gateways 05

    Managing an Analysis Services Tabular data source

    If you have an instance of SSAS Tabular model and you’re willing to add it to the enterprise gateway you need to do exactly the same thing as you did to add a new SSAS Multidimensional data source as above, but, you just enter the SSAS Tabular Model instance name for the server name. Then you should see something like the screenshot below:

    Managing Power BI Enterprise Gateways 06

    Note: If the data source you are adding is hosted by the same server as you installed the Power BI Enterprise Gateway then using “.Instance_Name”  rather than “CUMPUTER_NAMEINTANCE_NAME” for the server name is legitimate for SQL Server and both SSAS Multidimensional and Tabular models.

Managing Data Source Users

Managing data source users is the same for SQL Server Database, SQL Server Analysis Services Multidimensional (SSAS Multidimensional) and SQL Server Analysis Services Tabular (SSAS Tabular). The users you add will be able to publish reports that use the data sources. So it is trivial that the users should have read access to the databases (SQL database, SSAS cubes or SSAS tabular models).

  • Fro the Gateways page expand the gateway to see the underlying data sources
  • Click a desired data source
  • Click “Users” tab
  • Enter the users’ email addresses then click Add (you can add more than one users at the same time)

image_thumb16_thumb

Browsing (Live Connection) SSAS Multidimensional from Power BI Desktop

Note: Connect live to SSAS is now released and it is not in preview anymore. So some parts of this section might be out-dated. You can find more about Connect Live to SSAS Multidimensional here.

Previously we weren’t able to connect directly to an instance of SSAS Multidimensional. But, using the new Power BI Enterprise Gateway we can browse the cubes directly from Power BI Desktop. In this sanction I show you how to do the job.

  • Open Power BI Desktop. The Power BI Desktop should be installed on a machine which is in the same network as you installed the Power BI Enterprise Gateway.
  • Click “Options” from File-> “Options and Settings”

Power BI Explore Live Settings

  • Click “Preview Features” then tick “Explore live with SQL Server Analysis Services multidimensional models” then click OK

Power BI Explore Live Settings 01

  • You’ll get the below message, click OK, then close the Power BI Desktop

Power BI Explore Live Settings 02

  • Open Power BI Desktop again
  • Select “Analysis Services” from Get Data

Power BI Explore Live Settings 03

  • Enter the “Server” and “Database” names as you previously used in the Power BI Enterprise Gateway configuration
  • Make sure “Explore live” is selected
  • Click OK

Power BI Explore Live Settings 04

  • Select a cube then click OK

Power BI Explore Live Settings 05

  • Voila… We have all dimensions and measure groups on the “Fields” pane

Power BI Explore Live Settings 06

  • Expand “Internet Sales”
  • Tick “Internet Sales Amount” to create a new column chart
  • Expand “Sales Territory” dimension and tick “Sales Territory” hierarchy. (YES, hierarchies are now supported in Power BI Desktop)

Power BI Explore Live Settings 07

  • As you can see the drill down action is automatically enabled on the column chart which means you can easily drill down to the lower levels of the chart. To do so simply click the “Drill Down” button (image) to enable the drill down action. Now you can drill down to the lower levels of the chart by clicking on each column:

Power BI Explore Live Settings 08

Power BI Explore Live Settings 09

  • Click “Publish” button from the ribbon to publish the report to the Power BI Service
  • If you get the sign in message then make sure you enter to the same Power BI account as you setup the Power BI Enterprise Gateway

Power BI Publish Reports

Power BI Publish Reports 01

  • You can click the hyperlink to navigate to the report on the Power BI Service

Power BI Service Report

  • If you click on the ellipsis button on the right side of the dataset you already published you’ll see that the “This dataset connects to a SQL Server Analysis Services Tabular database & is always up to date. You don’t have to schedule a refresh on this dataset.” message.Power BI Service Dataset

Browsing (Live Connection) SSAS Tabular from Power BI Desktop

Live connection to an instance of SQL Server Analysis Services Tabular is not a new feature. Previously we were able to connect to a live dataset using “Power BI Analysis Services Connector”. The Analysis Services Connector is NOT compatible with the “Power BI Personal Gateway” so needed to install it on a separate machine. While with the new “Power BI Enterprise Gateway” we have the ability to live connect to all supported data sources including SSAS Multidimensional, SSAS Tabular and SQL Server database (and of course SAP HANA which is not a part of this article).

  • Open a new instance of Power BI Desktop (you cannot add different live connections in just one Power BI Desktop file as the data is NOT loading into the model. So you need to open a brand new instance.)
  • Click Get Data
  • Select “Analysis Services”
  • Enter the SSAS Tabular instance name
  • Enter the database name (this is optional)
  • Make sure the “Explore live” is selected then click OKPower BI Explore Live Settings 10
  • Select a desired perspective and click OK Power BI Explore Live Settings 11
  • Expand “Reseller Sales”
  • Tick Sales Amount to create a column chart
  • Expand “Geography” and tick Country Region NamePower BI Explore Live Settings 12
  • Now click the “Publish” button to publish the report to the Power BI ServicePower BI Publish Reports 02
  • Click the hyperlink to open the report in your browserPower BI Service Report 01

DirectQuery  to On-prem SQL Server Database from Power BI Desktop

We are now able to DirectQuery to a SQL Server Database from Power BI Desktop on top of Power BI Enterprise Gateway which means we don’t need to setup schedule refresh on the Power BI Service (Power BI website) anymore. With DirectQuery feature our Power BI reports and dashboards will be always up-to-date as the dataset is using a live connection to  our SQL Server database.

  • Open Power BI Desktop
  • Click “SQL Server” from Get Data

Power BI DirectQuery

  • Enter server name
  • Enter database name (database name is optional) and click OK

Power BI DirectQuery 02

  • Click “Connect” (If you’re using windows authentication then you’re good to go, but, if you want to use SQL Server authentication then you need to click Database and enter your SQL credentials)

Power BI DirectQuery 03

  • Select DimDate and FactInternetSales from the list then click “Load”

Power BI DirectQuery 04

  • Click DirectQuey then OK

Power BI DirectQuery 05

  • Expand FactInternetSales and tick SalesAmount
  • Expand DimDate and tick CalendarYear

Power BI DirectQuery 06

  • Click “Publish” button from the ribbon to publish the report into the Power BI Service

Power BI Publish Reports 03

  • Click on the hyperlink to navigate to the report from the Power BI Service

Power BI Service Report 02

    • From the navigation pane from datasets click on the dataset ellipsis button. This is what we expect to see: “This dataset connects to a source with DirectQuery, which is always up-to-date. You don’t have to schedule a refresh on this dataset.

    Putting all Live Reports Together in a Dashboard

    Now I want to create a dashboard and put all the charts together in the dashboard.

    Note: The dashboards are available only in Power BI Service (website).

    • From the navigation pane click the plus sign (image) from the “Dashboards” section
    • Type a name for the dashboard

    Power BI Dashboard

    • Now click on each of the reports we created in previous sections and pin the visualisation to the new dashboard
    • To do so you just need to click on the pin icon (image) on the top right of each visualisation then select the new dashboard then click “Pin”

    Power BI Dashboard 02

    • After pinning all the visualisations to the dashboard click the dashboard to open it

    Power BI Dashboard 03

    Enabling Q&A for a Dashboard

    Now we’ve created a dashboard that contains some visualisations on top live connections to the data sources. This means the lowest possible latency and the visualisations always indicate up-to-date information. But, what about Q&A? Can we enable Q&A for the dashboard? The answer is a NO for now. However, to see how to generally enable Q&A follow the below steps:

    • Open the dashboard menu by clicking on the ellipsis button of the dashboard then click “Settings”

    Power BI Dashboard 04

    • Tick “Show Q&A search box on this dashboard” box

    Power BI Dashboard 05

    • As you can in the above screenshot at the moment Q&A is NOT available for live connected datasets

    So far you have learnt:

    • How to install Power BI Enterprise Gateway
    • How to manage several data sources supported by Power BI Enterprise Gateway
    • Creating simple reports on top of the live data sources
    • Putting all visualisations together in a single dashboard

    In the next article I’ll show you Power BI Enterprise Gateway security and authentication settings including how you can manage the users and administrators and more.

One thought on “Power BI Enterprise Gateway, Everything You Need to Know

  1. good article, but you said in the intro you’d cover “how to create reports on top of live data sources” I don’t see that here. I can’t seem to find that anywhere! Any thoughts?

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.