Using a custom data refresh schedule in #PowerPivot for #SharePoint

A PowerPivot workbook deployed to SharePoint 2010 becomes extra powerfull because it enables you to use it as a “information source” for all other reports (wether they be reporting services report using “Odata feeds” or “Excel services” reports). One important feature to configure when you want to use PowerPivot as a “information source” is the feature to schedule data refreshes.

As some of you know that feauture is available, well… sort of. The “out – of – the – box” data refresh feature does not support intervals smaller then one day. I have several business scenario’s in which it is required that the information is near real – time (like every 5, 10 or 15 minutes).

Determined to find a way I consulted our local incompany SharePoint Guru and low and behold.. we found a solution. Here it comes.

First, getting lost

Being the “hackers” we are šŸ˜‰Ā we re-enginered the dll responsible for handling data refreshes (when I say we, I mean my colleague. I mostly sat next to him looking pretty). From my point of view it was like looking at a ping – pong game, hopping from one class to the other. After some minutes (we are of the impatient type) we thought we would never find it.

Then, someone got smart

During our hopping journey we did see calls being made to the database. My colleague suggested to have a look in the PowerPivot application database on the SQL Server… We found the holy grail!

We found several tables of which the following are usefull, at least to our knowledge:

  • DataRefresh.Items
  • DataRefresh.Runs

The “Items” table holds each published PowerPivot workbook. The “Runs” tables holds the PowerPivot scheduled refreshes. In short; when adding a PowerPivot workbook registered in the “Items” table to the “Runs” table with status “W” (waiting) it will be run the next time the SharePoint job scans the table.

So, guess what we did. We made a stored proc and a SQL job to schedule a custom data refresh every 10 minutes (which could be any interval since we use the schedule of the SQL job). The result is shown below:

Some pointers;

  • Keep the “Runs” table small. there is a maximum of records it can hold. I made sure that with every run it also made sure that thereĀ are maximum of 10 entries.
  • Make sure no other entry of the sameĀ PowerPivot workbook exists with status “W” or “Q”, if so cancel it and set its status value to “C”. (See theĀ DataRefresh.RunResults tableĀ for details).
  • The “Items” table not only contains the PowerPivot workbook entry but also meta data like the application server on which it is deployed. Copying this entry to the “Runs” table means “hard coding” on which application server the data refresh is run.

Do not try this at home, well at least not untested!

The solution described here isĀ NOT SUPPORTED BY MICROSOFT. Be aware of the fact that it is a “hack“. We are planning on implementing a more elegant and “supported” way using the SharePoint Object Model.

Advertisements

5 thoughts on “Using a custom data refresh schedule in #PowerPivot for #SharePoint

  1. Have you come up with a ‘supported’ way to accomplish this? Also, care to share your stored procedure/SQL job? Thanks.

  2. Pingback: Using a custom data refresh schedule in #PowerPivot for #SharePoint « madhavan

  3. Pingback: How to Refresh Data Models in Office 365 from On Premises Using Nintex Workflow – A Hybrid Approach | The White Pages

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s