Happily, at my office we just rolled out our first MS/BI-stack dashboard project last week, using SharePoint 2010, PerformancePoint, Analysis Services, and SQL Server 2005 and 2008, on Windows 2008 R2. It was quite bumpy - needlessly so in some spots - but it works, looks great and offers excellent information to the business. Most important to me personally is that we now have all the infrastructure in place for the delivery of content through this channel moving forward, and it overcame a failed effort with a competing technology that we'd tried without success over the past two years.
Here's just a brief recap of what worked well and what presented challenges.
I love using SharePoint, and I certainly see its appeal. It really can be a great collaboration tool, one that enables business users with moderate technical skill to use a collaborative web site quite effectively. But man, I do not envy the brave souls who work as SharePoint admins. No doubt they will be in high demand for the foreseeable future. The thing is, it's so wonderfully approachable, with so many genuinely useful features on the outside, yet so ... um, "challenging" on the inside. So, while I rarely do this because I am geek enough to like just about any technology, I have actively distanced myself from doing any SharePoint administration or integration, frankly just because I find it so unpleasant. A few years ago I wrote some integration code in C# using the SharePoint API to merge data from SQL Server tables into SharePoint lists. That was enough for me.
The approach we took in this project was to have our small dev team trial SharePoint 2010 and the rest of the stack on some disposable VMs, then decide if there were features that were compelling enough to warrant an upgrade to 2010 from our existing MOSS 2007 platform. We were only interested in dashboards, at least out the starting gate, so we put PowerPivot/Gemini in a column marked Nice To Have rather than as a requirement. This plan worked out OK, the team deciding we did want to move to 2010, but what we missed at the start of the process was that all the problems we'd face would be deployment problems, not really features problems. It would have been great to have discovered this sooner.
We marched along and built out the Analysis Services cube and PerformancePoint graphs and charts with reasonable effort, most of it working in the dev environment. There were the expected ETL and data quality issues, all to be worked through. Toward the end of the project schedule, however, it became clear that we had essentially overlooked the area where we would have problems.
We try, in a modest way, to use best practice as far as having dev/test/prod/dr environments when doing development work, and to formalize deployment using change control and some level of repeatable process. And this is where things really got sticky: down to the wire, nearing the end of our project, we found it is nearly impossible to use that model and deploy changes from a dev environment of SharePoint / PerformancePoint to test and production. It wasn't something we saw coming, figuring that that would be among the least of our worries. Surprise! Six weeks, six people, Premier support from Microsoft, project delays, all manner of fun, and ultimately we had to go live with at best a risky, difficult workaround involving a wholesale deletion of the entire site collection and content database with each deployment. You apparently just can't move content from one environment to another and have it work. This definitely put a damper on things.
Resounding "Meh" for SharePoint. That was no fun.
Other quibbles were smaller.
Would the LastNonEmpty Member Please Stand Up
We are running Analysis Service standard edition in an effort to economize (none of our SSAS content at this point is large enough to warrant Enterprise), and that meant we ran into the same issue most people seem to hit: writing calculated members in MDX to substitute for the "LastNonEmpty()" aggregate function that is built into Enterprise for snapshot data. That wasn't so terrible, but none of us had a lot of experience with MDX and it's got quite a steep learning curve. The excellent Fast Track to MDX, and some hints and tips from kind forum users, ultimately got me to a working series of recursive functions. As fallout from this, though, we went live with much too granular data in our cube's snapshot fact tables - 270 million rows of snapshot information for measures that change only very slowly. On the up side, we do have a working design that will allow us to swap in a more appropriate cube soon, with sparser fact tables.
Thumbs way up for SSAS Standard Edition.
But I Don't Haz teh Details
PerformancePoint Services is pretty neat - but also has some gaps in it. I imagine they will be filled as the product matures, provided it keeps a wide enough audience and attention from MS to move forward. We solved most of the issues we had with the charts and graphs, save one, which proved a real pain point: there's a right-click context menu that users can access in the browser as they interact with the charts, and the content of that menu is a bit too "automagic." It apparently will see what the associated chart is plugged into on the back end and then make some assumptions about what options to present.
The issue we had was with an item called "Show Details" - it is an attempt to make an easy drillthrough to cell data behind the chart. The thing is, you can't get to cell data for calculated members (see the previous topic) because it's either not there, or the calculation in SSAS refers to some arbitrary collection of data that the server can't really be expected to decipher. We just needed to be able to disable that option in the UI, so that our end users would not be tempted to try it, get an error message, and then call the helpdesk or our team for something we never expected to work in the first place.
We never did find a solution to this issue, but I got to a workaround, ironically, by researching PerformancePoint forums posts about how to fix the Show Details option when it was broken. I found out what types of things people were complaining about not working, then purposefully went into the charts and did one of the things that those posters had complained about. That change indeed "broke" the Show Details feature and caused it to be grayed out. A hack, to be sure, and not very satisfying. The good news is it enabled us to go live.
Thumbs most of the way up for PerformancePoint scorecards and charts.