By: Scott Murray | Comments (6) | Related: > Power BI
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; you want to make sure you are downloading the Enterprise Edition and not the Personal Edition.
There are a few caveats on the utilization of the Gateway.
- First, you need a Power BI Pro license / subscription in order to "retrieve data" utilizing the gateway.
- Second, when connecting to an Analysis Services dataset, the connection makes use of a concept called Effective User Name. This setup passes the user's credentials to the Gateway when a user attempts to refresh SSAS data or make changes to a visualization which require a "re-query". The Effective User Name is cross walked in the Active Directory to an AD user name and then compared to the permissions setup in your SSAS database.
- Third, all other connections use the authentication and user information input into the data source configuration.
- Finally, at the time of writing the Enterprise Gateway is still in "Preview" mode.
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.
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.
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.
Depending if you are signed in or not, you may get your organizations sign in screen which is similar to the below screen print.
Our next step is to configure the Gateway settings by defining a Gateway name and recovery key.
When we click configure, the Gateway is confirmed and added to the PowerBI setup for our account.
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:
- Server Name
- Database Name
- User Name
- Password
- Security or Authentication methods
- Advanced setting specific to just that data source type
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.
As illustrated in the below screen print, we are seeing a "Connection Successful" result.
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.
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.
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.
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.
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.
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.
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.
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.
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
- Power BI Personal Gateway - Connect to on premise data sources with Power BI Personal Gateway
Learn more about Power BI in this 3 hour training course.
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
