More Timephased Reporting with OData

I realized that yesterday’s post may have given the incorrect impression that you need to go through all sorts of mind bending contortions to get timephased reporting out of Project Online.  The reality is that you’d only have to limber up if you wanted to do reporting like I showed yesterday, i.e. a detailed view of all projects and capacity by resource and time.

image

The reporting gets a lot simpler when you’re looking for less data.

image

For example, if I don’t want to parcel out each project separately, I could use the same set of feeds I created in yesterday’s post:

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

…from there, I would need to add an additional query to get the TimeByDay dimension:

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

Put those together in PowerPivot, and you should have a data model that looks like this:

image

To simplify the charting process, I’ll add a field to the TimeSet table summarizing the data by month:

image

From there, it’s just a simple matter of creating a PivotTable and a bar chart:

image

Moral of the story: simplify the reports to reduce the effort required to create them.  This report was pretty easy to develop, but doesn’t have all of the individual projects displayed in the stacked bar chart.

To simplify things, I can add that detail as a second chart on the same worksheet using the data feeds we already created.  Overall, I’d argue that we don’t lose a whole lot in terms of experience by reducing the information density of the overall dashboard.

image

More Timephased Reporting with OData

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.

image

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:

image

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.

image

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

image

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

image

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]

image

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

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

image

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

image

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]))

image

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

image

…And Now the Report….

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

image

..which will look like this:

image

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:

image

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

image

…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.

Timephased Capacity Reporting with OData