Data gateway confusion

Microsoft has created data gateways as a way that cloud products such as Power BI and Azure Machine Learning can access on-prem data, often called a hybrid model.  This is most useful for companies that do not want to put their data in the cloud, or there is simply too much data to upload to the cloud.

The confusion is that there are a number of different gateways, and some of those have been renamed multiple times.  I’ll try to provide a quick summary to help you understand all the current options:

On-premises data gateway: Formally called the enterprise version.  Multiple users can share and reuse a gateway in this mode.  This gateway can be used by Power BI, PowerApps, Microsoft Flow or Azure Logic Apps.  For Power BI, this includes support for both scheduled refresh and DirectQuery.  To add a data source such as SQL Server that can be used by the gateway, check out Manage your data source – SQL Server.  To connect the gateway to your Power BI, you will sign in to Power BI after you install it (see On-premises data gateway in-depth).

Personal gateway: This is for Power BI only and can be used as an individual without any administrator configuration.  This can only be used for on-demand refresh and scheduled refresh (not DirectQuery).

To install either of these gateways, see On-premises data gateway.  The differences between the two:


Data Management Gateway: This is the old solution for Power BI but still used for Azure Data Factory and Azure Machine Learning.  You can download it here.

Here is the differences between the Data Management Gateway and the On-premises data gateway:

  Data Management Gateway PBI On-Premises Data Gateway
Target Scenarios Use Data Management Gateway for building advance analytics solutions.  With Data Management Gateway you can securely and efficiently transfer data between on-prem and cloud and integrate on-prem transformation with cloud. Use the on-premises gateway to keep PBI dashboards and reports up-to-date with on-premises data sources.
Key Capabilities Batch data transfer to/from a set of supported sources and destinations

Execute T-SQL against on-prem SQL Server

DirectQuery to SQL Server

Live connection to Analysis Services

Scheduled refresh against set of supported data sources

Cloud services it works with Azure Data Factory

Azure Machine Learning

Power BI

PowerApps (Preview)

Azure Logic Apps (Preview)

Microsoft Flow (Preview)

Microsoft has another different gateway that is specific to the Azure Analysis Services (Preview) service, at least for now: On-premises data gateway.  UPDATE 8/19/17: Azure Analysis Services now supports the shared On-Premises Data Gateway – see On-premises data gateway support for Azure Analysis Services.

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication. Bookmark the permalink.

2 Responses to Data gateway confusion

  1. Pingback: Understanding Data Gateways – Curated SQL

  2. Steven Neumersky says:

    The name changes are why chap my @$$ the most, but what fun would life be without our marketing friends!