Sunday, 26 August 2018

What are On-Premises data gateways in Power BI

What are Power BI Gateways
A Power BI gateway is software that you install within an on-premises network; it facilitates access to data in that network. It's like a gatekeeper that listens for connection requests, and grants them only when a users' requests meet certain criteria. This lets organizations keep databases and other data sources on their on-premises networks, yet securely use that on-premises data in Power BI reports and dashboards.

A gateway can be used for a single data source or multiple data sources. The following diagram shows a basic view, with the gateway handling requests from the cloud for three on-premises computers. We'll expand on this later in the article.

The On-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.

You can use a single gateway with different services at the same time. If you are using Power BI as well as PowerApps, a single gateway can be used for both. It is dependent on the account you sign in with.

Types of gateways
Power BI offers two gateways, each for a different scenario:

On-premises data gateway (Personal mode) – allows one user to connect to sources, and can’t be shared with others. Can only be used with Power BI. This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don't need to share the data sources with others.

On-premises data gateway(Enterprise mode) – allows multiple users to connect to multiple on-premises data sources. Can be used by Power BI, PowerApps, Flow, Azure Analysis Services, and Azure Logic apps, all with a single gateway installation. This gateway is well-suited to more complex scenarios with multiple people accessing multiple data sources.

Using a gateway
There are four main steps for using a gateway:

  1. Install the gateway on a local computer, using the appropriate mode.
  2. Add users to the gateway, so they can access on-premises data sources.
  3. Connect to data sources, so they can be used in reports and dashboards.
  4. Refresh on-premises data, so Power BI reports are up to date.
You can install a stand-alone gateway or add a gateway to a cluster, which is recommended for high availability.

How gateways work
The gateway you install runs as a Windows service, On-premises data gateway. This local service is registered with the Gateway Cloud Service through Azure Service Bus. The following diagram shows the flow between on-premises data and the cloud services that use the gateway.

Queries and data flow:

  1. A query is created by the cloud service with the encrypted credentials for the on-premises data source. It's then sent to a queue for the gateway to process.
  2. The gateway cloud service analyzes the query and pushes the request to the Azure Service Bus.
  3. The on-premises data gateway polls the Azure Service Bus for pending requests.
  4. The gateway gets the query, decrypts the credentials, and connects to the data sources with those credentials.
  5. The gateway sends the query to the data source for execution.
  6. The results are sent from the data source, back to the gateway, and then onto the cloud service and your server.
Requirements :
The following are the basic requirements to install a On-Premises Gateway.
Minimum Requirements:
  1. .NET 4.6 Framework
  2. 64-bit version of Windows 7 / Windows Server 2008 R2 (or later)
  1. 8 Core CPU
  2. 8 GB Memory
  3. 64-bit version of Windows 2012 R2 (or later)
Related Considerations:
  1. The gateway cannot be installed on a domain controller
  2. If you are planning to use Windows authentication, make sure you install the gateway on a computer that is a member of the same Active Directory environment as the data source(s).
  3. You shouldn't install a gateway on a computer, such a laptop, that may be turned off, asleep, or not connected to the Internet because the gateway can't run under any of those circumstances. In addition, gateway performance might suffer over a wireless network.
  4. Analysis Services is not required to use the gateway. You can use the gateway to connect to an Analysis Services data source.

Limitations of Analysis Services live connections:
You can use a live connection against tabular or multidimensional instances.
Server versionRequired SKU
2012 SP1 CU4 or laterBusiness Intelligence and Enterprise SKU
2014Business Intelligence and Enterprise SKU
2016Standard SKU or higher
  • Cell level Formatting and translation features are not supported.
  • Actions and Named Sets are not exposed to Power BI, but you can still connect to multidimensional cubes that also contain Actions or Named sets and create visuals and reports.
List of available data source types :
Data sourceLive/DirectQueryUser configured manual 
or scheduled refresh
Analysis Services TabularYesYes
Analysis Services MultidimensionalYesYes
IBM Informix DatabaseNoYes
IBM NetezzaYesYes
SharePoint list (on-premises)NoYes
SQL ServerYesYes

Download and install the On-premises data gateway :
To download the gateway, select Data Gateway under the Downloads menu. Download the On-premises data gateway.

Note that updating the On-premises data gateway is achieved by reinstalling the gateway, as described in this section. When updating the gateway (by reinstalling), your existing gateways settings are retained.

Install the On-premises data gateway

  • On-premises data gateway(Enterprise Mode): Multiple users can share and reuse a gateway in this mode. This gateway can be used by Power BI, PowerApps, Flow or Logic Apps. For Power BI, this includes support for both schedule refresh and DirectQuery
  • On-premises data gateway(Personal Mode): 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 schedule refresh. This selection launchs installation of the personal gateway.

There are a few things to note about installing either mode of the gateway:
  • both gateways require 64-bit Windows operating systems
  • gateways can’t be installed on a domain controller
  • you can install up to two On-premises data gateways on the same computer, one running in each mode (personal and standard).
  • you cannot have more than one gateway running in the same mode on the same computer.
  • you can install multiple On-premises data gateways on different computers, and manage them all from the same Power BI gateway management interface (excluding personal, see the following bullet point)
  • You can only have one Personal mode gateway running for each Power BI user. If you install another Personal mode gateway for the same user, even on a different computer, the most recent installation replaces the existing previous installation.
The data gateway installs and runs on your computer. It is best to install the gateway on a machine that can be left running all the time.

Here are a few things to consider before installing the gateway.
  • If you are installing on a laptop, and your laptop is turned off, not connected to the internet, or asleep the gateway won’t work and the data in the cloud service will not be synchronized with your on-premises data.
  • If your machine is connected to a wireless network, the gateway may perform more slowly which will cause it to take longer to synchronize the data in the cloud service with your on-premises data.
Once the gateway is installed, you will need to sign in with your work or school account.

After you are signed in, you will have the option to configure a new gateway, or to migrate, restore, or take over an existing gateway.

Configure a new gateway

  1. Enter a name for the gateway
  2. Enter a recovery key. This has to be a minimum of 8 characters.
  3. Select Configure.

The recovery key will be needed if you ever need to migrate, restore or take over a gateway. Be sure to keep this key in a safe place.

Migrate, restore or take over an existing gateway
You will need to select the gateway you want to recover and supply the recovery key that was used to first create the gateway.

On-premises data gateway connected
Once the gateway is configured, you will be able to make use of it to connect to on-premises data sources.

If the gateway is for Power BI, you will need to add your data sources to the gateway within the Power BI service. This is done within the Manage gateways area. You can refer to the manage data sources articles for more information.

For PowerApps, you will need to select a gateway for a defined connection for supported data sources. For Flow and Logic Apps, this gateway is ready to be used with your on-premises connections.

Install the gateway in personal mode :
The Personal version of gateway only works with Power BI.
After the personal gateway is installed, you will need to launch the Power BI Gateway - Personal Configuration Wizard.

You will then need to sign into Power BI to register the gateway with the cloud service.

You will also need to supply the windows user name and password that the windows service will run as. You can specify a different Windows account from your own. The gateway service will run using this account.
After the installation is complete, you will need to go to your datasets within Power BI and make sure credentials are entered for your on-premises data sources.

Source :

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 


  1. Wow, that's what I was exploring for, what a material!
    existing here at this webpage, thanks admin of this website.

  2. It's remarkable to pay a visit this website and reading the views of all friends about this piece of
    writing, while I am also eager of getting experience.


Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts