It’s been awhile that lots of us were waiting for this feature. And some of us like me just tried to build it in our way. I spent some time to develop something similar using OData in combination with IIS and Basic Authentication features. Well, it was sort of successful and unsuccessful simultaneously! I mean, I was able to refresh SQL Server data remotely, but, when it came down to refreshing the dataset uploaded into the cloud Power BI it just failed. It was mainly because of the method that Power BI uses to refresh data.

By the way, I’m glad to see that we are finally able to refresh an on-premises SQL Server database from Power BI website. Refreshing data is very crucial for every report and dashboard which is working on top of frequently changing database. So we need to be able to schedule a data refresh on the cloud. Yesterday Microsoft announced a new gateway specially designed for supporting data refresh for on-premises data sources as below:

SQL Server
Oracle
Teradata
IBM DB2
PostgreSQL
Sybase
MySQL
SharePoint List
SQL Analysis Services Tabular model (uploaded data, not live connections)
File (CSV, XML, Text, Excel, Access)
Folder
Custom SQL/Native SQ

As you see SQL Server is not the only one.

Installing Power BI Personal Gateway

It’s easy to install the Gateway. Just make sure that meet the following requirements:

The machine that you’re going to install the Gateway on it should be always up and running
You can NOT install the Gateway on the same machine as a Power BI Analysis Services Connector

If you meet the above simple requirements you’re good to go and install the Gateway. You don’t need to be an administrator.

Login to your Power BI account on www.powerbi.com
Click on download button to the top right of the page and select “Power BI Personal Gateway Preview”

Run the installer then click Next

After the installation completed click “Launch”

Click Next

Now you need to login to your PowerBI account

You should now be able to see the Gateway icon in your notification area

Double click on the Gateway icon to see if it’s connected

All done and you can refresh your data or setup a schedule for data refresh

How It Works

Let’s have a look at it more precisely with a very simple and common example. Let’s say you have created reports and Power BI designer which is using an on-prem SQL Server database. The Power BI Personal Gateway supports refreshing on-prem database connected through any of Power BI Designer, Power Query or Power Pivot:

The SQL Server instance that hosts your database is NOT installed on your machine
You installed the Power BI Personal Gateway on that computer
Remember, you don’t actually have to install the Gateway on the same machine which runs the SQL Server instance
You sign in to your Power BI account
You upload the pbix file you developed in Power BI Designer earlier
After the file successfully uploaded go to datasets
Click on the ellipsis button and click on “SCHEDULE REFRESH”