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:
  

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

  

• 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.
  • Click “Sign in to Power BI” and sign in with your Power BI account

  

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

  

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

  

  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”

  

  • You should see “My Enterprise Gateway” now

  

  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

  

  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

  

  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”

  

  * Learn more about privacy levels here.

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

  

  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”

  

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

  

  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:

  

  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)

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”

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

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

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

• 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

• Select a cube then click OK

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

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

• 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 () to enable the drill down action. Now you can drill down to the lower levels of the chart by clicking on each column:

• 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

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

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

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 OK
• Select a desired perspective and click OK
• Expand “Reseller Sales”
• Tick Sales Amount to create a column chart
• Expand “Geography” and tick Country Region Name
• Now click the “Publish” button to publish the report to the Power BI Service
• Click the hyperlink to open the report in your browser

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

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

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

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

• Click DirectQuey then OK

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

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

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

• • 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 () from the “Dashboards” section
  • Type a name for the 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 () on the top right of each visualisation then select the new dashboard then click “Pin”

  

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

  

  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”

  

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

  

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