The data source resides on its own private network

The Power BI Service is cloud-based, meaning that it was built to run only in the cloud. The service also assumes that other services are available over the Internet. While this is true of many Platform-as-a-Service (PAAS) and Software-as-a-Service (SAAS) applications, it is not always the case with on-premises and Infrastructure-as-a-Service (IAAS) applications.

The data source is not supported in the service

The gateway is also required when Power BI uses a data source that is not supported directly by the Power BI Service. An example of this is the R and Python data sources. Each of these languages supports a variety of versions and libraries that are not compatible with each other, and are thus not supported in a shared infrastructure environment like the Power BI Service and require the gateway to be set up and configured on servers for this use.

A full list of data sources and their need for a gateway can be found at https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources.

Components of the gateway

The gateway consists of three core systems that create a secure and reliable communication channel between the Power BI datasets and their data sources. Each of these services contains multiple components, but we will not go to that level of detail.

With a secure and reliable channel created through which data requests get submitted, we look in more detail how those requests are processed.

Typical steps for a data request

This process is the same for both the standard or personal gateways.

Cached workloads

Cashed datasets are the most common workload types in Power BI and include all reports built using an imported data source as well as all dataflows. In a cached dataset, all data, defined by the source queries (written in Power Query) are imported and stored in the model first. This transformation, performed by the mashup engine, is the same process used by Power BI desktop. The dataset needs to be loaded first before it can be queried and needs to be periodically refreshed for the report data to remain current. Because cached datasets store data in their internal data store, they tend to be much larger than non-cached datasets that store only metadata. When a cached dataset is refreshed, the required processing commands are sent from the Power BI Service to the gateway. Each request is then processed in three distinct steps.

Non-cached workloads

Both storage modes use the gateway as an agent to relay queries from the Power BI Service to their on-premises data sources. Similar to the cached dataset, the gateway fist needs to access the credentials required by the data source before opening a connection. There is no transformation step involved, so the mashup engine is not invoked. The result set for the query is compressed and encrypted. Non-cached workloads, therefore, do not have the same requirement for large amounts of memory, but a good CPU and network connectivity are vital as these directly affect report performance.

Personal gateway

The personal mode gateway exists primarily as an easy to configure and run version of the gateway. There are instances where it is required over the standard gateway. In personal mode, the gateway is installed as a user application. As a user application, local administer rights are not required, and the application runs under the identity of the user who executed it. By running as the user, the gateway mostly has access to everything that the user has access to, making setup and configuration easy. This is useful, as many organizations do not grant local administrator rights to users on their computers; configuring additional service accounts and granting those accounts access to the data source are also something that users are not able to do without the help of IT. This lower barrier to entry makes the personal gateway ideal for developers and other power users.

The personal gateway can only be used by the owner of that gateway. The use of that gateway is also dependent on that gateway being available. If it has been installed on a laptop or similar type device, then it is unavailable while the machine is in sleep mode or has been disconnected from the network, including any required VPN connections.

When reports are promoted to a production-like environment, ensuring access to the gateway becomes very important, if not critical. In these instances, the personal gateway is not sufficient, and a reliable version of the gateway is required; this is where the standard gateway comes into play.

Standard gateway

The standard gateway was built to provide a reliable gateway for use throughout the entire organization. The Standard gateway is the recommended gateway mode for use for anything that is deemed “in Production.”

Unlike the personal gateway, the standard gateway is installed as a service, preferably on dedicated machines running windows server. The standard gateway supports clustering and load balancing, helping organizations ensure reliable and consistent performance, making it the preferred choice for production workloads.

As a service, the standard gateway will run under its own local account; by default, this is NT SERVICEPBIEgwService. This service account can be changed to another local or domain service account.

Regardless of which gateway mode is installed, the underlying architecture of the gateway remains the same.

Requirements

