Capturing Custom Timescaled Data in Project Server (Part III)

So the pieces are starting to come together.  In the last post, I showed you how to write a macro to copy the BCWP data into the Baseline10 Cost timescaled fields.  In the post before that, I discussed how we could actually get Project Pro to save the data to Project Server.

So to make a long story short, we now have BCWP stored in the Baseline10 Cost timescaled data.  The next step is to pull it back out in some sort of usable format.

Before we get into that, a quick review: to avoid performance issues, the macro is storing the BCWP for the project in the Project Summary Task Baseline10 Cost field.  We’re not storing assignment level data.  If we were, the reporting would be a lot easier, as we could just leverage the OLAP cubes to achieve pretty much the same thing.

…but if life were easy, there’d be no market for consultants.  Hence, like Chekov’s Gilyaks, I metaphorically find myself trudging through the Sakhalinese mud, moving in parallel to a newly paved tsarist road.  (Yes, someone might have been reading Murakami over the holiday break.)

Now where were we?  Oh right….once we work out the issues with simply getting the data to copy and save correctly, we need to start looking at how we plan to consume the data.  As I saw it, I had three options:

  • OLAP Cubes (Assignments)
  • Reporting Database (Assignments)
  • Reporting Database (Tasks)

What I quickly realized is that while assignment level data gave me the most granularity and allowed me to piggy back off of the existing OLAP cubes, it also represented a significant performance issue.  Copying data for each timescaled cell for each assignment in a single project could take upwards of a couple minutes…plus saving timescaled data also increases the time required to save to Project Server.  Saving the data for each task could also present the same difficulty.

After generating assignment level data and seeing the performance issues, I went back to the drawing board, and decided to try again at the Task level, specifically at the project summary task level.  This would mean, I would only be copying data for a single task to generate the data required.

The BCWP data would then be stored in the TaskByDay table.  Unfortunately, ACWP is stored in the AssignmentsByDay table.  So I had to write a query to pull the data from the appropriate table and assemble it in a form that would be suitable for reporting.

Here’s what the query ended up looking like….

SELECT TimeByDay, ProjectName, ProjectStatusDate, Sum(BCWP) AS BCWP, Sum(ACWP) AS ACWP, Sum(BCWS) AS BCWS FROM
(SELECT     dbo.MSP_EpmTaskByDay_UserView.TimeByDay, dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmProject_UserView.ProjectStatusDate,
                      dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost AS BCWP, 0 AS ACWP, 0 as BCWS
FROM         dbo.MSP_EpmTaskByDay_UserView INNER JOIN
                      dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmTaskByDay_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
UNION ALL
SELECT     dbo.MSP_EpmAssignmentByDay_UserView.TimeByDay, dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmProject_UserView.ProjectStatusDate,
                      0 AS BCWP, dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualCost AS ACWP,  dbo.MSP_EpmAssignmentByDay_UserView.AssignmentBaseline0Cost AS BCWS
FROM         dbo.MSP_EpmAssignmentByDay_UserView INNER JOIN
                      dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmAssignmentByDay_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID) AS DataSet
GROUP BY TimeByDay, ProjectName, ProjectStatusDate

…which basically takes the BCWS and ACWP from the AssignmentByDay table, and then combines that with the BCWP stored in the TaskByDay table Baseline10 Cost field.

Drop that into an ODC, generate a line chart, and we end up with something like this…

image

Next up….generating the report in Excel.

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

One thought on “Capturing Custom Timescaled Data in Project Server (Part III)

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