What is Power BI Gateway: Streamline Your Data Access

The Power BI Gateway is an application that helps developers access data located within an on-premises network. The developers can choose from both cloud-based and on-premises locations when requesting their data.  

According to Statista Power BI has a 7.1 per cent market share amongst all Business Intelligence and Data Analytics platforms. This share is constantly on the up and owing to the wide suite of features it offers.  Power BI Gateway is a powerful tool for integrating on-premises data with Power BI. Learn how to use Power BI Gateway to securely connect to data sources. 
 
Table of Contents 

1) Understanding Power BI Gateway 

2) Benefits of Power BI Gateway 

3) Types of Power BI Gateway 

4) Power BI Gateway Architecture

5) Power BI Gateway Setup 

6) Managing and monitoring the Power BI Gateway

7) Conclusion 

Understanding Power BI Gateway 

The Power BI Gateway tool is a software application that helps users access the required data residing in an on-premises network. The tool acts as a gatekeeper for the source of data, and any requests made by users to access the data from a cloud or web-based application go through this gateway. Users are granted access depending on their authentication and data needs. 

It's important to note that the tool does not transfer any data from the on-location source to the user's platforms. Instead, the tool connects the data source to the platform directly, and the user or client can then access their data for creating reports and analysing data. The Power BI Gateway tool is also designed to establish connections between the data source(s) and the source on-location. 

Companies can utilise this tool to connect to sources like databases, local systems, servers, and so on, which provide them with essential data that needs to be further analysed. Users can access data directly from the on-location source without the need to move the data to the cloud. Generally, when an organisation expands its business, it needs multiple connections to create one Power BI report. 

Power BI typically supports more than 50 native connectors to generate valuable reports. However, it is a tedious task to connect with the on-location source, which is where the tool comes in handy.



 

Benefits of Power BI Gateway

Some key benefits of Power BI Gateway include: 

1) Companies can maintain connections to on-location data sources and update their dashboards and data reports by utilising the Power BI Gateway tool. 

2) Companies can directly access data without the need to move large volumes of it to an online Cloud Storage, reducing the risk of data loss. 

3) Companies can be assured of a secure and reliable connection established between the local data sources and Power BI. The safe connection protects the on-location data. 

For instance, a company may require a Gateway to access data from an SQL Server database residing on their local domain. 

Types of Power BI Gateway

The Power BI Gateway comprises three modes, depending on how the tool is to be used. Here are the descriptions of the modes in detail:
 

The Power BI Gateway comprises three modes, depending on how the tool is to be used. Here are the descriptions of the modes in detail: 

Personal Mode 

The Personal Mode of the Power BI Gateway service is installed only if a user intends to utilise it for themselves. They can connect it to the local data sources, including Microsoft SQL Server, Excel, and other similar sources. Users should note that the Personal Mode of the Power BI Gateway only supports one connection type, i.e., import data or schedule refresh.  

However, since the service is in Personal Mode, users cannot use it for team development scenarios. This drawback of the Personal Mode can be a possible disadvantage for multiple developers because they would not be able to use the gateway simultaneously. Users can utilise the Personal Mode to create their reports and connect to it. They can also share it with multiple users. However, users must note that the Gateway can only be used by one developer at a time. 

The above-mentioned factors explain why the mode is personal. Moreover, users will find it easier to install and configure the Personal Mode than the on-location gateway. The reason is that the Personal Mode installation does not include the option for configuring the data sources. Users will not need to perform any configurations after the installation. The Personal Mode is intended for Business Analysts who want a hassle-free experience publishing and refreshing their data reports. 

It is best suited for Business Analysts who want to publish their Power BI reports and schedule them to refresh easily. The minimalist and user-friendly characteristics of the Personal Mode make it ideal for single-developer scenarios. 

On-premises Standard Mode

The On-premises gateway mode enables multiple developers to connect with each other on various on-premises data sources. With a single installation, developers can use all of its supported services. This mode is best suited for situations that involve multiple developers with access to several sources of data. 

Additionally, the On-Premises Gateway Mode allows developers to have a Gateway administrator who can use the central configuration section of the service to add more data sources and control them. Moreover, this mode supports many other apps, such as Microsoft Flow, Azure Logic Apps, and PowerApps, which are cloud-based technologies from Microsoft. 

The On-Premises Gateway mode also supports all kinds of connections from Power BI, including DirectQuery and Live Connection, apart from importing data and scheduled refreshes. This mode is intended for enterprise usage of the Power BI tool, and it is beneficial in situations where Power BI is used with other applications like PowerApps. 

Furthermore, the Gateway administrator has the authority to allow multiple developers to use the same Gateway. This type of gateway offers more centralised control and monitoring features. 

Learn to manage data analytics environments using Microsoft Purview, Azure Synapse Analytics and Power BI. Sign up for the Designing and Implementing Enterprise-scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI DP500 course now. 

Virtual Network Data Gateway  

The Virtual Network Data Gateway allows users to connect to various sources of data through secure and reliable Virtual Networks without the need to install a gateway since Microsoft manages it. This type of Gateway is most suitable for complex scenarios where many users need access to multiple sources of data. 

Microsoft designed the VNet Data Gateway to allow users to connect their Azure data services with Microsoft cloud services within a Virtual Network securely. The VNet Data Gateway establishes a secure communication between the VNet and the data source, enabling query execution and result transmission back to the service. 

However, the VNet Data Gateway is only available to datasets in Power BI, Power Platform dataflows, and paginated reports in Power BI. It does not support Power BI dataflows and datamarts, and cloud architectures designed and built to offer data users access in compliance with local laws and regulations. 

Limitations in Azure AD, such as activating the service endpoint for Azure AD on the delegated VNet and activating conditional access policies for the tenant, can cause possible failures for users. Users can only set their regional information and subscription details for the VNet once during the initial VNet Gateway creation. 
 
