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
- IBM DB2
- SharePoint List
- SQL Analysis Services Tabular model (uploaded data, not live connections)
- File (CSV, XML, Text, Excel, Access)
- 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”
- Now you can setup the schedule then click Apply. If you want more than one refresh just click on “Add another time”.
- You can also see the refresh history by clicking on “Refresh History”
- All done!
I’ll have a look at the gateway more closely in the future posts.