At the time of writing, the Power BI gateway requires a compatible windows 64-bit operation system, which can be at least Windows 8 or Windows Server R2 with .Net 4.6 installed. Newer versions of the gateway require .Net 4.7.2. It is recommended to check on the minimum versions of both the .net framework and OS before installing the gateway. This does mean that unsupported OSs such as Linux will not work, and it is not uncommon to find a lone Windows machine in a predominantly Linux environment set up to act as the gateway.

Sizing

These are only recommendations by Microsoft, and computers that do not meet these requirements may still work, but should be tested first. These recommendations should be used as a starting point, and the computer can be upgraded if necessary. It is also important to consider the workload type. For example, in environments that use only DirectQuery or Live Connections, the gateway computers may not need as much memory or a solid-state drive, as these workload types do not consume as many resources. When import mode is used, however, ensuring that there is enough memory available helps avoid writing the content of the data transformations to disk while the mashup engine is transforming it. It should also be considered that multiple datasets may be refreshing at the same time, meaning that two, three, or more times the amount of memory may be required compared to using Power BI Desktop to load data.

Placement

In addition to the computer that the gateway is installed on, how the computer is connected to the network plays a big role in the overall performance. In both cached and non-cached scenarios, the latency between gateway, the data source, and the Power BI Service affects report refresh and report response times. This is especially relevant on gateways setup in personal mode, that are usually installed on desktops, or even laptops that could be using a slow LAN or even a wireless network.

The recommendation for the placement of the gateway is to set them up as close to the data source as possible, and on a network with the best outgoing Internet connection possible. Administrators may be tempted to install the gateway on the physical host of the data source; this is generally not recommended, especially in the case of services such as SQL Server that may have a high per-core cost that would be consumed by the gateway.

Downloading and installing the gateway

The install file for the gateway can is downloaded in several ways. The easiest way is to navigate to the downloads section under the settings pane and select the gateway. This directs you to a page where you can choose between the standard and personal mode gateway. Some installations will provide a single installer for both the standard and personal gateway.

Once the gateway is installed, the setup wizard guides you automatically into the setup process. If for some reason, you are not ready to proceed with setting up the gateway, you are able to cancel. This does not uninstall the gateway but leaves it in an unconfigured state. This is the same state that that the gateway is left in when installed via PowerShell.

Configuring the gateway

Once the gateway is installed, the next step is to configure it. If you installed the gateway manually using the installer, then you are directed straight through to the configuration screen. If you installed the gateway using PowerShell or if you canceled the setup after the install, then you can enter the configuration panel by searching for the On-Premises gateway in the Start Menu; this is the same for when you want to change the configuration of the gateway.

The first step is to provide the credentials of the user who will administer the gateway. The credentials are used to both authenticate the gateway to the service and make the user an administrator of the gateway. It is good practice to ensure that the Power BI administrators set up the gateway rather than have users or report developers perform this task to ensure a separation of concerns.

After successfully authenticating the administrators’ account, the next step is to choose the configuration type:

Register a new gateway on this computer

Creating a new gateway cluster is as simple as providing a name for the gateway and choosing a recovery key. The name of the gateway should conform to the Power BI gateway naming standard that your organization has in place; it is recommended that this name include the fact that it is a gateway as well as which environment this gateway is to be used in. Although it is possible to use the same gateway for multiple environments, this should be discouraged as development or testing workloads can have negative consequences on the production environment.

The recovery key is required for additional nodes to be added to the cluster, repair the cluster member, and/or to upgrade the gateway. Ensuring that this key is recorded and kept secure is important.

Tip

if your organization does not have a secure means of storing such credentials, then you should consider using Azure Key Vault. https://azure.microsoft.com/en-us/services/key-vault/

Adding a new member to an existing cluster

Adding another gateway to an existing cluster is also a relatively straightforward task. Again you are asked to provide a name for the gateway. You will then enable the “Add to an existing gateway” option that returns a list of existing gateway clusters that the user is an administrator for.

Steps

1. 1.

   Capture the new name of the cluster, again applying the organization naming convention.

    
2. 2.

   Enable “Add to an existing gateway.”

    
3. 3.

   Select the gateway cluster to add the new node to.

    
