How to manually update your PowerPivot Management Dashboard

We have gotten lots of feedback about the new CTP3 PowerPivot Management Dashboard feature. Most folks are excited about the new reports and can see how the reports give you an unparalleled ability to ‘peek’ inside the running services. However the default “wait-a-day” update cycle can see a bit long. A day-long cycle period seems OK for long-term analysis of data, but for demoing the system and investigating how to use the capabilities work you might want a shorter cycle, or want the ability to refresh the data during the day. This post is all about how you do that.

First, you have to determine if the Usage file collection is long enough for you to wait. By default, it takes 30 minutes for your actual usage of the system (clicking here; clicking there) actually end up in a Usage file. If you can wait 30 minutes, then leave the system as it is. If you need usage information sooner, then you will need to tweak two settings:

1. As a property of the PowerPivot service application (there is a link to the service application(s) on the Dashboard page, top right corner), is the Query Reporting Interval. Its default value is 300 (every 5 minutes). This means that every 5 minutes the PowerPivot system service writes using information to the SharePoint Usage subsystem. If you want the interval smaller you can reduce it to every couple of seconds, but this means that there will be more information kept in PowerPivot database. You might reduce it to every 10 seconds if you want to see the data as soon as possible.

Set-SPUsageService –UsageLogCutTime 1

Now that the data is being logged into the Usage files(you can see these in the ULS folder), all you have to do is to manually run 3 timer jobs to get the data from the Usage files to the reporting database. To run a timer job manually, click on it and then click on “Run Now” button at the bottom of the page. Run these timer jobs in the following sequence: (Important: you must run these in this sequence!)

• SharePoint Usage Data Import  (this is run every 30 minutes by default) – this timer job moves the Usage data from the Usage files to the centralized SharePoint Usage collection database.
• SharePoint Usage Data Processing (this is run daily by default) – this timer job moves the Usage collection database to the PowerPivot database associated with the service application. 
• Microsoft PowerPivot Dashboard Processing (this is run daily by default) – this timer job actually processes the Management Dashboard workbook that holds the data for reporting. The reports and charts are built from this workbook.

As a “geek’ish” implementation note, the workbook itself is kept in a document library in the Central Administration web application:

There is a subfolder for each of the localized languages that PowerPivot supports (which is where the reports themselves are kept).

So . . . after all of this.

Now you can refresh the Dashboard page and you will see updated data/reports!
Again, if you can live with the 30 minute delay, then all you need to do is run those 3 timer jobs in the sequence specified and you can refresh the page.
All is golden!

Enjoy.

_-_-_ Dave