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

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”

image

  • Run the installer then click Next

image

  • After the installation completed click “Launch”

image

  • Click Next

image

  • Now you need to login to your PowerBI account

image

image

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

image

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

image

  • 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”

image

  • Now you can setup the schedule then click Apply. If you want more than one refresh just click on “Add another time”.

image

  • You can also see the refresh history by clicking on “Refresh History”

image

  • All done!

I’ll have a look at the gateway more closely in the future posts.

Leave a Reply