“Where are PerformancePoint ScoreCard comments stored and how can we access them?”
Last Tuesday evening I delivered a presentation, Summing up BI features in MOSS. There was a lot of discussion and questions around Reporting Services integration as well as PerformancePoint Server 2007 integration. One question asked was where comments (or annotations) added to scorecards via the browser and/or Dashboard Designer are stored, from the perspective of accessing and doing comment roll-ups (and no, it’s not a SharePoint list as someone suggested :-)). This is something I’ve also been pondering so jumped in earlier today to take a look. This post details my initial findings.
First off, for those of you reading this not familiar with PerformancePoint’s scorecard comments/annotation feature, here’s a brief overview.
When you design a scorecard in PerformancePoint’s Dashboard Designer, you can choose to enable comments. When you enable comments, end users with sufficient privileges will be able to add and edit comments to items within a published scorecard directly via the browser, which will look something like that shown below. Note the Microsoft data link; you can also add links to scorecard items when designing scorecards in Dashboard Designer.
The same scorecard is shown below in Dashboard Designer.
Comments can be enabled in scorecards in Dashboard Designer using the View Options, shown below.
Scorecard comments can be created and edited in both the Dashboard Designer and via the browser. The image below shows the Comments dialogue within Dashboard Designer. The main difference between editing/adding comments in Dashboard Designer and the browser is that in Dashboard Designer you can also add Links (hyperlinks) which are then exposed within the published scorecard.
Synching of comments between Dashboard Designer and a published scorecard is a two way process, i.e. comments added to a scorecard within Dashboard Designer are available in the published scorecard (once the scorecard is published to SharePoint). Comments added to a scorecard via the browser are available within Dashboard Designer.
So where are the comments stored?
As suspected, the comments are stored in the PPSMonitoring database on the SQL Server, in a table named Annotations.
Comments (and links) added to scorecard items are added to the database table as separate items. Where there are multiple comments added to an individual scorecard item, those comments are concatenated and each comment defined by a <AnnotationComment Guid> tag. Links added to scorecard items are defined by a <BpmPropertyHyperlink> tag.
There’s a whole bunch of references over on the MSDN site, in the Monitoring Server SDK Documentation. I’ve listed the ones below specific to the Comment members:
[General link]
http://msdn2.microsoft.com/en-us/library/microsoft.performancepoint.scorecards.annotationcomment_members.aspx
[AnnotationComment]
http://msdn2.microsoft.com/en-us/library/microsoft.performancepoint.scorecards.annotationcomment.annotationcomment.aspx
Exporting Scorecard comments OOTB
There doesn’t appear to be an out-of-the-box solution for exporting comments and/or rolling comments up on a per scorecard basis. I tried exporting a scorecard to Excel (using the Export to Excel option within the published scorecard’s Web part properties) but even though the exported spreadsheet maintained an active link back to the monitoring server, comments were not included in the exported spreadsheet.
I’ll keep ‘digging’ and maybe I’ll find something.