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:
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:
- 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.