Get Free Tips
MSSQLTips.com
Home » How to use the Power BI Enterprise Gateway
Power BI

How to use the Power BI Enterprise Gateway

written by Scott Murray 0 comments

Problem

How do I use the new Power BI Enterprise Gateway?

Solution

The Power BI Desktop tool and related website, http://powerbi.microsoft.com, continue their march toward a unified Business Intelligence Solution. New releases and updates continue to be rolled out almost monthly, and one of the newer releases in the Power BI tool set is Power BI Gateway – Enterprise Edition.

In the previous tip, Connect to on premise data sources with Power BI Personal Gateway, we discussed using the Personal Gateway to attach to On-Prem databases. However the Personal Gateway contains some limitations which include limited refresh options, limited immediate refreshes, and limited administrative methods for maintaining organization wide connections to various databases and data sources. That is where the BI Gateway – Enterprise comes onto the scene.

The Enterprise Gateway provides a central location for organizations to create and maintain connections to a sundry list of data sources and provide Power BI Desktop users with easy access to these shared data source connections. Furthermore, the Enterprise Gateway provides security administration to control which users can access these shared data sources via the Enterprise Gateway (of course, end user may have other methods of accessing the data).

You can download the Power BI Gateway – Enterprise from: https://powerbi.microsoft.com/en-us/downloads/ or https://go.microsoft.com/fwlink/?LinkId=698863

There are a few caveats on the utilization of the Gateway.

Installing and Using the Power BI Enterprise Gateway

After downloading the Enterprise Gateway, you can just double click on the *.exe file to begin the installation. As shown below, you may get some warnings about the install. The below warnings are concerned with installing on a laptop computer and how refreshes may fail or be slow. After reading the warnings, we can click Next.

Install 1

Next, you will need to tell the install where to install the Gateway, agree to the Terms of Service ( you read them right? ), and finally check or uncheck whether to send stats to Microsoft and then you can click Install.

Install 2

If the install is successful, then the below screen will appear. Our next step is to sign into your Power BI account to finish the configuration of the gateway.

Install Complete

Depending if you are signed in or not, you may get your organizations sign in screen which is similar to the below screen print.

Azure Sign in

Our next step is to configure the Gateway settings by defining a Gateway name and recovery key.

Config Gateway

When we click configure, the Gateway is confirmed and added to the PowerBI setup for our account.Gateway setup

We are now ready to add our data source by selecting Close & Add data sources button. Here is where we specify our data source name and data source type. Based on the data source type, various additional fields will appear, primarily things such as:

Once you complete the required details for the data source, you will click the Add button. You will notice that you need the data source details handy, as the details do not “prepopulate” like in SSRS when you are building a connection string.

Data Source

data source type

As illustrated in the below screen print, we are seeing a “Connection Successful” result.

DSN Complete

Our final configuration step is to tell the gateway who can access this newly created data source; this task is completed by simply clicking the Users table and then entering the user’s email address and clicking add as illustrated below.

Add Users

Making Adjustments to the Power BI Enterprise Gateway

If after we have configured the gateway, we need to make changes to it, we can make adjustments only on the Power BI website. We do that by going to the settings gear button and then select Manage gateway. As can be expected, we can have multiple data sources, but only one gateway.

Manage Gateway

However, as shown below, at the present time, only the user name and password are adjustable on the Manage Gateway data source setting screen ( make sure you click on your data source name, LocalAdventureWorks in the below example, to access these settings). Of course, you can switch to the Users tab to add additional users to the access list. We can also test our connections to make sure they are up and running by clicking the Test all connections button. If successful, we will get a Connection Successful message.Manage Gateway2

Add user

Similarly we can add other administrators to our Gateway, by clicking on the Gateway Name, ScottTestPowerBI_EnterpriseGW, in the below example. We then click on Administrators, and from that window, we can add via email address additional folks who can administer our Enterprise Gateway. An administrator has permissions to add and remove users and data sources.

Add Admins

Dealing with Possible Errors

Of course, all does not always work as we intend it to; during my lab testing, I experience two main errors. The first one, illustrated below, was a problem where I had the wrong server or database name in the data source connection. This information was corrected and then the error went away after I refreshed the example report.Cannot connect to database

The second error, shown below, occurred when I attempted again to refresh the dataset on a report (the first screen print is the full screen, but is a bit small, while the second and third illustrations are zoomed in versions of the error). In the below situation, the Power BI Enterprise service had inadvertently stopped on my local machine. I restarted the service, and then I was able to successfully interact with the report.Offline full

Offline0a

Offline 1

PowerBIService

Finally, the one item that certainly stumped me for a little while was how do I tell a Power BI dataset or report that I publish to the Power BI website to use an Enterprise Gateway data source? The magic is in the black box as this is a direct quote from Microsoft: “Matching your Power BI Desktop connections with a gateway. The connection string configured in the Power BI Desktop file needs to match a data source already configured in the Power BI service. When a match is found, it will use the gateway that the data source belongs to.”

That means that upon publishing a Power BI document to the PowerBI site, if your documents data source matches an already setup data source on the Enterprise Gateway, the published dataset and / or report will use the Enterprise Gateway for its connection without any user intervention. However to make this work there is one key option that you need to select when creating a new Power BI Desktop dataset. After selecting Get Data in Power BI Desktop, you will then select the tables you would like to include in the model. Next, you will get the below Connection Settings option screen; to allow the Enterprise Gateway to be used, you must select DirectQuery on this screen.

Direct Query

Now after publishing the “Direct Query” report ( called HR List2 in the subsequent example ) to the Power BI website, if we look at the data set, we can see it says it is using Direct Query that does not require a scheduled refresh. Additionally, when we go to Dataset Settings, it shows that the Enterprise Gateway is being used and that you cannot adjust the database settings.Database settings

Using Enterprise Gateway

Conclusion

In this tip, we covered installing and maintaining the Power BI Enterprise Gateway to retrieve data from many data sources. The Enterprise Gateway is an organization wide tool used to set company level connections to on premise data sources. The Gateway is installed as service on a machine ( server class recommended ) and is the link between Power BI datasets / reports and the On-Prem databases that house the data needed for these reports.

Next Steps

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide