Power BI Gateway: A Step by Step Comprehensive Guide

Power BI is an efficient business analytics and reporting application software for analyzing data. Data access and transfer have become critical since there is a rising trend of using both on-premises and cloud data forms in an organization. This is where the Power BI Gateway comes into play. PBI Gateway is a service that connects the Power BI Service to the on-premises data sources and enables the creation of scheduled refreshes. In this article, we will describe what a Power BI Gateway is, the types of Gateway, installation of the Gateway, how to use it, and its advantages. 

What is Power BI Gateway?

Power BI Gateway expresses non-stop data refresh from the sources to the Power BI report deployment. A Power BI Gateway is an application that helps transfer data between an organization’s on-premise systems and Power BI or other related Microsoft cloud solutions such as Azure Analysis Services, Power Apps, etc. It operates as an intermediary to prevent sensitive data from being released from the organization’s on-premises infrastructure. It simultaneously enables the users to analyze it using applications hosted in the cloud. For more details, you can visit our Power BI Tutorial.

Do You Always Need a Gateway?

Not every scenario requires a Power BI Gateway. It is only necessary when your data source is in your facility or your organization’s premises. For instance, when the data is stored in an on-premises SQL Server, a gateway is needed, while in cases like Azure SQL Database, it is not. Through the Gateway, data is passed through a controlled channel so as not to traverse the Internet, keeping data within the organization’s system. 

Types of Power BI Gateway

There are three main types of Power BI Gateway: 

On-premises Data Gateway (Standard Mode): This version is suitable for large organizations that require the application to handle several users and sources of information. It enables output reports to be shared and worked on in an environment that involves development from various people. 

On-premises Data Gateway (Personal Mode): This mode is for an individual consumer. It supports only one connection type and could be better for collaborative scenes. 

Virtual Network Data Gateway: This type forms several data sources in a VN. Microsoft handles it wholly and is more suitable for tasks with many connections to other databases. 

To further enhance your Power BI skills, consider enrolling in our Power BI Course, which covers these gateways in detail.

Power BI Gateway Architecture – Working

The components of a Power BI Gateway are shown in the image below; the steps of a Power BI Gateway include processes that ensure proper and secure transfer between cloud services and data sources on the local network. Here’s an explanation of the workflow: 

Scheduler Service and Data Movement Service (Cloud):

The topology starts from a cloud service of Power BI and the scheduler service to trigger the whole dataset refresh. The Data Movement Service creates a request to retrieve the required data from the on-premise data stores. These are queries that accompany encrypted credentials. As a result, it can participate in the access of data without posing any threat. 

Azure Relay (Cloud):

The query and the credential are then sent effectively to the service bus; this serves as the middleman. The Service Bus sends data messages to Azure Relay, a Microsoft cloud solution to assist in transferring the data requests to the on-premises network.

Gateway (On-premises):

The query and the credentials are then forwarded to the Gateway by the Azure Relay. It then decrypts the credentials for an on-premises data source to authenticate and gain access to the data. The Gateway helps as a secure link for passing data or information since it can only flow from on-premise to the cloud and vice versa. 

Database (On-premises):

The Gateway then proceeds to run the query that has been provided to it on the specific on-premises data source, a DB. It does so with the objective of safely requesting the required data that a user would want to input into a program. 

Data Return (On-premises to Cloud):

Subsequently, it transmits the data through the Service Bus into the Gateway’s cloud environment. This is transmitted safely so that the nature and content of the data will remain as is and will be concealed. 

Final Delivery (Cloud):

Data is imported and returned to the Power BI service to create reports and dashboards or refresh them.

It provides a mechanism for secure and efficient transfer of On-premise data and real-time connection to Power BI cloud service to avoid direct exposure of On-premise data to cloud service. To learn more about how to develop informative Power BI Reports, read our Power BI Reports blog.

Important Things to Consider Before Installing the Power BI Gateway

Before installing a Power BI Gateway, consider the following: 

• System Requirements: Other requisites are, . NET Framework 4. RAM of 8, 8 GB of RAM, eight-core CPU, 64-bit Windows Server 2012 R2 or later required. 
• Location: Never install the Gateway on a domain controller. Hence, it is recommended that this tool be worked on on a computer that is directly connected to the Internet for the best performance. 
• Security: Outbound-only ports are sufficient, and the ports that must always be open are TCP 443, 5671, 5672, and 9350-9354. In this connection, the Gateway is mentioned not to demand inbound ports to maintain the security of the data. 

How to Install Power BI Gateway

Step 1: Download the Gateway

• Download Link: To access Power BI Gateway, visit the Power BI Gateway download page to download the application. 

• Alternative Access: However, to do this, you can go to the Power BI service, download it, and then head to “Data Gateway”.

Step 2: Run the Installation File

• It is necessary to start the Installation File. Once you have it, extract files from it, or in other words, open the installation file. You will be asked for the type of the specific Gateway you want to establish. We’ll install the on-premises recommended gateway for this article as it supports both Live and DirectQuery connections.

Step 3: Choose the Installation Folder

• The installer shall download other required files if there is a need to download them. Here, you will be required to choose a directory where you want the application to be installed. As for the folder, you can leave it to the default one or manually set up the new path.

Step 4: Register Your Gateway

• After installation has been completed, it requires the user to get the GW to register. You usually need to use your Power BI email to sign in, but it sometimes logs you out unnecessarily. Here, you will have the choice of either registering a new gateway or migrating/restoring a current one.

Step 5: Enter Gateway Information

