On-Premises Data Gateway: Not Just for Private Resource Access (2024)

The on-premises data gateway isn’t just for enabling Power BI Service cloud-hosted components to pull data from private local network resources.

True, Microsoft’s short description of the gateway says that it is “a bridge” which “provides quick and secure data transfer between on-premise data, which is data that isn’t in the cloud, and several Microsoft cloud services.” However, beyond that description, there are at least three additional key uses for this gateway which are of particular pertinence in the Power BI world.

What the Gateway Enables

All in all, an on-premises data gateway offers at least four key capacities in a Power BI context:

On-Premises Resource Access

Enables cloud-based Power BI to pull data from sources inside your local network (perhaps a database server, local file share or internal web app that is not exposed to the public Internet).

When the cloud-based Power BI Service needs data from one of your internal resources, the relevant expression is relayed to your gateway, which runs on a host you provide, presumably inside your local network. As part of processing that expression, the gateway pulls the appropriate local data, then returns its output to Power BI in the cloud, where it is then incorporated into the ultimate results produced by the Power BI Service.

Driver/Library Host for Microsoft-shipped Data Connectors

Microsoft ships many data connectors with Power BI. While most work out of the box, several require prerequisites to be installed. For Power BI Desktop, the install location is your local system. In contrast, when the cloud-based Power BI Service is involved, the situation is more complex, as you cannot install prerequisites directly in the Service.

Instead, M code that relies on the presence of prerequisites must be run on a system you provide where you’ve installed those prerequisites. The on-premises data gateway is the link between the cloud-based Service and this system. When the Service encounters an expression that pulls data from one of these prerequisite-dependent sources, it relays it to the gateway, which orchestrates its processing on your host. The results are then returned back to the Service where they are incorporated into the final output that’s produced.

ODBC/OLE DB Driver Host

Power BI can use an ODBC or OLE DB driver to access data. However, the driver needs to be installed somewhere. For Power BI Desktop, that somewhere is the local computer. For cloud-hosted Power BI, the driver can’t be installed directly into the Power BI Service but instead must live on a system you provide (a gateway host) that is linked to the cloud via an on-premises data gateway. Similar to the proceeding point, processing of M expressions that rely on this driver will be offloaded to your host via the gateway.

Custom Connector Host

When complex M code is involved with accessing an API or source, you can write it once, package it into a custom connector, then simply reference that connector from your various projects.

To do this with Power BI Desktop, the connector needs to be installed in the appropriate local directory. To use a custom connector from the Power BI Service, it must be installed on an on-premises data gateway host. Expressions that use it will be relayed from the Service via the gateway to that host for processing.

Why “On-Premises” in the Name?

Three out of these four key functions don’t, per se, necessarily have anything to do with accessing what you or I would consider on-premises data. For example, you might be using a custom connector or ODBC driver to access a cloud-hosted database server that is reachable from the public Internet. The ultimate server being queried isn’t on your premises or inside your private network, yet (perhaps confusingly) an on-premises gateway is still required to access it.

The key to clearing up this confusion is to think of “on-premises” in the title “on-premises data gateway” as referring to where the processing of the expression takes place, instead of as implying that the ultimate resource being accessed is always local to your premises.

The on-premises data gateway runs on a host you provide. M expressions are relayed to it from the cloud-based Power BI Service, which are then evaluated locally on your gateway host. This enables those expressions to run in a context that has access to drivers, custom connectors and network connections that the Power BI Service does not itself have access to.

Since your gateway host is not part of the Power BI Service, from the Service’s perspective, it lives on your premises—even if your gateway host is a VM in a cloud space somewhere, not a physical system on your physical campus.

Thanks to Bob Z. for his most helpful feedback on this article!