Well, some of you have been reading this blog for nigh on 4 months, and through this medium, I feel that we’ve built a solid and trusting relationship. Hence, it’s probably about time for me to admit something rather embarrassing. Just between you and me (and the search engines), until recently, I couldn’t write a SQL query to save my life.
Now, I know what you’re thinking: “That’s no big deal.” In the real world, not being able to write a SQL query is perhaps not a big deal. But in the world of EPM tools, that’s tantamount to admitting that you’re an adult illiterate. (And that’s not to disparage any illiterate adults who may, in fact, be reading this post.)
Well, I am pleased to say that I have joined the ranks of the literati again after taking some time over the weekend to pick up some basic query writing skills. I wouldn’t claim to be an authoritative expert in the field, but at least now I am somewhere in the advanced bumbler level.
Honestly, it was a great experience. Everyone should learn SQL queries. I can’t wait till my kids are old enough to want to learn so that we can bond over writing queries. Looking at some existing queries that other folks have made for me, it kind of feels like the first time I walked into a restaurant in Seoul after having taught myself to read Hangul.
So what to do with this newfound knowledge? Why, develop some slick portfolio reports, that’s what!
Hence today, I present to you the Baseline Execution Index (BEI) Report.
The BEI Report is something that I’ve stumbled across before and is actually required in a lot of military contracting. In fact, if you plug BEI into your search engine of choice, you’ll see a number of references to it on the US Defense Acquisition University Website. (See here)
Essentially, it’s a performance measure on the project (or portfolio) that totals up all of the tasks actually finished in a given period, then divides that number by the total of all tasks scheduled to finish in the given period. That results in an index:
(Number of Tasks Actually Finished / Number of Tasks That Should Have Been Finished)
In a perfect world, your index would be hovering right around 1. That means you are completing all of your tasks more or less on schedule. A BEI greater than 1 would indicate that you are completing more tasks each month than were baselined – and a BEI of less than one would indicate that you’re completing less tasks than were planned.
This is another one of those reports that could be used at the portfolio level to indicate portfolio performance or at the individual project level to indicate project performance – both reports doable with the relatively low administrative overhead of simply requiring a baseline and a reasonably decent schedule update process.
Drop this query into an appropriate ODC to get the required data set:
SELECT dbo.MSP_EpmProject_UserView.ProjectName, NULL AS BaselineFinish,
TaskActualFinishDate AS ActualFinish, Set1.TaskActualFinishDate AS TotalDate
FROM dbo.MSP_EpmTask_UserView AS Set1 LEFT OUTER JOIN
dbo.MSP_EpmProject_UserView ON Set1.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE (TaskBaseline0FinishDate <> 0) AND (TaskActualFinishDate <> 0)AND (TaskActualFinishDate < GETDATE()) AND TaskIsMilestone = 0 AND TaskIsSummary =0
SELECT dbo.MSP_EpmProject_UserView.ProjectName, Set2.TaskBaseline0FinishDate AS BaselineFinish, NULL
AS ActualFinish, Set2.TaskBaseline0FinishDate AS TotalDate
FROM dbo.MSP_EpmTask_UserView AS Set2 LEFT OUTER JOIN
dbo.MSP_EpmProject_UserView ON Set2.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE (TaskBaseline0FinishDate <> 0) AND (TaskBaseline0FinishDate < GETDATE()) AND TaskIsMilestone = 0 AND TaskIsSummary =0
Order By TotalDate
…which essentially is a modified form of the query I used last week to generate my Cumulative Milestone Report. What we’re doing is concatenating two sets of data, the Actual Finish dates for specific tasks as well as the Baseline Finish dates for tasks.
Note that the following filters have been applied:
- Only baselined tasks are included. This ensures that we adhere to the current approved plan and ensures that project managers will not game the system by adding their own tasks.
- The Actual Finish Date and/or Baseline Finish Date is/are less than today’s date. We shouldn’t be reporting on Actual Finish Dates in the future because, well, there shouldn’t be any, and if there were, they would indicate the project manager requires additional training rather than a bonus for finishing early.
- Milestone tasks are excluded. As they do not indicate work and often are used to manage external factors, they should not be relevant to this exercise.
- Summary tasks are excluded. Including them would invalidate the data by introducing duplicate record sets.
If you followed that query I wrote last week, you’ll note that I didn’t bother to convert the TotalDate field to the first of each month. I intend to use the Excel grouping functionality to do that. Since I don’t plan to implement any cumulative line graphs in the Excel chart, that shouldn’t be an issue. (Although if you did plan on adding some sort of cumulative task completion plot line, I would recommend you take a look at that post.)
Open Excel and add the connection to the ODC you configured with the SQL query above. Insert a PivotTable based on that data and configure it as follows.
Now we’re going to add two calculated fields. Click on the PivotTable and select the Options tab. Click on Fields, Items & Sets to generate the two fields.
Call the first field something like “BEISource.” Configure as follows:
Now we’ll add a “TargetSource” field.
Your PivotTable should look like this:
Rename the fields to something more user friendly.
Group the data by clicking on the Total Date field and selecting the Group button under the Options tab. Group by Month and Year (or Quarter or whatever you would like).
Now select the option to create a PivotChart. Use the column chart format. You should now have something that looks like this.
Right click on the Target series, and change the chart type to line. This gives us a nice benchmark to measure against.
Right click on the Y axis and choose the option to Format the Axis. Set the options as follows:
Configure the X axis as follows:
And you should have something that looks like this:
Not a very compelling picture, for which I blame the sample Contoso data. When I filter on a single project with better data, I get something like this:
Looking at this illustration, I would conclude that we were getting about half the work done that we thought we would between April and June, then were able to pick up some of the slack in July followed by a slowdown again in August.
…and now if you’ll excuse me, I plan to head out and solve the problems of the world with well-written SQL queries….