Capturing Custom Timescaled Data in Project Server (Part I)

Well, it’s that time of year again, the down time between the solar and lunar new year festivities when we bid farewell to 2011 and welcome to the year of the water dragon.   This year, as I attempt to forestall getting back into “serious” work, I figured that I’d kick off the blog with a return to the basics and a look at reporting.

image_thumb[1]

Seeing as I’ll be presenting on Earned Value at the upcoming Project Conference, this next series of posts combines EVMS and reporting into the same concept.  The general gist is how to capture timescaled data within the Project Pro client using a spare baseline cost field.  This is a technique that I had heard about from other MVPs, but had never tried myself.  When a client request popped up, I figured I’d take some time to see if I could figure out how it works.

The Challenge

So first off the challenge:  we’re implementing Earned Value techniques in conjunction with Microsoft Project Server at this client, and I’d gotten to the point of developing a couple reports in Excel Services.  When it came time to generate a timescaled EVM curve, I realized that Project Server doesn’t seem to actually maintain timescaled EVM data anywhere.  Essentially that means it’s almost impossible to generate the report below  from the data stored in the enterprise databases.

image

Just to throw a quick primer on EVM terminology out there, note that Microsoft Project uses the DOD terminology for EV calculations, hence…

  • EV = BCWP
  • PV = BCWS
  • AC = ACWP

…which I’ll be using for the rest of these blog posts…

From an enterprise data perspective, we can generate a similar graph using BCWS = Baseline0Cost, and ACWP = Actual Cost – both of which are stored in the Project Server databases and readily available for reporting purposes. 

The challenge here is to copy the timescaled BCWP data from the BCWP row in Project Pro to the Baseline10Cost row – thereby making it available for enterprise reporting purposes.

image

The same concept could be applied to any number of other challenges, for instance capturing snapshots of milestone finish variance, or snapshots of EAC calculations on each status date.  The possibilities are endless.

Enterprise Baseline Mechanics

So I sat down and wrote a macro to copy BCWP to the Baseline10Cost field.  Unexpectedly at that point, I ran into a significant snag….it turns out that I didn’t quite understand how enterprise baseline data is saved.  Hence, I had to take a quick detour to figure that out before coming back to the issue at hand.

The key to understanding baselines in Project Server is that baselines are stored in two different data sets.  The first data set consists of the scalar data.  That is the data on the left of the Task Usage view.  (I should point out that I really liked the definition of “scalar” that I found on the Web….”Having value, but no direction.”  Definitely sounds like some people I know. Smile )

image

Editing scalar data is pretty straightforward.  Simply update the field and publish the project to Project Server.

image

Run a query against the reporting database within SQL to display the edit.

image

Timescaled data is a bit more complicated.  That’s stored in any of the tables with the designation “ByDay” – i.e. AssignmentsByDay, TasksByDay, etc.  The trick here (and thanks to Brian and Adrian for pointing it out) is that simply editing the timescaled cost values will not work…

image

When I make the manual edits above, and then publish the project, the SQL query against the TasksByDay table results in the following:

image

Worked just fine, right?  Well, unfortunately, things are not as simple as they appear.  It turns out that simply entering timescaled data doesn’t trigger Project Server to capture the data in the enterprise databases.  And if Project Server doesn’t capture the data, we can’t generate reports against it.

After doing some testing, it seems that Task and Assignment timescaled data are treated differently.

For Task timescaled data to be saved, at least one of the scalar values must be edited.  In the example below, I have edited the Baseline10 Cost field in addition to the two timescaled values.

image

This triggers Project Server to recognize the timescaled data and to store it on publish.

Assignment timescaled data appears to be a bit more complicated.  As far as I can tell, Assignment timescaled data requires a value to be entered in both the scalar and the corresponding Baseline Work field.

In this example, I enter data in Baseline10 Cost, Baseline10 Work, and the scalar Baseline10 Cost field.

image

Note that any non-null value in the Baseline10 Work field will trigger the save.

image

So, in short, if you wish to edit Baseline timescaled data, you’ll need to remember the correct triggers to force Project Server to retain the data.

Data Baseline Save Triggered By
Task Scalar Data Edit
Assignment Scalar Data Edit &
Timescaled Work Edit

You could apply the same approach to a budget resource assignment, i.e. create a budget resource, assign it to the summary task, and then copy the relevant data into the assignment row – in which case you may not have to mess with the baseline mechanics.  I didn’t test this scenario against a custom timescaled budget cost field, but if you’re looking to create more custom timescaled fields, it wouldn’t be hard to just create a budget resource called “BCWP” and then use that to store the data.

Next up…the macro.

Advertisements
Capturing Custom Timescaled Data in Project Server (Part I)

10 thoughts on “Capturing Custom Timescaled Data in Project Server (Part I)

  1. Jerry I says:

    Great article.

    How would you proprose marrying this information with AC from Enterprise system e.g. PeopleSoft? We are using an external db for capturing the historic EV results qry against the financial system. Wish it was integrated!

  2. Jon says:

    Hi Andrew – I got your macro to work and liked the idea of putting it in a budget resource. I tried doing that and it sums up to BCWP, but it doesn’t enter it directly from BCWP to the applicable row in Budget Cost. It does update, but the numbers don’t match by day/week.

    Set TSVSBaselineCost = ActiveProject.ProjectSummaryTask.Assignments.Item(1).TimeScaleData(ActiveProject.ProjectStart, ActiveProject.StatusDate, pjAssignmentTimescaledBaselineBudgetCost, pjTimescaleWeeks, 1)

    To run this as a Budget Resource item, is there something I am missing? Great site and resources.

  3. Yury says:

    Hi Andrew,

    Since VBA macro works slow for 1,000 resources, is it possible to use server side PSI application to achieve same goal.
    Option 1: Macro can invoke Web Service (PSI) to update Project Server Database fields
    Option 2: Run background application on server side to use PSI to update Project Server DB with timescaled EV

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