In this article, I’m going to explain what a Power BI Data Gateway is and how to set it up. I’ll also cover the underlying architecture that the gateway uses and how to manage on-premise data using the Data Gateway.
Please note that this article is not an introductory course to Power BI and assumes that you have a fair knowledge of working with Power BI Desktop as well as the Power BI service. (https://app.powerbi.com)
The Power BI Data Gateway, as the name suggests, is a kind of gateway that acts as a connecting platform between the Power BI Cloud service and the on-premise datasets that are not on the cloud. For example, if you have set up your data sources on your local system or on a customer’s data center which is behind a firewall, you will need to install the Power BI Data Gateway for the Power BI Service to be able to connect to those data sources and fetch the data.
The Power BI Data Gateway is not only limited for use by Power BI, but it can also be used by the other Azure cloud services like the Azure Analysis Services, Power Apps, Azure Logic Apps, and Power Automate. All these cloud services can use the same data gateway without any other specifications required.
Data Gateway Architecture
As I have already mentioned, the Power BI Data Gateway is a kind of bridge between the on-premises data sources and the Power BI service available on the cloud. The gateway is a windows application that has to be installed on the same network as the other data sources are in. If you see the figure below, there are multiple steps that take place while querying through the data gateway. As you can see, there is also a clear distinction in the architecture between the cloud services and the on-premise data sources.
Let us now try to understand in a step-by-step manner, how the data processing is done using the data gateway.
- Any of the cloud services, like the Power BI Service, Power Automate or Azure Analysis Services etc. have the data model defined within them. This model also keeps the necessary queries that are required to refresh the data
- These queries, along with the credentials are first of all encrypted by the Power BI Cloud Service
- Once encrypted, the information is then passed on to the Gateway Cloud Service
- The Gateway Cloud service just analyzes the query and then puts the query into the Azure Service Bus queue for further processing
- It is the job of the Azure Service Bus to push the encrypted queries to the Application Gateway which is installed locally
- Once the information is received by the application gateway, it decrypts the source credentials and then connects to one or more data sources as defined by the connection string
- The query is then executed by the Application Gateway on the data source and the results are sent back
- As the Application Gateway receives the data from the data source, it then passes on the information back to the Power BI Cloud Service
- The cloud service finally processes the results and updates the visuals/datasets
Figure 1 – Data Gateway Architecture (Image Source: https://docs.microsoft.com/en-us/data-integration/gateway/media/service-gateway-onprem-indepth/on-prem-data-gateway-how-it-works.png)
Download and Install Power BI Data Gateway
The Power BI Data Gateway can be installed on a Windows PC or Server. However, it is not advisable to install it on a device that might be turned off, because in such a case the gateway service will stop, and the data refresh will stop. In order to install the gateway, please follow the steps below.
- Log onto the Power BI service and sign in with your credentials
-
Under the downloads section, click on Data Gateway
Figure 2 – Download Data Gateway
-
On the next page that opens, click on DOWNLOAD GATEWAY
Figure 3 – Download Data Gateway Button
- Once it is downloaded, open the file as an administrator
-
As the application starts, click on Next on the first page
Figure 4 – Data Gateway Installer
-
On the next page that appears, select the On-premises data gateway (recommended) and click Next
Figure 5 – Installation Mode
-
The application might take a few minutes to install
Figure 6 – Time to Install
- Once the application is installed, it will open the page to sign in to the service
Configure Power BI Data Gateway
Once the Power BI Data Gateway is installed, the next step is to configure it to connect to the Power BI service. Follow the steps below to connect the gateway to the Power BI Service.
-
As the page for sign-in opens, click on Sign-On
Figure 7 – Data Gateway Sign-in
-
Provide your email address that you use to login to the Power BI Service and click on Next. Please note that this user must have administrative rights in the Power BI service workspace
Figure 8 – Sign-in Credentials
-
Enter the password for the account and click Sign In
Figure 9 – Sign-in Credentials
-
In the following page, the message will appear as “Gateway was not found”. This means that the service was not able to find any gateway configured in our Power BI Service that connects to the PC on which we just installed the gateway
-
Just go ahead and click Configure
Figure 10 – Configuring the Gateway
-
Select Register a new gateway on this computer and click Next
Figure 11 – Registering the gateway
-
Provide a name for the gateway and an 8-digit recovery key and Click on Configure
Figure 12 – Registering the gateway
-
Once configured, the gateway will display a message saying it is online and now ready for use. You can click on Close
Figure 13 – Gateway Registered
- The gateway has been successfully installed on the PC and is now ready for use by Power BI and Power Apps as mentioned on the page
Now that the Gateway is installed and ready for use locally, the next step is to configure the same in the Power BI Service. Please follow the steps below.
- Log-in to the Power BI Service
-
Click on Settings Icon and then select Manage Gateways
-
You’ll see that the name that we specified while installing the gateway is automatically being listed under the cluster
Figure 15 – Data Gateway Cluster
- Once the cluster is available, the next step is to add a data source to it
-
Click on Add data sources to use this gateway
Figure 16 – Add Data Sources to use the Gateway
-
In the Data Source Settings page that appears, provide the name of the Data Source and select the type of the data source, which in our case is SQL Server
Figure 17 – Selecting the Data Source and Source Type
-
Once the Data Source Type is selected, provide the required details that are needed to connect to the source database in the local system and click Add
Figure 18 – Provide Data Source Details
-
If the connection is established, you’ll receive a message that says, “Connection Successful”
Figure 19 – Connection Successful
That’s all. You have now successfully set up Power BI Data Gateway and this can be used in any Power BI reports as per the requirements.
Conclusion
In this article, we have seen how to set up Power BI Data Gateway and configure the same in the Power BI Service. Data Gateway is essential if there are data sources that are available on-premises and not on the cloud. This data gateway acts as the bridge between the Power BI Cloud Service and the on-premise data sources through a secured network.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021