Everything you need to know about the Power BI Gateway

When do you need a gateway and what is it?

If you’ve built a nice Power BI dashboard, you also want to have it refresh automatically. This is the only way you are guaranteed always up-to-date data. Automatically refreshing such a dashboard can take place when it is published to the Power BI service, Power BI’s cloud environment. This allows users to “consume” the dashboard, i.e. use it to view the correct and latest insights and figures. 

If you want to refresh a dashboard that uses data sources that aren’t in the cloud, you can’t access it from the Power BI service without a Gateway. So: 

You need a Gateway if you want to refresh data from on-premises data sources.

These local sources are technically referred to as “on-premises,” or simply “on-prem.” Examples of such sources include Oracle databases, MySQL data, and even an Excel file stored on a network drive.

For data sources that you can move yourself, like that Excel file, it’s best to store them in the cloud, for example, in a Teams environment. This way, you no longer need a gateway for that connection!

Gateways can be categorized into several types:

  • Personal or Standard mode: As the name suggests, personal mode is only for your own use. Standard mode can be shared with other users (and also offers more features).
  • Single gateway or a cluster of gateways: In this case, multiple gateways are combined, and queries are automatically distributed to optimize the load per gateway. If one of the gateways in the cluster is temporarily offline, your refresh will still be performed by the remaining gateways in the cluster.

Please note: the image 1 example illustrates the concept of gateways.

What do you need for a gateway? 

You need two things: software and a machine that is always connected to the on-premises resources. Simple really. The challenge lies mainly in identifying the right “place” of the machine in the architecture. Discuss this with IT and have them also install and configure the machine. 

Machine sounds very heavy and complicated, but it is usually nothing more than a virtual system, a virtual machine. 

 

How do you install a gateway?  

  • Install the desired gateway via https://powerbi.microsoft.com/en-us/gateway/. 
    Open the “On-premises data gateway” program and log in with your Microsoft account (Figure 2). 

Figure 2: On-premises data gateway 

 

  • Check that you can now find the gateway in the Power BI service, under Settings -> Manage connections and gateways -> On-premises Data Gateway (Figure 3). 

Figure 3: Control Gateway 

  • In the “Connections” tab on the left you can now set up the connections to the on-premises resources. Choose the connection type, enter the credentials, and so on. Another option is to go directly to the settings of the dataset and add the connection to the gateway there.
  • Did you already add the connection to the gateway itself instead of to the dataset? Then in the screenshot below (image 4) you still have to ‘map’ the gateway and dataset connection, or link them together. 
    Do you also want to make the gateway and connections available to others? Then add them via “Manage users”. 

Figure 4: Establishing a connection 

Why do we love gateways? 

Sometimes you just have to deal with on-premises sources. This could be legacy technology that has not yet been migrated to the cloud. So we just have to make do with that. 

And then a gateway is ideal, because it makes automatic refreshing possible. This saves you having to periodically open each report in Power BI Desktop, refresh it, save it, publish it and close it again. 

If the date selections are also cleverly arranged so that they automatically move with the times, you really don’t have to worry about it anymore. 

Why do we hate gateways?  

We prefer to avoid gateways. They add complexity to the architecture, the connections are not very intuitive to configure and you need IT support to get a machine available in the right place in the architecture on which you can run the gateway software. So we always move local files to a cloud environment, try to use API connections instead of local connections and encourage the transition of systems to the cloud. 

Are you having trouble updating your datasets? We are happy to support you! Together we will look at what the best architecture situation is for you, whether we should deploy a gateway, and how we can help you set up and configure in the Power BI cloud. 

We are happy to help you become the king of your own data! And your refresh processes. 

Interested, want to know more or curious about what else we can do for you? Contact us at info@datakingdom.nl

Steven Annegarn  

King Expert Officer @ Data Kingdom  

 

Steven Annegarn