Power BI lets you connect to a variety of data sources, irrespective of where they are stored. You can have data sources on the cloud or office premises, and still Power BI can connect to it without putting much effort and money on infrastructure.
It is always easy to connect to the data source stored in the cloud. However, when it comes to on-premises data sources, the Microsoft cloud services, like Power BI, require a Power BI gateway to get connected.
With the help of a gateway, Power BI can access data from locally stored databases quickly and securely.
What is the Power BI Gateway?
Power BI gateway is a software that can connect and access the data sources stored locally on-premises. It can help the businesses who keep the data in the local databases behind the firewall but wish to use Microsoft cloud services.
With the help of an on-premises gateway, you can not only connect Power BI to local databases but can also connect other Microsoft services like PowerApps, Azure Analysis Services, Power Automate, and Azure Logic Apps.
Power BI gateway acts as a bridge between Power BI and the local databases of the company. However, you need the right credentials to bypass this gateway and access the database.
Types of Gateway
Microsoft offers two types of gateway, but there is a difference between them. One gateway type is the standard mode, and another is the personal mode.
On-premises data gateway (Standard Mode)
With standard mode gateway, multiple people are allowed to access multiple local databases. If you have multiple developer environments that need to access the databases, the standard mode is recommended.
You can install a single gateway and connect Power BI, Azure Logic Apps, Azure Analysis Services, Microsoft Flow, and PowerApps to on-premises databases.
The standard mode is for complex environments where you have multiple users working on multiple on-premises data stores.
On-premises data gateway (Personal Mode)
With personal mode Power BI gateway, only one user can access the local databases. You can only connect Power BI to on-premises data sources with this mode of the gateway.
If you have an environment where only a single person accesses the data source to analyze the data and generate reports, the personal mode is recommended.
Installing and configuring on-premises data gateway personal mode is easier than standard mode because of its single user allowance. The privileges cannot be shared among multiple users in personal mode, hence the name.
Standard Mode vs. Personal Mode
There is not only the difference in the number of users allowed in both the mode, but few more things make them different from each other.
|Standard Mode||Personal Mode|
|Can work with Power BI, Azure Logic Apps, Power Apps, Azure Analysis Services, Power Automate, Dataflows||Can work with only Power BI|
|Can allow multiple users to access local data sources||Can allow only one user to access local data sources|
|Supports DirectQuery||Doesn’t support DirectQuery|
|Cannot run as an app for users who do not have administrator privileges||Can run as an app for users who do not have administrator privileges|
Caption: Table of difference between standard mode and personal mode on-premises data gateway
Power BI Gateway Architecture
The Power BI data gateway works in a particular flow whenever there is a request to access on-premises data sources from the cloud services and web apps.
The entire flow is divided into six steps.
Step 1: Power BI creates a query that requires access to the local data source. This query, along with encrypted credentials, is sent to Gateway Cloud Service.
Step 2: The Gateway Cloud Service analyzes and processes the request and forwards it to Azure Service Bus.
Step 3: Azure Service Bus, then sends the request to the on-premises data gateway.
Step 4: When the on-premises data gateway gets the request, it decrypts the credentials and connects to the required data sources depending on the query.
Step 5: The Power BI gateway forwards the query to the connected data sources so that it can be executed there.
Step 6: The results given by data sources are forwarded to the gateway. The on-premises gateway then sends the results back to Power BI.
If there is a large amount of data to be returned from data sources, it is temporarily saved on the gateway machine. Once all the result is collected from the data source, it is forwarded to the cloud service (Power BI in our case).
How to Use Power BI Gateway?
It is very easy to get started with using Power BI data gateway in your setup by following the below steps.
Step 1: Download the Power BI gateway, depending on the type you want, and install it on your computer.
Step 2: Once you installed the gateway, configure it depending on your business environment.
Step 3: Add gateway admins to administer and manage the data sources along with network requirements.
Step 4: Refresh the local data source by using the on-premises gateway and keep the Power BI report up-to-date.
You can troubleshoot the gateway if there are any issues in connecting local data sources via the gateway.
Power BI gateway makes it easy to connect Microsoft cloud services to a local database behind the firewall in your organization. As every business doesn’t store their data on the cloud, it is necessary to have software like the on-premises gateway to help connect to the local data repositories.
Microsoft provides two types of gateways. You can opt for any one depending on the needs of the business. The article explains many things about the Power BI gateway, which can help you understand the gateway concept and know how it works with Microsoft services and data sources.