Aveek Das
Managing the Data Gateway

Setting up Power BI Data Gateway

March 19, 2020 by

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.

  1. 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
  2. These queries, along with the credentials are first of all encrypted by the Power BI Cloud Service
  3. Once encrypted, the information is then passed on to the Gateway Cloud Service
  4. The Gateway Cloud service just analyzes the query and then puts the query into the Azure Service Bus queue for further processing
  5. It is the job of the Azure Service Bus to push the encrypted queries to the Application Gateway which is installed locally
  6. 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
  7. The query is then executed by the Application Gateway on the data source and the results are sent back
  8. As the Application Gateway receives the data from the data source, it then passes on the information back to the Power BI Cloud Service
  9. The cloud service finally processes the results and updates the visuals/datasets

Data Gateway Architecture

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.

  1. Log onto the Power BI service and sign in with your credentials
  2. Under the downloads section, click on Data Gateway

    Download Data Gateway

    Figure 2 – Download Data Gateway

  3. On the next page that opens, click on DOWNLOAD GATEWAY

    Download Data Gateway Button

    Figure 3 – Download Data Gateway Button

  4. Once it is downloaded, open the file as an administrator
  5. As the application starts, click on Next on the first page

    Power BI Data Gateway Installer

    Figure 4 – Data Gateway Installer

  6. On the next page that appears, select the On-premises data gateway (recommended) and click Next

    Installation Mode

    Figure 5 – Installation Mode

  7. The application might take a few minutes to install

    Time To Install

    Figure 6 – Time to Install

  8. 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.

  1. As the page for sign-in opens, click on Sign-On

    Data Gateway Signin

    Figure 7 – Data Gateway Sign-in

  2. 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

    Signin Credentials

    Figure 8 – Sign-in Credentials

  3. Enter the password for the account and click Sign In

    Signin Credentials

    Figure 9 – Sign-in Credentials

  4. 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

  5. Just go ahead and click Configure

    Configuring the Power BI Data Gateway

    Figure 10 – Configuring the Gateway

  6. Select Register a new gateway on this computer and click Next

    Registering the gateway

    Figure 11 – Registering the gateway

  7. Provide a name for the gateway and an 8-digit recovery key and Click on Configure

    Registering the gateway

    Figure 12 – Registering the gateway

  8. Once configured, the gateway will display a message saying it is online and now ready for use. You can click on Close

    Gateway Registered

    Figure 13 – Gateway Registered

  9. 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.

  1. Log-in to the Power BI Service
  2. Click on Settings Icon and then select Manage Gateways

    Managing the Data Gateway

  3. You’ll see that the name that we specified while installing the gateway is automatically being listed under the cluster

    Data Gateway Cluster

    Figure 15 – Data Gateway Cluster

  4. Once the cluster is available, the next step is to add a data source to it
  5. Click on Add data sources to use this gateway

    Add Data Sources to use the Gateway

    Figure 16 – Add Data Sources to use the Gateway

  6. 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

    Selecting the Data Source and Source Type

    Figure 17 – Selecting the Data Source and Source Type

  7. 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

    Provide Data Source Details

    Figure 18 – Provide Data Source Details

  8. If the connection is established, you’ll receive a message that says, “Connection Successful

    Connection Succesful

    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.

Aveek Das
Latest posts by Aveek Das (see all)
PowerBI

About Aveek Das

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interest are SQL Server, SSIS, and SSAS. In his leisure, he likes to keep his skills sharp by writing articles on his blog and other forums. He is also an amateur photographer and hops around to do street photography. You can find him on LinkedIn View all posts by Aveek Das

14,815 Views