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).
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.
I have a passion for crafting Business Intelligence Solutions for my user groups. My experience includes almost 15 years of SQL Server involvement with the last 12 years focused specifically on Business Intelligence, SharePoint, OLAP, SSRS, and Decision Support solutions. Currently, I am a Business Intelligence Architect in the healthcare industry, and I also teach database and analytics classes for Kennesaw State University, Southern New Hampshire University, and Reinhardt University. My education includes an MBA and an undergraduate in Accounting (yes I am a reformed accountant!), both from Kennesaw State University. I enjoy every day by trying to grow my faith and spend precious time with my family. I have been happily married to my wife of over 20 years, and we have two teenagers one who we home school with the help of a University Model School, Cornerstone Prep in Acworth, GA (cornerstoneprep.org). Our other child is a Construction Management major at KSU’s Southern Poly / Marietta campus. We are a soccer and Cross Country (XC) family who play, coach, and referee soccer or run for fun most every day. For several years, our family has volunteered (and played with the dogs and cats) at Etowah Valley Humane Society in Cartersville, GA.
MSSQLTips Awards: Champion (100+tips) – 2016 | Author of the Year – 2015 | Author Contender – 2014, 2016-2021
