Timephased Capacity Reporting with OData

Catching up on the blog comments after a couple of weeks of vacation and post-vacation wheel spinning.  One of the comments that caught my eye was Bram de Vries’ comment on a previous reporting post:

Do you think I need to jump through all these kinds of hoops to create a report where timephased assignment work is compared with timephased capacity for (filtered) list of resources?

I admit, my initial response was that it should be quite easy.  Simply pull a couple tables together into PowerPivot and work up a report.  As I progressed through the mental exercise though, I found it to be a bit harder than I anticipated.  So Bram, I agree with you, there are a couple of hoops to jump through to get the standard timephased capacity report out of Project Online.

That being said, it is possible.  I’m not sure I’d want to do this in a real production environment, but I suppose it’s a good lesson.

The Challenge

So the challenge I set before myself was to see if I could create a stacked bar chart showing projects and capacity using OData.  Essentially, I am recreating the Resource Center view.

The first question you should be asking yourself is “Why? Why would I recreate out of the box functionality?”  The general answer I would give is because the Resource Center is dumb.  That’s not dumb as in it has a poor design.  No it’s dumb because it ignores nuances.  For instance, I might have a bunch of projects in discovery that I want to filter out of this view – or I might have cancelled and archived a couple of projects.  At present, there’s no real way to be selective about which projects appear in this view.

Hence, it’s actually quite common to develop custom views that emulate the out of the box Resource Center functionality.

Without OData…

Before OData, you would have had to write a query against the SQL databases to get this data.  There’re a couple of ways to do this, but the easiest method I’ve found is to basically treat Capacity as a project in its own right, i.e. to use a UNION ALL command to join project and capacity data.

That query would look something like this:

With OData…

The problem I found with OData is that it’s quite difficult to get the equivalent of a UNION ALL command to work.  Essentially what you have to do is to create a master table with all of the possible rows in your table, i.e. all of the resources and days, then map either Capacity or Demand to each of the respective rows.

That master table is tough.  I tried it in Excel 2013 with a reasonably powered PC, and kept running into performance limitations – probably because I was trying to create a table consisting of 100 projects X 140 resources X 365 days (in 2013), yielding a master table of approximately 5 million rows.  No matter how I tried it, I couldn’t get it to work.

Hence the hack.  What I realized is that I just needed to use Project Server and hack the source data by adding rows to the Assignments table that could be used to hold capacity calculations for each of the resources during each of the days I would like to analyze.  That reduces the total number of extra rows I’d have to create to 140 resources X 365 days = 51,000 rows – in addition to the existing assignment rows.

What’s the best way to create those rows in the Assignment table?  Well, by creating fake assignments for each of the resources.  To do this, I created a new enterprise project called “Capacity.”  On this project I assigned 1 task and set it to 1 year in duration.

I then assigned every resource in my resource pool to the task.

To keep these assignments out of the Resource Center, I go into the Resource Usage view and flag everything as Proposed.

Publish the project and fire up Excel.  Feel free to take a moment and peruse some of my other posts on reporting against OData if you’re not entirely comfortable with PowerPivot quite yet.

Building the Data Connections

At a minimum, we’re going to need the following three OData feeds:

• Resources:

http://demo/pwa/_api/projectdata/Resources()?$select=ResourceId,ResourceName

• Assignments (or Demand):

http://demo/pwa/_api/projectdata/AssignmentTimephasedDataSet()?$filter=TimeByDay ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=ResourceId,ProjectName,TimeByDay,AssignmentCombinedWork

• Capacity:

http://demo/pwa/_api/projectdata/ResourceTimephasedDataSet()?$filter=TimeByDay ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=ResourceId,TimeByDay,Capacity

You’ll note that I am filtering the timephased data to only give me 2013 data, as with OData, it’s important to take performance into consideration and minimize the sheer amount of data imported.

Building the Data Model

To build the data model, I borrowed a trick from this blog post by Kaspar de Jonge and created my own merge column.

For the AssignmentTimePhasedDataSet, I created a new field called “Merge” and set it to calculate with the following formula:

=[ResourceId]&[ProjectName]&[TimeByDay]

For the ResourceTimephasedDataSet, I created a new field, also called “Merge” and set it to calculate with a different formula:

=[ResourceId]&”Capacity”&[TimeByDay]

From there, it’s a simple matter of building the relationships between the three tables:

We need to build a custom column to swap out the work calculation for our Capacity project, and replace it with the appropriate Capacity calculations from the ResourceTimephasedDataSet.  I create a new column called “Work” in the AssignmentTimePhasedDataSet and assign it the following formula:

=IF([ProjectName]<>”Capacity”,[AssignmentCombinedWork],RELATED(ResourceTimephasedDataSet[Capacity]))

This formula displays the appropriate work column – except for the Capacity project, wherein it will pull the correct row from the ResourceTimephasedDataSet.  The RELATED function is basically playing the role of a Vlookup.

To minimize the charting overhead, I’ll also simplify the date field to render by month.

=[TimeByDay]-Day([TimeByDay])+1

…And Now the Report….

Back in Excel, create a PivotTable using the following settings:

..which will look like this:

Turn that into a stacked bar chart, right click on the Capacity bar and convert into a line chart, and you’ll get something like this:

Add some slicers and you’ll get something a bit nicer:

…although the Capacity line keeps reverting back to a bar whenever a filter is applied – which is kind of annoying.  This goes back to something I read in someone’s blog post while researching this, which is starting to resonate with me – and validate Bram’s comment from the beginning of this post: PowerPivot is really designed for the ad hoc analyst pulling data together periodically.  It still has some way to go before it’s an enterprise tool for creating stable repeatable reports.

After going through this exercise, my modified response to the initial comment: sure, it’s possible to do timephased reporting, but you’d be much better off skipping capacity and just sticking to the demand part of the equation.  That would be quite easy to do – and would require none of the hoop jumping I just detailed above.