Power BI : Excel Auto Refresh from On Premise SQL Server Data Source in Office 365

  • Article

Hi! Let's propose this scenario:

You have an Excel Workbook that consumes data from a SQL Server Database in your network, like this diagram:

In this case, you live happily ever after because your desktop is in the same network of your SQL Server, so you just create your data connection, input the server name, database, credentials and you are ready to analyze your data! But now let's pull you off your comfort zone and move your excel into a SharePoint Online site under an O365 tenant, then you will see things this way:

This change quite a bit, doesn't it? Now you only see a web page that looks like an Excel Workbook. You can even change some things in your published workbook. But if we are now working in the cloud, how you think your workbook will now connect to your on premise data? Here is where comes the Power BI published OData feeds. If you are curious enough, this would look like this (in a very, very, simplified way):

What will happen, is that your published workbook will connect to a published data source, that will connect to your on premise SQL Server data using a gateway.

Things to take in mind:

  • You won't be able to refresh manually from the web page your workbook data. The refresh process is configured in the Power BI site, however, you can force the refresh on demand.
  • You will need to setup things in your on premise environment, meaning to install the gateway in your SQL Server or in a place where it is accessible.
  • At least one or your boxes in your environment needs access to internet.
  • By the time this article has been published there are limited data sources that are supported for the data refresh.

So, from a high-level perspective, in order to consume on premise data form an excel workbook that is published in a Power BI site, you have to:

  1. Create your workbook the old fashioned way, by creating your data connection from the excel workbook itself. Do not use Power Query nor the PowerPivot data connection tools.
  2. Remember, when finish the connection wizard, to check the Add this data to the data model so it will create the PowerPivot data model.
  3. Once you have your workbook showing your data, publish it to your SharePoint site.
  4. In your Power BI Admin Center, follow the instructions to create and install a new Gateway from this article
  5. Create a new data source, by copying the same connection string from your workbook, follow this article for more information.
  6. Go to your Power BI site, the one that is accessible from the same site where you published your Excel Workbook. Usually you will see it in the Quick Launch, if you can't see it, try "Site Contents":
  7. From the Power BI, enable Power BI feature from the ellipsis (...) menu in the bottom right of your just uploaded document.
  8. Once enabled, the same menu will now show you an option "Schedule Data Refresh". You will be redirected to the settings pages for that workbook:
  9. Setup the refresh schedule, and press the "Save and refresh now" button to force the refresh so you can test it!

Hope this helps, I haven't tested this with the others sources: Oracle, SharePoint Lists and Power Query... will try to find out more time for that!