Learn to manage data and turn it into powerful insights to make better-informed business decisions with our Microsoft Power BI Training Course now! 

Power BI Gateway architecture 

Users may find it difficult to understand the architecture of the on-premises Gateway. Here is a simplified description of how the Gateway works: 

1) The On-premises Power BI Data Gateway typically has three components: Cloud Services, Gateway Services, and On-Premises Data Sources. 

2) The cloud-based Power BI service creates an access query that requests data from an on-premises data source. 

3) The Power BI Gateway Cloud service processes and analyses the request, and then sends it to the Azure Service Bus (ASB). Additionally, the service manages the Azure Service Bus by default. 

4) All data access requests sent to the on-premises data gateway are handled by the Azure Service Bus. The credentials are decrypted for the data source and the user is connected to the data source with the help of the ASB service. 

5) Additionally, the On-Premises Data Gateway sends the data access query to the on-premises data source from the cloud service. 

6) The data query is executed at the source, which can be SQL Server, SharePoint, SSAS, and so on. 

7) The result of the executed Data query is returned by the data source(s) to the on-premises Data Gateway. Furthermore, the On-Premises Data Gateway sends the result back to the cloud service via the Azure Service Bus. 

Regarding data requests pulled by the Azure Service Bus, the Gateway pulls the ASB to check for pending data requests. Although the ASB cannot trigger the data gateway, this protocol is in place to ensure the architecture's security. 

In a scenario where the Data Gateway can be triggered by the ASB, the security ports for inbound requests will have to be kept open. Keeping inbound security ports open puts the architecture's security at risk. Therefore, by using only outbound security ports, the data gateway's connection is kept very secure. 

Learn to process data from various sources, and implement security standards for datasets and groups. Sign up for the Microsoft Power BI Data Analyst PL300 Course now! 

Power BI Gateway setup 

To start using the Data Gateway in Power BI, developers can follow these four steps: 

1) Install the Data Gateway on their systems. 

2) Add other developers to the Data Gateway, giving them access to on-premises data sources. 

3) Use data from on-premises sources to analyse and generate reports. 

4) Utilize the Power BI Gateway service to refresh data on-premises, ensuring that reports and dashboards are updated. 

Power BI Gateway Installation 

Developers can install the Power BI Gateway service on any server residing on a local domain. Here is a list of system prerequisites for successfully installing a Data Gateway for the Power BI service: 

1) The .NET 4.5 framework must be available as it can work with C# or F# and Visual Basic. It can contribute to better performance, security, and reliability 

2) Windows 7 of the 64-bit version or Windows Server 2007 R2 or later 

3) An 8-core CPU 

4) 8GB Memory (RAM) 

5) Windows 2012 R2 (64-bit version) 

After listing out the installation requirements, here are the key steps to install the Power BI Gateway service on your system:  

Step 1: Open your Power BI workspace and click the download icon. Then click the ‘Data Gateway’ option below Power BI Desktop.  

Step 2: The download page for the Data Gateway will open. Click on the ‘Download Gateway’ option.  

Step 3: The On-Premises Data Gateway installer dialogue window will open. Click on the ‘Next’ button.  

Step 4: The Gateway type can then be selected from the options of ‘recommended’ and ‘personal mode’. The ‘recommended’ mode is also referred to as the standard mode. Read the points carefully and then click on ‘Next’.  

Step 5: Developers can choose to keep either the default path of installation or they can enter a new path. Click the checkbox for terms and conditions and proceed by clicking on ‘Install’.  

Step 6: The developer can then enter the email address of their Power BI service account. The entered address will be used henceforth with the Gateway. They can then proceed by clicking on ‘Sign in’.  

Step 7: The developer can now choose to register a new Gateway on their system. In the case of having an installed Gateway, they can also choose the option of migrating a gateway into the system. After choosing the option, click ‘Next’.  

Step 8: The developer can then enter a new name for their Gateway and create a new recovery key for securing the Gateway. Once done, click ‘Configure’.  

Step 9: The last step gives the developer a display of the installation’s confirmation status. The services included with the Gateway are also displayed. Once checked, click ‘Close’.  

Step 10: After the installation wizard is completed, the developer can add a data source for the installed Gateway. This source will be on-premises and accessible to developers from the Power BI cloud-based service through the new Gateway. 

Managing and monitoring the Power BI Gateway

Effectively managing and monitoring the Power BI Gateway is essential for optimal performance. Here are vital considerations for this process:

Manage gateways

Familiarise yourself with the Power Platform admin centre to manage On-premises Data Gateways efficiently. Once installed, manage Gateways based on specific requirements. The management options may vary depending on how each service integrates Gateways. Administrators can add or remove Gateway administrators in the management dashboard, configure settings, and manage security roles and permissions.

Troubleshooting 

Monitoring the On-premises Data Gateway's performance is crucial. Instead of manually monitoring performance counters, Power BI provides query logging and a Gateway Performance PBI template file for visualising results. When troubleshooting, verify the Gateway software version, check logs for errors, and consult the Power BI community forum for solutions.

High availability

Prioritise high availability for datasets and reports in Power BI. Design a well-architected On-premises Data Gateway setup with failover and redundancy. Cluster multiple Gateway instances, monitor each instance's health, and regularly test failover strategies. This ensures efficient, reliable data access, minimising downtime and operational challenges.

Conclusion 

The Power BI Gateway establishes a connection between the dataset on the cloud-based service and the source of data on-premises. It is important for developers to know that the Gateway is necessary only for accessing data sources on-premises data sources.  

Learn to use Microsoft Power BI’s vast range of tools and model your data into visually immersive insights with our Microsoft Power BI for End Users 55400AC Course now!