Marrying Server Data to a Resource Pool Org Chart
…still playing with the reporting options in Microsoft Office Project Server 2010 and Visio Services. This is a follow on post to our last story on how to create an org chart using Resource Pool data. In that post, I talked about how to export the resource data into the Visio Org Chart Wizard. I am still not sure why, in fact, someone would choose to do this, but one possible option would be to provide a reporting “hook” to hang all sorts of resource data on.
In this post, I’ll introduce some methods of getting data out of Project Server, and then connecting it to the org chart, thereby using the org chart as a navigation aid for resource reporting:
Our goal is to take a chart that looks as follows….
…and combine it with enterprise data. Easy enough, once you have the initial chart developed. To start out, we’re going to select the Data option in Visio. We’re going to point the connection directly to our reporting database in SQL.
Make sure to connect to the appropriate table within the database. In this case, we need resource data and will use the MSP_EPMResource_UserView table.
This gives us a list of all the fields within the table. We can opt to import all of them, or just some of them. Make sure to import the Resource Name and/or the Resource UID if you decided to use that as the primary identifier in the org chart development. Since most of the Resource tables refer back to the Resource UID, I would encourage you to use that field.
In this case, I am keeping things simple, and just importing three fields. Basically, we’re going to attach some demographic data to the org chart.
You’ll see the data show up in the External Data window at the bottom of the screen. Select Automatically Link to marry the SQL database with the org chart reporting structure.
Identify the unique identifier….in this case that is the Resource Name.
And you’ll see the chain link against most of the rows in the External Data window, showing that the system linked the database data dynamically to the Visio shapes. I can now click on any of the Visio shapes to display the Shape Data Window with our newly imported data.
Incorporating OLAP Data
Well, that’s relatively easy for nontimephased data. But what if I want to marry OLAP data with my chart? That’s a bit more complicated, but doable. The first question is of course, why? OLAP data is best served by importing into Visio using the PivotDiagram functionality. That will be a subject for another post. But let’s bypass this question for now, and just assume that we want to marry OLAP data to a flat org chart. To do this, we need to first export the data into a dynamic Excel worksheet, and then import into Visio.
First off, we open Excel and add a PivotTable. Add an external connection to provide the table data, and point the connection to the OLAP cube in Project Server.
The cube we are using is the MSP_PortfolioAnalyzer. I’ve added a calculated Utilization field to track how many hours are assigned to each resource – and perhaps to annoy any TOC consultants who may be reading this post.
Saving the worksheet to our local machine or to a SharePoint library, we now navigate back to the org chart we already created in Visio. Add a Data Link to the new Excel worksheet.
Select the columns to include.
The data is now imported into the Visio file. Now just run the AutoLink command to link the OLAP data to our org chart report.
And we now have OLAP data married to each of the resources identified in the org chart.
Presenting with Visio Services
Reports ain’t nothing unless they can be presented in an easy to use, easy to navigate fashion. Luckily, we can do that in SharePoint 2010 with Visio Services. In this case, I save the file as a Visio Web Drawing (.vdw) file.
And now I have an attractive, intuitive report, where users can simply select a resource and see dynamically updated data pulled from Project Server.