4. 4.

   Provide the recovery key used for the gateway cluster.

    

Network connectivity

The Power BI gateway requires connectivity to the Internet. By using the gateway, Power BI removes the need to allow incoming connections; these are connections that originate somewhere on the Internet and would need to pass through the corporate firewall. For good reasons, most organizations prohibit such connections. Instead, the gateway makes an outgoing connection to the Power BI Service via the Azure Gateway Service and uses Azure Service Bus as a messaging system.

Depending on how the setup of your network is configured, you may need to request that the firewall administrator open up certain ports and URLs to allow outgoing connections. This is common in secure environments such as banks and other regulated environments. All traffic passes through port 433, and a list of URLs can be found at

https://docs.microsoft.com/en-us/power-bi/admin/power-bi-whitelist-urls

There is an option to run a network diagnostic assessment that will let you know of any problems.

If your organization uses a proxy to access the Internet, you can read how to configure that here https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-proxy.

Updating the gateway

New versions of the gateway are often released, and the administrator must ensure that these are kept up-to-date. New releases of the gateway not only provide performance and security enhancements but also enable new functionality by updating the gateway to use the latest mashup engine that may be required by datasets built using the latest version of the Power BI desktop application.

It should be noted that the gateway becomes unavailable for at least the duration of the upgrade and possibly longer if issues are experienced, so upgrading the gateway should only be done during approved windows for business-critical workloads. If the gateway is installed as part of a gateway cluster, then this upgrade procedure is simpler as each node in the cluster can be upgraded independently of one another, reducing the risk of an outage. It is recommended that you first upgrade one of the non-active nodes in the cluster first.

General health

No application can function at its optimal level if any of these resources is in short supply. In addition to the four primary resources, a good general health monitor will watch out for other signs that the system is under strain or if there are other problems. In the case of the gateway, this could include query timeouts or failures. For a quick understanding of the four primary resources, many users and administrators use Task Manager.

Task Manager

Task Manager is an easy to use application that can quickly give the user an overview of the health of the system. In addition to providing system-level counters, users can drill down to specific applications and processes.

While Task Manager is simple to use, it is not a suitable candidate for reliable monitoring as it required the administrator to be logged into the terminal and monitoring the system. This is impractical for continued usage; instead, Performance monitor provides a better functionality.

Performance Monitor

Performance Monitor, also known as Perfmon, is a system monitoring tool that can be configured to provide in-depth counters on a number of key matrices. Performance Monitor works by aggregating a number of events over a given timespan. As all counters are simple aggregations, these counters are not very helpful in diagnosing specific problems but are very useful in determining if a problem exists. An example of this is monitoring failed queries; while the counter will not help you identify the failed query, it alerts you to the fact that a query has failed. While Performance Monitor can be used in a similar way to Task Manager, as a real-time display of counter values. A better solution is to create a data collector that can be used to systematically log counters and their values on a schedule. These values can then be stored to various destinations such as text files. These stored results can then be collected and analyzed on a schedule using a tool such as Power BI; however, for a real-time monitoring solution, you can log these counters directly into a SQL Database where a new real-time dashboard and alerts can be created.

One of the core reasons for collecting these counter values is to build a baseline for the cluster. A baseline is simply an understanding of what the computer’s resource usage looks like when it is stable. This becomes important during times of crisis when the administrator needs to identify the source of a problem. If you understand what the expected or baseline values are, then it becomes easier to determine if there is a problem. An example of this is finding out that the gateway is consuming 80% of the CPU; this simple measure by itself does not tell you if something is wrong, but if this measure is usually at 40%, then that is an indication of a problem and it should be investigated. It is also good to be able to identify failing queries before users start to complain.

Both the system counters and the gateway counters can be collected into a single data collector set allowing you to build reports and alerts; next we will look at some of the important system and gateway-specific counters.

On-premises data gateway counters

The On-premises data gateway provides several gateway-specific counters that the administrator can use to monitor gateway activity.

