Managing the On-Premises Data Gateway

For many organizations, the data sources for Power BI datasets or reports are located in on-premises environments. The on-premises data gateway (gateway) serves as a bridge between the Power BI service and on-premises data sources, providing a means to securely connect to these sources to support scheduled data refreshes. In the case of DirectQuery and Analysis Services Live connections, the gateway is used to return the results of queries requested by users in the Power BI service.

As a critical component of many Power BI solutions and potentially other solutions utilizing Microsoft cloud services, such as Azure Analysis Services, Power Automate, and Power Apps, a sound understanding of the data gateway is essential.

This chapter reviews the architecture and behavior of the On-premises data gateway in the context of Power BI. End-to-end guidance and best practices are provided across the primary stages of deployment, from planning to...

On-premises data gateway planning

Planning for the On-premises data gateway involves considerations for infrastructure/resources and networking, data governance, and the administration of the gateway itself. Before committing to a gateway, an organization can determine if BI solutions should be developed against data sources that require a gateway such as legacy on-premises databases or if this data should be loaded/centralized in a source that doesn’t require a gateway such as Azure Synapse or Azure SQL Database.

For example, a BI team could determine that a gateway will only be used for a particular on-premises SQL Server database and any other on-premises sources such as files on network file shares and other databases will either not be supported or will require its own gateway in the future.

After determining that a gateway or cluster of gateways will indeed be a required component of the Power BI architecture, more detailed considerations can be reviewed such...

Gateway concepts

There are a number of different concepts and designs enterprise BI teams should be familiar with when it comes to the On-premises data gateway. These include the concept of gateway clusters, different gateway architecture designs, and security. Each of these subjects is explored in detail in the following sections.

Gateway clusters

Gateways natively support clustering. Clustering is when independent software systems, called instances or nodes, work together as a unified resource to provide greater scalability and reliability. Each Power BI dataset is associated with a single gateway cluster, which is composed of one or more standard gateway instances.

For example, if a Power BI dataset imports data from both a SQL Server database and an Excel file, the same gateway cluster is responsible for the import from both sources.

Likewise, if hundreds of business users interact with reports based on the same DirectQuery dataset or a Live connection to an...

Gateway installation and configuration

Once the gateway scenario and architecture are planned as per the previous sections, BI or IT administrators can download and install the gateway (or multiple gateways) on the chosen server(s). The gateway installation file to be downloaded is small and the installation process is quick and straightforward.

The gateway installer application is obtained via the Download (down arrow) dropdown in the Power BI service, as shown in Figure 11.8.

Figure 11.8: Download in Power BI service

The Data Gateway item from the download menu shown in Figure 11.8 currently links to a Power BI Gateway page with two large buttons near the top for downloading either the standard or personal mode gateway installers.

Selecting either button downloads the installer file (GatewayInstall.exe or on-premises data gateway (personal mode).exe) locally. The installation and configuration process via the installer application is very straightforward....

Managing gateway clusters

Once a gateway is installed, the Power BI account used to register the gateway during installation can access the Manage gateways portal in the Power BI service to assign administrators for the gateway.

The Manage gateways portal is available via the gear icon in the top-right corner of the Power BI service, as shown in Figure 11.15:

Figure 11.15: Manage gateways

The Manage gateways portal exposes all gateway clusters where the user is assigned as an administrator. The following sections describe the management of gateways within the Power BI Service. However, it is important to note that certain essential gateway management features have been moved to the Power Platform Admin Center.

Specifically, the gateway security roles and permissions described in the Defining gateway roles and permissions section of this chapter are only available in the Power Platform Admin Center. In addition, the Power Platform Admin Center also provides...

Troubleshooting and monitoring gateways

For organizations with significant dependencies on the On-premises data gateway, it’s important to plan for administration scenarios, such as migrating or restoring a gateway to a different machine. Administrators should also be familiar with accessing and analyzing the gateway log files and related settings to troubleshoot data refresh issues. Finally, gateway throughput and resource availability can be monitored using Power BI template files provided by Microsoft.

In this section, we investigate each of these topics in turn.

Restoring, migrating, and taking over a gateway

Sometimes it is necessary to migrate or restore a gateway to a separate server. For example, a gateway may have initially been installed on a server with insufficient resources to support the current workload. In other cases, a hardware failure may have occurred on a gateway’s server and thus it’s necessary to quickly restore connectivity...

Data refresh

The configuration of gateways and their role during data refresh varies depending upon the mode of the dataset, such as import, DirectQuery, or Live. In this section, we explore the gateway’s role, additional configuration steps, and technical nuances of each of these modes.

Scheduled data refresh

Scheduled data refreshes allow Power BI datasets to be updated with the latest data on a preset schedule. The scheduled refresh for an import or composite mode dataset is configured on the Settings page for each dataset in the Power BI service.

The Settings option is found by clicking the three vertical dots displayed next to datasets listed in the Power BI service, either in the left navigation pane or the Datasets + dataflows tab of a workspace. Once on the Settings page, the Schedule refresh section can be expanded as shown in Figure 11.23.

Figure 11.23: Scheduled data refresh

In Figure 11.23, a Daily refresh at 5:30 A.M. Eastern Time is set...

Summary

This chapter reviewed the primary planning and management scenarios for the On-premises data gateway. This included alternative-solution architectures, methods for distributing workloads across multiple gateways, and ensuring high availability via gateway clusters. Additionally, this chapter described the process of administering a gateway, including the configuration of data sources and the authorization of users or groups to utilize the gateway per source. Finally, the primary tools and processes for troubleshooting and monitoring the gateway were reviewed.

While this chapter focused on using the Power BI service with on-premises data and traditional Power BI datasets and reports, the following chapter highlights paginated reports and the option to deploy Power BI exclusively on-premises via Power BI Report Server. This includes the publication, refresh, and management of Power BI reports on-premises and within the Power BI service as well as the primary differences...