It would seem to be BI Week in the Project Server blogosphere (see here and here, with an excellent recorded webinar on the topic by Marc Soester here). I guess that’s kind of like Shark Week on the Discovery Channel, but thankfully without the blood and rotting fish. Seeing as I have been spending a fair amount of time putting together a BI presentation of late, I figured I’d add my contribution to the discussion.
The 2010 BI Story
My initial thoughts are that Project Server 2010 reporting features have made great strides at focusing on what Project Server does best, i.e. enable data input, processing and management. Much of the reporting baggage (with perhaps some notable exceptions) has been divested into specific report authoring tools like Excel, Visio, and SQL Reporting Services, which dare I say do a much better job of data representation than the Data/Portfolio Analysis views have in the past.
So it looks like the Project Team has taken some of Jim Collins’ hedgehog concept to heart and incorporated that as a design into Project Server. Get rid of some of the reporting features and leverage SharePoint’s Insights offering to surface data in a rich way. (Did I say rich, man, phew there’s a lot to the BI offerings). Then incorporate all of that as part of the out of the box Project Server and SharePoint package. We now can author in Excel or SSRS and aggregate through PerformancePoint or even mash things up using the REST.API.
Needless to say, report authoring will probably increase its prominence in the Project Server consultant’s skills repertoire. Not that it wasn’t critical before, but before, a lot of the reporting could be farmed out to specialized technical resources. Now, there is no need to send it out to other resources. With the latest BI tools available, anyone with the requisite desktop tool skillset can generate effective, professional looking reports. (Note to the Microsoft training community: now more than ever before would be a great time to market single day advanced reporting classes in Visio, Excel, and/or ReportBuilder)
So now that Data Analysis is no longer shipping with Project Server, it seemed like it would make sense to see how we could recreate some of the popular reports we all know and love in 2007, and port those over to some of the offerings in 2010.
Following is a rough proof of concept exercise in developing a resource report in ReportBuilder v2. Note that v3 should be coming out shortly, and I am sure that will have even more bells and whistles – and be even easier to use.
The first lesson in 2010 is that with the rich offerings available for reporting, you need to make a decision as to which reporting tool is most appropriate for your needs. I could easily generate the same report in Excel, but in this case I plan to use SSRS. (That, by the way, is generally the Achilles’ heel of any ReportBuilder demonstration to a nontechnical audience….the question will inevitably come up, “Why don’t you just do that in Excel?” – which speaks to the strength of Excel and Excel Services more than any weaknesses on the part of SSRS.)
The general guideline is to use SSRS over Excel Services when the reports need to be scheduled and distributed in some off-line asynchronous fashion. For better guidance, refer to the now soon-to-be ubiquitous Microsoft BI 2010 poster.
Starting out, let’s take a snapshot of our goal. This is a Resource Capacity report from Data Analysis in a 2007 environment:
Building the Report
To recreate this as an SSRS report in 2010, I will first open ReportBuilder, and create a new blank report. For this report, we will choose a matrix.
Now I need to configure the appropriate connections. As you can see, I select Analysis Services from the drop down, then input my Analysis Services server details. So far, so easy.
Pick the appropriate cube. In this case, we want the MSP_Portfolio Analyzer cube. This is probably a good point to note that we are assuming you already have Project Server building cubes. This is probably also a good point to note that Project Server can generate a database and a set of cubes for each department configured, as opposed to 2007, where we were limited to the set of 14 cubes for each Project Server instance.
We’ll need to select the appropriate fields for inclusion in our report.
Now we assign the fields to sections of our table.
Turn off the subtotals as not relevant to today’s exercise.
Format the report.
Run the report to review the results.
…and post the results to SharePoint server for consumption in a Webpart, or incorporation into a PerformancePoint dashboard.
And that’s it. Total time spent, about 10-20 minutes.