At the time of writing, 27 gateway-specific counters can be monitored. Each counter monitors a different workload type and it is not unreasonable to monitor all 27. If, however, you know that some counters are not relevant to your environment, then you may exclude them. Some of the counters that you will want to pay attention to are

• # of all queries executed/sec: This counter will give you a count of the number of queries executed per second. If your organization uses only imported datasets, then this number will likely be small. For reports that use DirectQuery and live connections against on-premises data sources, this number can be a good indication of heavy use.

• # of queries failed/sec: Any number greater than 0 should be investigated as it may be cause for concern and indicate problems with the source system. Timeouts against the production OLTP system can be an indication report beginning to affect the operations of the system.

• # of items in the Service Bus pool: The gateway uses Azure Service Bus to send data requests to the gateway reliably; this counter lets you know if the gateway cluster can satisfy all incoming requests. There is no golden value here, and each environment will differ, which is why baselining this value is important. If this number begins to climb, it is an indication that more queries are being sent to the cluster than the cluster can resolve. To fix this problem, the cluster can be scaled up or out by adding more resources to the gateway computers or adding more members to the cluster. Additionally, the developer can be asked to look for more efficient ways to query the data. (Dataflows could be an appropriate solution.)

Note

Adding additional nodes to the cluster may be meaningless if the “Distribute requests across active gateways in the cluster” is not enabled, as queries will not scale beyond a single node.

Troubleshooting

Here we look at some of the tools available to help the administrator troubleshoot some of these problems.

Refresh history

When a refresh fails in the service, details of the error are logged to the service. These errors can be viewed by the administrators and other users with access to the dataset. You can view these details by viewing the refresh history; if any of these jobs have failed, then details of the failure can usually be logged to the service.

Event logs

The windows event logs can also be used to diagnose problems with the gateway. Such events are usually at the application level, so if there are problems with the gateway starting or if the gateway process suddenly terminates, then the details of the error can usually be found in the event log.

Gateway logs

The gateway itself also records more detailed logs. These logs are usually the best place to start. However, these logs are only available on the standard gateway. The logs can be accessed by

1. 1.

   Launching the On-premises data gateway app.

    
2. 2.

   Login (you must be logged in to view the logs).

    
3. 3.

   Navigate to the Diagnostics.

    
4. 4.

   Select export logs.

    

The application generates a .zip file on the desktop of the user who exported the log. Inside the .zip file, you find multiple files; many of these files are created daily with a date timestamp to make finding the relevant log entries easier. These files can also be accessed directly depending on the operating system the gateway is installed on. You can the log files at:

• UsersPBIEgwServiceAppDataLocalMicrosoftOn-premises data gatewayReport

• WindowsServiceProfilesPBIEgwServiceAppDataLocalMicrosoftOn-premises data gatewayReport

By default, these logs only capture error information. However, it is also possible to capture detailed query execution details and performance counters. The detailed execution report has a wealth of information on which queries are failing or how long they take to complete. The Performance counters are the same as the Performance Monitor counters. To enable the additional logging, you need to manually update the configurations file

Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config setting the QueryExecutionReportOn and SystemCounterReportOn values to True.

After making the changes, you need to restart the gateway which can be done from within the On-premises data gateway app.

1. 1.

   Navigate to Service settings.

    
2. 2.

   Select Restart now.

    

To make analyzing these logs easier, Microsoft has created a Power BI report template that you can use to connect to these logs. At the time of witting, this report was still in preview, but as a template, you are able to modify it to meet your needs.

Additional logging

The level of logging provided by the gateway logs is often enough to diagnose most problems; however, it does not include all the information available. The gateway offers a verbose logging option than can be enabled via the On-premises data gateway app.

Like the performance logging options, the gateway needs to be restarted, so you need to consider this before enabling it during a critical time. This level of logging is also likely to cause additional load on the gateway and computer that it runs on, so leaving the gateway in this mode is not recommended.

Cmdlets to manage policies

Cmdlets to manage gateway clusters and members

Cmdlets to manage users

Managing data sources