Blogroll

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.

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:

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.

8 thoughts on “Timephased Capacity Reporting with OData”

If you do get stuck having capacity in your report of this style, (the directors here swear by it) then you can always add a handy little macro to redraw that capacity line. I outlined the steps for this in my similar post (using a sql query instead of PowerPivot, becase that’s how I roll!) so I thought I would share. It basically triggers when you use the slicers or refresh the data, and keeps that line happy.

Cool solution, thanks for describing it! But I think I found a solution without the hack, using PowerPivot, oData and a macro to format Capacity as a stacked area and Work as stacked bars. I’ll send you the Excel sheet I created and hopefully you can check out the PP model without connecting to the website of my customer.

Instead of the Capacity hack use the ResourceTimephasedData odata feed to create the overall resource capacity by day (month/qtr)? Then you will have a very similar equivalent to the Resource Center graph without the extra hoops.

Hi – I’m interested in how you would get the capacity by Month without the hack as resources get added all the time. Can you help with steps within the context of the rest of this post? Appreciated, as I’m a novice odata-ite but actually got this to work (thanks Andrew!)

Interesting post. Is there any way to pull resource calendar exceptions into the capacity calculation. I’m trying to figure out a way to recreate the resource capacity data which takes into account resources vacation time.