Power BI In A Jiffy: Schedule Data Refresh

Schedule Data Refresh allows Windows Azure SQL Database, OData and SQL Server data sources configured in Power Pivot to be refreshed. Any of the data sources that are retrieved via Power Query are not supported for Schedule Data Refresh. Any WASD, OData and/or SQL Server database retrieved using Power Query then added to the Data Model (Power Pivot) cannot be refreshed either. That’s the “in a jiffy: part. The rest of the post is a walk through of the Schedule Data Refresh feature.

As Power BI features are updated fairly frequentlly, the following walk through is valid at the time of writing. Future enhancements will be followed up in newer posts.

Schedule Data Refresh Feature

I believe this is a new addition to Power BI that I might have missed recently. So, I decided to check it out a little bit a few minutes before my presentation at SQL Malibu on Feb 12, 2014 on “Building Your Myth Busting Lab With Power BI“. When preparing for this presentation, I purposely left out any specific Data Sources, Data Management Gateway and Schedule Data Refresh features as I know that the Power BI Product Team is releasing new features and fixing bugs quite frequently. The cloud offering makes it possible for them to do so.

The features around the “Data Management” side part of it was OK-ish a few months ago and it has been great, because it means that the Product Team has lots of opportunity to shape the features to meet our needs through feedback that the public give them from their preview / trial offerings.

Now that Power BI is in GA in most countries (except for Australia, where I am), I think it’s a good idea to look into Scheduled Data Refresh. When I first read the article about Data Refresh, I initially thought this would also work for Power Query queries that have been added to Data Model. I was mistaken.

Data Sources in Power Pivot

Only Windows Azure SQL Database (WASD), SQL Server and OData data sources created in Power Pivot are supported for scheduled data refresh. If you are using SQL Server database, you will need to make sure that you configure Data Management Gateway and that the server is up and running at the scheduled data refresh time. Below is a snapshot of Power Pivot Data Sources that I have scheduled for refresh, excluding the Power Query one (as it doesn’t work).

If you are accessing a local database (presumably for testing), make sure that you fully qualify the name of the server, i.e. <Server Name>\<SQL Server Instance name>. If you use a shortcut such as “.\<SQL Server Instance name>, the Data Management Gateway will not be able to resolve the name and result in an error.

 

Enabling Schedule Data Refresh

Once the workbook is uploaded to Office 365 and enabled, you can schedule data refresh as shown below from the Power BI Site.

You can select the data sources that you want to schedule the data refresh for. Please note that in my example, I also have Power Query connection to my local SQL Server database. The Power Query data source cannot be refreshed. So, I’m only enabling the OData, WASD and SQL Server data sources for scheduled data refresh. At the time of writing, you cannot schedule each data source at different time. So, either they are scheduled for the same time or not excluded in the schedule at all.

Once you set the data refresh schedule, you can click on “save and refresh report”. If the report has not finished refreshing and you click on it again, it will come up with an error message similar tot the following:

Schedule Data Refresh History

If you go to the History page, you can check the status under the “Next Refresh” line. Below is a screenshot of history log after a few on-demand and scheduled refreshes. The list is quite comprehensive and lets you know which part of the data sources that cannot be refreshed. When the current data source hit an issue during refresh, the rest of the data sources enabled for refresh in the workbook will not be refreshed.

Email Refresh Failure

In the Settings, you can also configure one or more email address to send refresh failure notifications to. This send email feature is definitely handy. The error notification sent by email is similar to the following.

What could be improved?

1. The ability to configure different data sources in the workbook at different times or different refresh rate. This can be a double-edged sword though. Data source synchronisation is never easy.

2. Lower latency for status update to reflect the refresh process. At the time of writing, I find that I need to wait for about 10 – 30 minutes before I find out that the 30 second refresh is successful.

3. When the data source names in Power Pivot have been updated, the Schedule Data Refresh should ideally reflect that. At the time of writing this doesn’t happen at all.

Do you have your own suggestions for improvement / bugs to report? Please comment below and I will do my best to pass them on to the Power BI team. Alternatively, you can write to them in the Power BI forum.

Hope this walk through has been useful.

Wrap Up

In summary, Schedule Data Refresh allows users to refresh workbooks in Power BI with the latest data at a defined date / time as well as on-demand. You can schedule all the data sources in the workbook to be refreshed or only a selection of them, for the same schedule date and time. Although the feature is a little simple at the moment, it is much anticipated to match its cousin’s, on-premise SharePoint’s “automatic refresh Power Pivot“.

Further Reading

Keeping Your Reports Up to Date with Scheduled Data Refresh by Power BI Team

Automatically Refresh PowerPivot Data in SharePoint on Technet

Announcing the General Availability of Power BI for Office 365 by Power BI Team