By: Scott Murray | Comments (2) | Related: > Power BI
Problem
How do I connect Power BI Online to on premise data sources?
Solution
Microsoft continues to rapidly develop and deploy tools within the Power BI application stack, of course there is the Power Query and Power Pivot tools in Excel. Furthermore, in a previous tip, Getting Started with Power BI Desktop, we described in detail the new Power BI Desktop (AKA PBID) that Microsoft has added to the Power BI tool set.
Currently Power BI Desktop connects easily to online datasources or content packs, but not to on premise datasources without the assistance of the Power BI Personal Gateway (I am a bit surprised that the team used the term Personal, as connections can be made to multiple sources that are not "so personal" in nature). Thus in order to connect to on premise (on-prem) data sources, you will need to utilize the Power BI Personal Gateway tool. The tool can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=47753.
Installing Power BI Personal Gateway
Once you download the Power BI Personal Gateway, you will need to install the tool on an on-prem machine. There are, of course certain restrictions on which machine you install the Gateway on. These restrictions include:
- Computer must be running a 64-bit Windows OS.
- The Gateway is only available for those accounts using PowerBI Pro.
- Computer cannot have the Power BI Analysis Services Connector installed too (the Power BI Analysis Services Connector is used to connect Power BI tools to Analysis Services databases / cubes).
- The administrative privileges of the user installing and running the gateway controls how it runs in the background and how it handles data refreshes:
- User has Administrative Privileges = Gateway runs as a service and user account does not need to be logged into machine as long as the service is running.
- User has Non Administrative Privileges = Gateway runs as an application and user must be logged into the computer for scheduled refreshes.
The install process is rather easy and normal; just read the instructions and agreements and then click "Next.. Next.. Next........ Finish. Some of the install is shown below.
Configure Power BI Personal Gateway
The next step is to configure the connection to your Online Power BI service on the Azure platform. As shown below, our first step is to sign in to our Power BI Web Service. The sign in may be a several step process which redirects you to your organization's Azure login.
After connecting to the Power BI Service, we must specify the credentials under which the service will run.
If all is successful, we will get a confirmation similar to the below screen print.
Furthermore, you will also see the Gateway showing up in the Status Bar.
Connecting to the Power BI Personal Gateway
Now we need to move to the Power BI website, http://powerbi.microsoft.com and create the connection to the Personal Gateway. You will of course need to login using the same PowerBI account/credentials you used during the Personal Gateway installation and setup. We will specifically be working with the Datasets section. For this example we will be using the Web Query Report dataset. This dataset (and the related Report) was uploaded from the Power BI Desktop application and queries data in the AdventureWorks database located on my laptop.
To complete the refresh connection, we will need to click on the Dataset, Web Query Report in our example, and click on the ellipse (3 dots) button to open up the dataset settings window. The settings window will open, and you will want to click on the Schedule Refresh option to complete the online setup.
If all the connections are setup correctly, then the Gateway Status will show as "online".
However, you may run into a problem if your Gateway is not "turned on" as shown below.
Or you may be connected to the gateway, but a problem exists with
the connection to the actual database. In the below example, I actually
purposely stopped the service; the credentials error message is a misnomer error. However, this condition can
easily be resolved by starting the service and then using the Edit Credentials link as shown in the
below screen print to reset the connection.
If we truly need to adjust the credentials, for a SQL Server connection either Window or Basic (Standard SQL Server Login) credentials can be used. These screens can also be used if the credentials account and/or password change.
Finally, we can set the refresh schedule for the dataset. Please note a couple items about the schedule:
- You need to "switch" the Keep your data up to date to "Yes". I found that option was not quite intuitive; just click the bar to set to yes--#1
- The Refresh Option available will be based on your account type. Since I am using the basic edition, I can only update daily --#2
- You need to add a specific time for the refresh to take place, otherwise the update will occur as 00:00--#3
- You can add multiple update times--#4
- Be sure to click Apply when finished setting the schedule--#6
Finally, you can add Cortana search capabilities and Featured or "default" Q & A questions.
We can always check the status by clicking the
Refresh History link. The Type column will note whether the
refresh was requested On Demand or Scheduled.
If you checked "Send refresh failure
notification email to me", then upon failure, you will receive a
notification similar to the below message.
You can manually Refresh the dataset, by going to the dataset settings and then selecting Refresh Now.
However, if a refresh is already occurring, a new refresh will not be allowed
Finally, Microsoft has recently released an Enterprise Gateway in preview or beta mode. This gateway will have expanded capabilities for monitoring and access control; please be on the lookout for a future tip on the Enterprise version of the Gateway.
Conclusion
This tip covered how to use the Power BI Personal Gateway to query and retrieve data from on prem data sources. The Gateway tool allows you to connect to datasources that you connect to "locally" or on-prem. The Gateway needs to be installed on a 64 bit windows machine, but can run either as a service or as an application. In order to complete the "connections" between Power BI online and the local datasets, the Gateway requires adding credentials to connect to both your Power BI account and the local data source. Once these connections are completed locally within the Gateway, you must move to Power BI online to finish the setup; connections to the gateway must be confirmed and then a Refresh schedule can be configured.
Next Steps
- Review all the Power BI tips
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
