Refreshing On-Prem SQL Server Database on Power BI using Power BI Personal Gateway

image 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

image