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:
- 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
Hi Dave, thanks for this post. I really need it. But i have a doubt.
In this post you explain how to manually refresh Management Dashboard. But there are any method or way for manually refresh Data Refresh? In other words, I want get Data “Now” from SQL Server Database to PowerPivot Woorkbook in SharePoint, but i don´t find to run Data Refresh “Now”, i have to choose between Daily, Weekly or Monthly.
Thanks!
Re: data refresh “now”
In CTP3 we don’t have this feature. The best workaround is to request a data refresh in the next 15min window.
In RTM, we will have this feature, but is a bit different than you describe. When you setup a schedule, for example, daily at 2am, you can also check a box that says “in addition, run it now” — in which case, you can do “run now” but only as an option to the schedule — namily you get the schedule + “run now”. We don’t have a “run now” by itself.
Thanks very much Dave!
Hi Dave,
I’m trying to get my Management dashboard up and running, but somewhere down the line a job fails. I have processed all the jobs you mention in your post, the all report “Last run time: 3/10/2010 10:45 AM”. But my Management dashboard reports “Last successful refresh run: 12/1/2009 1:07:49 PM”. Any idea where too look?
Thanks,
Kasper
Kasper: Like all things take a look at the ULS logs. It is our single source for issues like that.
I have a big problem with muy dashboard since I don’t have the “Microsoft PowerPivot Dashboard Processing” at alle even though I’ve configured “Usage Logging”. Even droipped my PP apllication and made a new one.. What am I missing ?? I’m going crazy
I am actually missing all the jobs used for PowerPivot usage. I got the “Data Refresh Job” though. My PowerPivot workbooks works fine but not the Management Dashboard
Bobby: By “missing all of the jobs used for PowerPivot usage”, you mean the SharePoint timer jobs? Have you enabled the SharePoint Usage capabilities? Including the PowerPivot events? It seems like you’ve might have missed one of the installation steps. Is this a “New Server” or “Existing farm” installation?
_-_-_ Dave
I’m having the same issue as Bobby. The usage logging is turned on with the PowerPivot Options checked, but the only SharePoint Timer Job created is the one for PowerPivot Data Refresh. I don’t have any of the others for updating the management dashboard. Any idea for how to add these manually or force them to be created?
Bobby and Will:
Boy oh boy. This is *the* day for this bug to pop up. We had a similiar bug report this morning.
I need you to email me (dwickert@microsoft.com) with the steps that you took as part of your installation (new or existing?). Did you ever do a repair or uninstall? We will need the detail.txt files in the installation log folder. You might have several with different timestamps, we will need them all with their timestamps (as part of the folder name).
Thanks.
_-_-_ Dave
Hi
Did you fin any solution to the problem with missing Power Pivot job definitions. I have the same problem. I only have the “Data Refresh Timer job”
Christian and I had an email exchange over the weekend. Unfortunately he had already re-imaged his VM so the installation logs are gone. However we will continue to look for users who are encountering this. We have not found a good repro yet. If this occurs to you please drop me a line (dwickert@microsoft.com) and we’ll get right to it. We need the detail.txt files in the SQL Server installer bootstrap folder.
Thanks.
Detailed instructions for what we need is located in this post — http://powerpivotgeek.com/2010/01/22/how-to-start-debugging-a-powerpivot-for-sharepoint-installation-issue/
Henry Schein Drugs http://iwatchfamilyguy.com/ – buy protonix online These side effects include but are not limited to headache, diarrhea, abdominal pain, nausea, dizziness, fatigue, sleep deprivation, and it may be associated with a greater risk of hip fractures. http://iwatchfamilyguy.com/ – pantoprazole without prescription
[...] Read more… Possibly related posts: (automatically generated)Customer Update 01/12/08 [...]
[...] This post was mentioned on Twitter by Thiago Zavaschi, powerpivot. powerpivot said: Learn how to manually update your PowerPivot management dashboard from the PowerPivotGeek: http://bit.ly/8ZTjYS [...]