• Gateway Name: Choose a name that can easily be associated with the gateway aspect, such as the movie Vaio, for instance, “Reza-Vaio-Gateway. ” 
• Recovery Key: Make a recovery key, which is required to get back to the Gateway in the future. Store this key securely.

Step 6: Optional – Add to Gateway Cluster

• You have the option to add the Gateway to one of the existing gateway clusters to make the setup highly available. For this example, leave this one blank.

Step 7: Confirm Gateway Status

• On successful registration, a message will appear on the screen that the Gateway is up and running..

Step 8: Managing Gateways in Power BI Service

• In the Power BI Service, click on the Settings icon, then select “Manage Gateways.” You will see a list of all gateways set up under your account.

Step 9: Tenant Administration

• If you’re a tenant administrator, you can enable “Tenant administration for gateways” to see and manage all gateways within your organization’s tenant, even if you’re not the direct administrator of each gateway.

Ensure that the recovery key is securely stored, as it is essential for gateway recovery or migration.

How to Use Gateway in Power BI

Once Power BI Gateway is deployed, it underlines a solution for error-free, secure data extraction from the on-premise sources to Power BI cloud service. For maximum usage of the gateway, you have to link your datasets to the on-premise sources by the usage of the gateway. Here’s a step-by-step guide on how to do this: 

Step 1: Open Power BI Desktop

• Load Your Power BI File: Begin by using the ‘open a new file’ or’ empty document’ in your *. PBIX file in Power BI Desktop. To be precise, this file has your reports and data models. 

Step 2: Identify and Copy Data Source Paths

• Navigate to Data Source Settings: Go to the Home tab in Power BI Desktop and navigate to ‘Edit Queries’. Then, under it, click on ‘Data Source Settings’.

• Copy Data Source Path: For each data source that you have referenced in your reports go to Cell, right-click and choose Change Source. Highlight with your mouse the file path or connection string that appears on the screen and copy this path/ connection string. This path will be required when establishing the data source in the Power BI service.

Step 3: Manage Gateways in Power BI Service

• Access Power BI Service: The next step is to use any web browser on your computer to access the Power BI service.
• Navigate to Manage Gateways: Click on the Settings icon (usually represented by a gear symbol), and then select Manage Gateways from the menu.

Step 4: Add Data Source to the Gateway

• Select Gateway: Select the gateway which you installed earlier from the list that is shown on the computer screen.
• Add Data Source: Click on Add Data Source. You will need to provide the following details:
  • Name: Provide a label/name to the data source for better reference.
  • Data Source Type: Choose the type of structure (for example, File, SQL Server, etc. ).
  • File Path or Server Name: Type the path or the server name which you have copied above here. Make sure this path is accessible from the machine that has the gateway installed.
  • Credentials: Enter the credentials which have access to the data source. For example, for a file connection, use the local username and password, including the domain (domainusername). 

Step 5: Confirm and Test the Connection

• Verify the Connection: After entering the required information, click the “Test Connection” button. If the connection is successful, you will see a confirmation message stating, “Connection Successful.”

Step 6: Configure Dataset to Use Gateway

• Go to Datasets: Navigate to the Datasets section in Power BI Service.
• Set Gateway Connection: Click on the dataset you want to refresh using the gateway. Under Gateway Connection, select Use a data gateway in the dataset settings and then choose the appropriate gateway from the list.
• Apply Settings: Click Apply to save the settings.

Gateway Connection Setup to a Dataset

To set up a gateway connection to a dataset:

1. Navigate to the Dataset: In Power BI Service, select the desired dataset.
2. Configure the Gateway Connection: Under the Gateway Connection tab, select Use a data gateway, choose the appropriate gateway, and apply the changes.

This setup allows the dataset to refresh using on-premises data sources securely. 

For more advanced configurations and scenarios, consider our Data Analyst Course.

Troubleshooting Power BI Gateway

Common issues with Power BI Gateway include missing data sources in the gateway setup, incorrect credentials, or network configuration errors. Enabling additional logging and accessing gateway logs can help diagnose and resolve these issues. 

For specific troubleshooting steps, consult the Power BI Gateway troubleshooting guide.

Benefits of a Power BI Data Gateway

The Power BI Gateway offers several advantages:

• Unified Access: One gateway can connect multiple on-premises data sources to the cloud, simplifying data management.
• Secure Data Transmission: Azure Service Bus is used for secure and efficient data transfer.
• Centralized Management: Offers a central hub for managing data sources and user access, which is particularly useful in enterprise environments.

To prepare for interviews and understand commonly asked questions, visit our Power BI Interview Questions page.

Summary

In summary, the Power BI Gateway is a crucial component for organizations that need to access and analyze on-premises data through Power BI and other Microsoft services. It offers secure, efficient, and centralized data management, making it an invaluable tool for data-driven decision-making.

Power BI Gateway FAQ

1. Why do you need a Power BI gateway?

The gateway enables secure data access between on-premises data sources and Microsoft cloud services, allowing for real-time analysis without moving data to the cloud.

2. What is a gateway connection?

A gateway connection establishes a secure link between on-premises data sources and cloud-based applications.

3. What is the difference between standard and personal gateway Power BI?

The standard gateway supports multiple users and data sources, while the personal gateway is limited to individual use and supports only one connection type.

4. What are the requirements of Power BI Gateway?

Minimum requirements include a 64-bit Windows 7 or later operating system, .NET Framework 4.5, and specific hardware specifications for optimal performance.

5. How is a gateway different from a router?

A router directs data packets within a network, while a gateway connects different networks, facilitating secure data transfer between them.