Big Data

Building an enterprise 'resource usage' view with Project Server's Portfolio Analyzer

Microsoft Project 2002 Pro features a Portfolio Analyzer that gives you access to rich OLAP data via Microsoft Excel Pivot Table and Pivot Chart functionality. Here's how to build a resource usage view with an enterprise scope.

The Resource Usage view in Microsoft Project has always been a useful place to see which tasks a resource is working on within a project. It’s not just a list of tasks. It provides the project manager with a time-scaled view of data like Work, Actual Work, or Availability for each task in the project. These data points are broken down by day, week, or month to give a more complete picture of how resources are being used across the project and across time.

Project Server used with Project 2002 Pro stores all the projects for an organization in a central database. This central storage invites cross-project comparisons and reporting about what resources are doing across all the projects. The best way to access this information would be through a view similar to the Resource Usage view, except that instead of being confined to one project, it would show what each resource is doing across the entire database of projects.

Because of the vast customizability of Project Server to more accurately model different organizations, there are no “out of the box” reports built in. But it does provide something called the Portfolio Analyzer, which gives you access to rich OLAP (Online Analytical Processing) data via Microsoft Excel Pivot Table and Pivot Chart functionality. Using the Portfolio Analyzer, we’ll show you how easy it is to build a resource usage view with an enterprise scope.

For this project to work, your machine must be running Microsoft Office XP. This will ensure that you have the right Office Web components installed for building the OLAP-based Portfolio Analyzer view. You must also have access to an account with administrator privileges.

Creating a new Portfolio Analyzer View
Our first step is to create a new view. Log into Project Web Access with an account that has administrator privileges. Click the Admin link at the top of the screen; then, click on the Specify Views link on the left navigation bar. Figure A shows a section of the Specify Views screen.

Figure A

Next, click the Add View link and select the Portfolio Analyzer option. You’ll need to enter a name for your new view and decide if it should display the chart, table, or both. Figure B shows these options. For this view we’ll need only the Pivot Table.

Figure B

The fun part
Now we can actually build the view. Figure C shows the canvas on which we’ll create our view. The Field List gives us access to the elements we can place on the chart. Figure C also shows the different areas of the Pivot Table: the Filter area, the Column and Row areas, and the Totals or Detail area.

Figure C

Our first step will be to drag the Work field out and drop it into the Totals area of the chart. When you do, you’ll see the total number of hours of work assigned to all the resources, across all the projects, for all time periods. In our example it is 249,801 hours, as seen in Figure D.

Figure D

Now we can “cut” this total into rows. To do so, we need to drag the Resource element into the Row area of the chart. At this point the view will show how the 249,801 hours are divided among all the resources in the Enterprise Pool. Figure E shows the chart with the Resource element added.

Figure E

We can add a further dimension that will allow us to see, for each resource’s hour totals, how they break down across the projects to which they are assigned. To do this, we just drag the Projects Name element and drop it just to the right of the Resource element in the Row area of the chart, as shown in Figure F.

Figure F

You can see how our view is shaping up. For example, in Figure E, we could see that Aaron Con was assigned to 840 hours. Figure F shows that those 840 hours are for two different projects.

All we have left to do is add the time element to the view. We do so by dragging the Years element from the field list into the Column area of the chart. We can then define which date elements to include. Clicking on the small down arrow on the right of the Years element shows us the breakdown of the time elements. For our example, we can uncheck the 2001 and 2003 elements to show only the current year. Figure G shows this in detail. We can even uncheck the Q1 box for 2002 to show only the last three quarters of the year.

Figure G

Clicking OK here will show us the complete Enterprise Resource Usage view we have created. Figure H shows a view that gives us the breakdown of Work hours by time period for all the resources in the Enterprise pool, broken down by individual project name.

Figure H

Notice that since we’re displaying Work values for only the last three quarters of 2002, the Grand Total of Work for Aaron Con has dropped to 444 hours. We can also see that the bulk of this work is in Q3 for the GC320001 project. This project accounts for 360 hours of Aaron’s 444-hour total, while the remaining 84 hours occur in Q4 on the QPX 6000 project.

And there you have it. The basic view is built. At this point, you can add other fields within this framework if you want. Figure I shows what the view would look like if you decided to include the Actual Work field. This figure shows the time element filtered to show Q1 of 2002 and displays the time down to the month.

Figure I

With this view, we can see another interesting point: While Aidan Delaney was assigned to 644 hours of work for February, only 112 hours of Actual Work was recorded for that same month. This kind of problem would have been very hard to see using Project 2000. To do so would have meant building a very large consolidated project containing all the projects in the organization. With Project Server, however, the data driving these reports is stored in an OLAP database, so the process is very fast.

In future articles, we’ll cover how to create custom calculations and how to do charting against the Portfolio Analyzer data.

Editor's Picks