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…
Next up….generating the report in Excel.