Installing the On-Premises Data Gateway

The On-Premises Data Gateway, originally referred to as the Power BI Enterprise Gateway, is a Windows service that runs in on-premises environments. The sole purpose of the gateway is to support secure (encrypted) and efficient data transfer between On-Premises data sources and MS Azure services such as Power BI, PowerApps, MS Flow, and Azure Logic Apps via an outbound connection to the Azure Service Bus. Once installed, a gateway can be used to schedule data refreshes of imported Power BI datasets and to support Power BI reports and dashboards built with DirectQuery Power BI datasets and those which use Live Connections to SSAS (SQL Server Analysis Services) databases.

A single On-Premises Data Gateway can support the refresh and query activity for multiple data sources, and permission to use the gateway can be shared with multiple users. Currently the gateway supports all common data sources via scheduled imports (including ODBC connections) and many of the most common sources via Live Connection and DirectQuery.

Figure 15: User interface of the On-Premises Data Gateway (March 2017)

Getting ready

Hardware and network configuration

The hardware resources required by the gateway vary based on the type of connection (import versus live connection), the usage of the reports and dashboards in the Power BI service, and the proportion of data volume handled by the gateway versus the on-premises source systems. It's recommended to start with eight-core CPUs with an 8 GB of RAM server. This machine cannot be a domain controller, and to maintain availability of Power BI content, the gateway server should be always on and connected to the internet.

Based on an analysis of current and projected workloads, the gateway resources can be scaled up or down and optionally additional gateways can be installed on separate servers to distribute the overall Power BI refresh and query deployment workload. For example, one gateway server can be dedicated to scheduled refresh/import workloads, thus isolating this activity from a separate gateway server responsible for DirectQuery and Live Connection queries.

The gateway does not require inbound ports to be opened and defaults to the outbound port of TCP 443, 5671, 5672 and 9350 through 9354. The gateway can be forced to use HTTPS communication exclusively and avoid the use of IP addresses via both the UI and the configuration files directly but this may impair performance. For the default communication mode, it's recommended to whitelist the IP addresses in your data region in your firewall. This list is updated weekly and is available via the Microsoft Azure Datacenter IP list (http://bit.ly/2oeAQyd).

How to do it...

Installation of on-premises gateway

1. Download the latest Microsoft On-Premises Data Gateway (http://bit.ly/2nNNveZ).
2. Save and run the install application on the machine to use as the gateway server.

Figure 16: The Gateway Installation Application

3. Choose the On-premises data gateway (recommended).
   

Figure 17: Selection of On-Premises Data Gateway

4. Choose the file directory for the installation and accept the terms of use and privacy agreement.

5. Sign in to the Power BI Service to register the gateway:

Figure 18: Registering the Gateway

6. Enter a user-friendly name for the gateway and a recovery key. Click on Configure.

Figure 19: Creating the Gateway Recovery Key

With the Gateway installed and registered, data sources, gateway admins, and authorized data source users can be added in the Power BI Service. A Manage Gateways option will be available under the Gear Icon in the Power BI Service. See the Configuring Refresh Schedules and DirectQuery Connections with the On-Premises Data Gateway recipe of Chapter 12, Deploying and Distributing Power BI Content, for details on this process.

How it works...

• As new versions of the gateway are available, a notification is made available in the Status tab of the On-Premises Data Gateway UI as per Figure 1. The Power BI Gateway team recommends that updates should be installed as they become available.
• The On-Premises Data Gateway, rather than the personal gateway, is required for the DirectQuery datasets created in this book and the use of other Azure services in the Microsoft Business Application Platform.
• The Power BI service uses read-only connections to on-premises sources but the other services (for example, PowerApps) can use the gateway to write, update, and delete these sources.

Gateway recovery key

The recovery key is used to generate a symmetric and asymmetric key which encrypts data source credentials and stores them in the cloud. The credentials area is only decrypted by the gateway machine in response to a refresh or query request. The recovery key will be needed in the following three scenarios:

• Migrating a gateway and its configured data sources to a different machine
• Restoring a gateway to run the service under a different domain account or restoring a gateway from a machine that has crashed
• Taking over ownership of an existing gateway from an existing gateway administrator

It's important that the recovery key is stored in a secure location accessible to the BI/IT organization. Additionally, more than one user should be assigned as a gateway administrator in the Power BI service.

There's more...

The Power BI Ideas Forum (http://bit.ly/2n5bFPd) is a valuable source for identifying requested features and enhancements and their status relative to future releases. For example, filtering on the Idea status of 'Started' implies that the feature has already been reviewed and planned and, with development activity taking place, will likely be released, at least in an initial or preview form, relatively soon. Filtering on Planned ideas, particularly those with higher community vote counts, provides a sense of impactful updates to be released over a longer time horizon.

Figure 20: Power BI Ideas Forum filtered on the status of started

See also...

• Details of configuring and managing data sources through the On-Premises Gateway are covered in Chapter 12, Deploying and Distributing Power BI Content.
• Guidance on analyzing performance monitor counters associated with gateway activity is included in Chapter 10, Developing Solutions for System Monitoring and Administration.