Skip to main content

Cloud

Power BI Refresh for both Online and On-Premises Data Sources

Great news!  As of July 7, 2015, data refresh is alive and working in Power BI v2!

Last month, the Power BI team activated the ability to refresh from online sources like Salesforce, Azure storage, and Google Analytics.  But now, you can refresh datasetsthat connect to on-premises sources as well.   All the common on-premises data sources supported by Power BI can be refreshed, including SQL Server, Oracle, MySql, Teradata, etc.  Same thing with the standard online sources — everything from Dynamics CRM to Salesforce to Azure to Google Analytics.
The only tricks:

  1. The datasets refreshed must be created in Power BI Designer files OR Excel workbooks with a data model (meaning: data has to be imported via Power Pivot or Power Query).
  2. To connect to on-premises sources — whether in conjunction with online sources or on their own — you must install and use the Power BI Personal Gateway.

Note: this meants that Excel workbooks with only worksheet data (e.g. data that did not get imported via the Power Tools) CANNOT be refreshed.
What wizardry allows data refresh from on-premises source?  It’s an evolution of the Data Management Gateway technology that was part of the Power BI v1 offering.  The new tool, dubbed the Power BI Personal Gateway, is now downloadable from the DL menu on your Power BI site.  It install directly on your desktop machine, and allows automated refreshes against on-premises data sources by way of the Azure Service Bus.  The Service Bus creates a secure channel from your machine to the Power BI service, and use of the Gateway tool avoids having to open firewall ports, etc.
Essentially, once you set up the Gateway, you can go to the Power BI site and configure refresh on any given Dataset.  You’ll need to authenticate through to the data source, but once that is done you can use either the “REFRESH NOW” or “SCHEDULE REFRESH” options in the Dataset menu to get either an instant refresh, or to keep your data up to date.   Scheduled refreshes can be performed multiple times per day (assuming you are on a paid account), and the service can be configured to send you emails about refresh status and failures.
The Power BI service is still in preview as of this writing, so head to PowerBI.com and check out this great functionality for yourself for free!
 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Andrew Tegethoff

Andy leads Perficient's Microsoft BI team. He has 16 years of IT and software experience with a primary focus on Enterprise Information Management solutions using the Microsoft Data Platform.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram