What is Power BI Gateway?
Power BI Gateway is a critical component in the Power BI ecosystem that enables data to flow securely between on-premises data sources and the Power BI Service in the cloud. It allows you to keep your Power BI reports and dashboards up-to-date by connecting to data sources that are not directly accessible from the cloud. It acts as a bridge, facilitating data refresh and access to on-premises data while maintaining data security and compliance.
By using a Power BI gateway on-premise, Organizations or Institutions can host/maintain databases and other data sources on their on-premises network locations and maintain data security on their on-premises data in the cloud infrastructure.
Table of contents
- Power BI Gateway bridges the gap between on-premises data sources and the Power BI service.
- It comes in two main types: On-premises Data Gateway (Standard) and Personal mode.
- Installation involves downloading the appropriate gateway, configuring data sources, and linking the dataset to the gateway in the Power BI Service.
- Troubleshooting and regular updates are essential for maintaining a reliable data gateway connection.
There are three types of Power BI Gateways. Each of these gateways serves a different purpose and is custom-tailored for specific scenarios, as highlighted below:
|On-premises data gateway
|On-premises data gateway (personal mode)
|Virtual network data gateway
|• Using this gateway, multiple users can connect to different on-premises data sources.
• A big advantage of this gateway is that by installing a single gateway, it is possible to use this gateway with all the supported devices.
• The best usage of this gateway involves scenarios where you need multiple people to access a different range of data sources that adds to the complexity.
|• This gateway allows one user to connect to different data sources but can’t share with other users.
• The biggest limitation is that this gateway can only be used with Power BI.
• This gateway typically works well in the scenario where there is a single user who creates a report for his purpose with no need to share any data sources with others
|• This gateway allows multiple users to connect with different data sources that are secured by virtual networks.
• It provides flexibility in that you don’t need to install this gateway given it’s a Microsoft-managed service.
• This gateway typically works well similar to an On-premises data gateway where you need multiple people to work on multiple sources.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use Power BI Gateway?
There are five key steps to be followed to use the Power BI gateway. These key steps are outlined below:
Step 1: First download the gateway and then install it in a local system
Step 2: Once the installation is complete, configure the Power BI gateway after the assessment of your firewall and other network requirements
Step 3: You can then add Power BI gateway admins to ensure the management and administration of other network requirements
Step 4: Refresh the on-premises data sources using the gateway
Step 5: In case you encounter any issues with the gateway, troubleshoot them as needed
In the subsequent sections, we will understand the software and system requirements and how to install the Power BI gateway in a step-by-step process.
The minimum requirements to install a Power BI gateway include:
- .NET Framework 4.7.2 (Gateway release December 2020 and earlier)
- .NET Framework 4.8 (Gateway release February 2021 and later)
- A 64-bit version of Windows 10 or a 64-bit version of Windows Server 2012 R2 with current TLS 1.2 and cipher suites
- 4 GB disk space for performance monitoring logs (in the default configuration)
Additionally, you also ensure that the below-recommended hardware configuration requirements are met, which include:
- An 8 core CPU
- 8 GB memory
- A 64-bit version of Windows Server 2012 R2 or later
- Solid-state drive (SSD) storage for spooling
How to Install Power BI Gateway?
Before you start installing the Power BI gateway, ensure that you are installing it on a system that’s turned on and that the computer is on a wired network instead of a wireless one. You have two choices from which you can install – i.e., standard mode or personal mode. If you are installing an on-premises data gateway (standard mode), then ensure it’s on a domain-joined machine that has a trust relationship with the target domain.
Step 1: Installing a Data Gateway
Navigate to the Microsoft URL and choose Download standard mode to download the gateway installer.
It would start the installation process on your computer.
Once the installation is complete, provide your email address to be used with the gateway and click on the Sign in button.
Once you have signed in to your account, select the “Register a new gateway on this computer” option and click on the Next button.
Enter the mandatory details highlighted as *. Once all the details have been keyed in, click on the Configure button.
Review the information in the final screen. This would install a data gateway.
Once you have installed a gateway, you can also add another gateway to create a Power BI gateway cluster. Having a cluster helps the admins avoid any single point of possible failure for the data access available on-premises.
Step 2: Adding a Data Source for Gateway
Once the gateway is installed and ready for use, the next step involves Power BI gateway configuration to the Power BI service and adding a data source for the gateway.
- To apply the configuration, log into the Power BI service (app.powerbi.com)
- Choose the Manage connections and gateways option under settings.
- You will notice the gateway details under the cluster. Now to add a data source to it click on Add data sources to use this gateway option
- In the Data Source Settings page, provide details such as data source name, type, and other details, etc., and click on Add to establish the Power BI data connection.
Step 3: setting up a Power BI gateway connection to The Dataset
Now to set up the Power BI gateway connection to the dataset, provide the details such as Authentication method, user credentials, server details, and if needed fill in the advanced connections. Click on the Add button. If you see a connection successful message then the data connection is completed successfully.
Power BI Gateway Architecture
Power BI gateway architecture involves the following components.
- Power BI cloud service: The cloud service performs a query creation and encryption of the credentials which is required for the on-premises data source. It further sends both these queries and credentials to the gateway queue for processing.
- Gateway Cloud Service: It performs the query analysis and further pushes the request to Azure Relay.
- Azure Relay: It relays the pending requests to the gateway. Note that both the gateway and Power BI service can only accept TLS 1.2 traffic
- On-premises data gateway: It receives the query, decrypts the credentials, and using these credentials connects to multiple data sources. The data gateway further sends the query to the data source to be run.
- On-premises data sources: The query is run on the data sources, sent back to the gateway, and then further to the cloud service
Note when you run Power BI data refresh queries, it results in large amounts of data. To handle such requests, spooling is performed. This is a process of temporarily storing the data on the gateway machine till you receive the data from the data source and then sending it back to the cloud service.
The overall Power BI gateway architecture diagram is provided below:
Source: Microsoft Documentation
Common troubleshooting steps for Power BI Gateway include:
- Checking the gateway status and logs in the configuration tool.
- Verifying that the data source is accessible from the gateway machine.
- Reviewing data source credentials and permissions.
- Ensuring that your network/firewall settings allow the necessary communication.
Important Things to Note
- Regularly update the Power BI Gateway to ensure you have the latest features and security patches.
- It is essential for connecting to on-premises data sources and automating data refresh in the cloud.
- Security and proper configuration are crucial to protect sensitive data.
- Power BI Gateway for Enterprise offers advanced features for larger organizations and high availability scenarios.
- Compatibility and connectivity options may vary depending on the type of data source and its location. Always refer to Power BI documentation for specific details.
Frequently Asked Questions (FAQs)
Updating Power BI Gateway is essential to ensure that you have the latest features, bug fixes, and security patches. To update it, follow these steps:
a. Download Latest Version:
• Go to the Power BI Gateway download page on the Power BI website.
• Download the latest version of the gateway that matches your requirements.
b. Install the Update:
• Run the downloaded installer and follow the installation prompts.
• During the installation, choose the “Upgrade” option.
• After the installation, open the configuration tool.
• Review and update your data source configurations, as necessary.
d. Apply Changes:
• Click on the “Apply” or “OK” button to save your changes.
e. Monitor and Test:
• Monitor the gateway to ensure it’s running correctly.
• Test your data sources and reports to confirm that the update hasn’t caused any issues.
Gateway credentials are typically stored within the Power BI Gateway itself. These credentials are used to connect to data sources and authenticate when retrieving data. You can manage and update these credentials through the Power BI Gateway configuration tool. They are not directly stored within the Power BI Service or Power BI Desktop.
Whether you need a gateway for SharePoint in Power BI depends on the specific scenario and data source you are using. Here are some key points to consider:
• SharePoint Online: If you are connecting Power BI to SharePoint Online, you usually do not need a gateway. You can connect directly from Power BI Service using the SharePoint Online connector.
• SharePoint On-Premises: If you have SharePoint data hosted on-premises, you may need a gateway. The Power BI Gateway (On-Premises Data Gateway) is used to bridge the gap between your on-premises data and the Power BI Service. It allows you to refresh data from on-premises data sources, including SharePoint on-premises.
• Hybrid Scenarios: If your SharePoint data spans both SharePoint Online and SharePoint on-premises, you might need a gateway for the on-premises part.
The specific need for a gateway depends on your SharePoint deployment and data source configuration.
Power BI Gateway provides several benefits:
• Data Refresh: It allows you to refresh data from on-premises data sources, keeping your reports and dashboards up-to-date in Power BI Service.
• Secure Data Transfer: It ensures secure data transfer between your on-premises data sources and Power BI Service by using encryption and authentication.
• Scheduled Refresh: You can schedule data refreshes, automating the process and reducing manual intervention.
• Data Source Support: It supports a wide range of data sources, including databases, files, cloud services, and on-premises systems.
• Data Transformation: You can perform data transformations and data source-specific operations as part of the refresh process.
• Consolidation: It allows you to consolidate data from various sources into a single dataset in Power BI.
Using Power BI Gateway is essential for organizations that have a mix of on-premises and cloud data sources or need to keep data updated in Power BI Service.
This has been a guide to Power BI Gateway. We learn the steps to install gateway, its uses, Types, Architecture, Troubleshoots and points to remember. You can learn more from the following articles –