Porting the Cumulative Milestone Report into OData (Part 2)

In this post, we’ll create the appropriate data connections to pull the data from Project Server OData into Excel 2013.  To do this, we first need to do a bit of analysis on the required data.  As we already have a working version of the report using SQL queries, we can break down the technical requirements relatively easily.

image

Establishing the Dimensions

First off, we need to identify how we’re going to slice and dice our data, and then ensure that we have that data available to us.  For this report, we’ll ensure we have the following dimensions available:

  • Project Name
  • Project UID
  • Total Date

Total Date is a field that we will create that displays the beginning of the month for any given date.  In this case, we have two data sets that we need to map to the same month: the task baseline finish date, and the task forecast finish date.  Essentially, we’re going to count how many tasks were baselined to finish in any given month, and then compare that to how many tasks are forecasted to finish in that month – as well as any tasks that actually completed in that month.

If you’re tracking your schedules more granularly, you may want to modify the Total Date field to the beginning of any given week or day within the target time span.  (For example, when I was last tracking an ERP go-live schedule, we modified our reports to group everything to the day.)

Each of the dimensions is captured from an OData feed.  Below, I’ll show how I developed the feed in LINQ, and then what the resulting URL was.  As I mentioned in the last post, you can skip the LINQ development and simply modify the data in Excel PowerPivot, but it was quicker to just add the filters and select the fields in the query itself.

Project Name

Let’s create a table with the project name.  We can use this field to filter our report at the project or program level.

Here’s the LINQ query….

from p in Projects
select new
{
    p.ProjectId,
    p.ProjectName
}

…which, when dropped into LINQPad, yields the following URL:

http://demo/PWA//_api/ProjectData/Projects()?$select=ProjectId,ProjectName

Total Date Dimension

We also need the first day of the month within which any work may occur.  We can do that using the TimeSet feed.  That contains a list of the days on which work may be scheduled within Project Server.  I filter out every value but the beginning of the month.

from ts in TimeSet
where ts.TimeByDay.Day == 1 && ts.TimeByDay > new DateTime(2011,1,1) && ts.TimeByDay < new DateTime(2015,1,1)
select new
{
    ts.TimeByDay
}

…and the URL:

http://demo/PWA//_api/ProjectData/TimeSet()?$filter=day(TimeByDay) eq 1 and TimeByDay gt datetime’2011-01-01T00:00:00′ and TimeByDay lt datetime’2015-01-01T00:00:00’&$select=TimeByDay

Establishing the Measures

We now need to add two more data sets to provide the baselined finish dates vs. the current forecasted finish dates.  We need to split these up into two data sets because we may have had a task that was baselined to complete in March, but is now scheduled to complete in April.  Hence, we need to have the task show up in both months in the correct columns.  In SQL, we could handle this using a UNION command.  With PowerPivot, we’ll take a slightly modified approach.

Forecast & Actual Finish

Forecast & Actual Finish are fairly straightforward.  We’ll get them from the Tasks table.

from t in Tasks
where t.TaskDuration == 0 && t.TaskFinishDate != null
select new
{
    t.ProjectId,
    t.TaskId,
    t.TaskFinishDate,
    t.TaskActualFinishDate
}

http://demo/PWA//_api/ProjectData/Tasks()?$filter=TaskDuration eq 0M and TaskFinishDate ne null&$select=ProjectId,TaskId,TaskFinishDate,TaskActualFinishDate

Baseline Finish

The Baseline Finish field is treated a bit differently in OData than in SQL.  In SQL, I have a nice table with all of my task data to pick and choose from.  In OData, the baseline data is pushed off into a table by itself, i.e. I have one field for BaselineFinishDate – and 11 different rows for Baselines0-10..  This necessitates adding a filter to only pull baseline data for Baseline0.  For the moment, I am filtering for milestones by only selecting those rows with a baseline duration of zero.

from tb in TaskBaselines
where tb.BaselineNumber == 0 && tb.TaskBaselineDuration == 0
select new
{
    tb.ProjectId,
    tb.ProjectName,
    tb.TaskId,
    tb.TaskBaselineFinishDate
}

http://demo/PWA//_api/ProjectData/TaskBaselines()?$filter=BaselineNumber eq 0 and TaskBaselineDuration eq 0M&$select=ProjectId,ProjectName,TaskId,TaskBaselineFinishDate

Add each of the OData feeds to Excel through the Data > Add Other Sources option:

image

When prompted, select the option to Only create the connection.

image

Next up….creating the data model in PowerPivot.

Advertisements
Porting the Cumulative Milestone Report into OData (Part 2)

One thought on “Porting the Cumulative Milestone Report into OData (Part 2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s