Well, I got some very good feedback from that post I did a couple of weeks ago, and decided to throw some more BI examples at the wall to see what sticks. This post kicks off a twoish part series on developing a Cumulative Milestone Report at the project, program or portfolio level.
A Public Service Announcement
Before we get immersed in technical detail, we should probably step back a bit so that I can point out to you that your organization is very likely discriminated against by many of the EPM tool vendors. “Huh,” you ask? “How can that be?”
Well, if you’re reading this you’ve probably implemented some sort of EPM tool to support your internal project management processes. Chances are that those processes are still in an early stage of development. From a BI perspective, that means that you’re probably shut out of all of the fabulous eye candy that your executives saw in the demos when the tool was first adopted.
Face it, it’s an open secret in the implementing community that all of those cool reports and drill downs are predicated on actually having data and/or metrics to display, data such as cost or effort tracking figures that get pumped into OLAP cubes on a nightly basis.
But what about the rest of us? What about the organizations that are still tracking milestones and schedule variance and well, that’s pretty much it. And trust me when I say that if you have to start somewhere, simply tracking milestones and schedule variance is a massive step forward for many groups.
Well now there’s hope. BI eye candy can be yours as well. In previous posts, I discussed a portfolio timeline report and a stage gate report. In this post, I’d like to introduce a cumulative milestone report. Sit back, enjoy, and know that you too can generate those exciting, catchy BI reports that were promised in the original demo.
In all seriousness, there has been a noted paucity of compelling schedule driven reports in the marketplace of EPM ideas. There are bright spots however, one of them being the slew of reports available from the Decision Edge add in. In fact, typically when my clients start asking about options for reporting portfolio status with very limited data sets, I direct them to this great site from DecisionEdge, a third party reporting tool vendor. (Although for pure timeline views, I often recommend the Chronicle Graphics product.)
It looks like the fine folks at Decision Edge have removed their gallery of reports from their Website, but as I recall they used to have an entire selection of milestone-based reports available on their site, things like the Cumulative Milestone Report above or the Baseline Execution Index report. If you’re looking for a selection of reports, you could definitely do worse than examining their add-in.
The Cumulative Milestone Report has been percolating in the back of my brain for a while, and I decided that what with my ongoing interest in BI and my newly arrived copy of SQL Queries for Mere Mortals, I might see if I couldn’t generate something pretty similar.
So I did. And that’s what this post is about. Today, I’ll talk a little about the SQL query…and in tomorrow’s post, I’ll talk about how to configure the report in Excel.
The Value Proposition
Why should you consider implementing a report like this? Good question, I am glad you asked. When I initially developed it at a portfolio level, I figured that this sort of report would be a good measure of how effective the enterprise PMO is at improving project performance over time. As I mentioned above, the nice thing is that this report only requires a couple of milestones and a baseline to function.
However, the single project version of the same report is essentially the same (as I’ll discuss tomorrow). So if you’re looking for an easy graphical indication of the state of your project, then this report would also work – again because it has a very low data entry overhead.
First off….the query. Feel free to copy this and wait till tomorrow for the Excel configuration. Read further to understand the mechanics of how it works in case you would like to tweak it.
SELECT Set1.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, Set1.TaskFinishDate AS PlannedFinish, NULL AS BaselineFinish,
TaskActualFinishDate AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set1.TaskFinishDate + 1 – DATEPART (dd,Set1.TaskFinishDate))) 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 (TaskIsMilestone = 1) AND (TaskBaseline0FinishDate <> 0)
SELECT Set2.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, NULL AS PlannedFinish, Set2.TaskBaseline0FinishDate AS BaselineFinish, NULL
AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set2.TaskBaseline0FinishDate + 1 – DATEPART(dd,
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 (TaskIsMilestone = 1) AND (TaskBaseline0FinishDate <> 0)
Order By TotalDate
Drop this into an ODC file to get the the data set we will require. For more instructions on doing that, please see this post. Since the query is based off of built-in fields, it should work in pretty much any Microsoft Project Server environment without requiring any major adjustments.
Since I expect few organizations to be able to use this without some tweaking, let me walk through how I developed it and how I developed the logic. I am sure the query itself could be improved, and welcome any suggestions on how to do so.
After some playing around, it seemed that I needed the following fields:
- Task is Milestone – to filter on milestones only (although there’s no real reason that’s required. You could use the same approach to look at every task in your portfolio if you wanted).
- Project Name – the default report shows every project in the portfolio. This field can be used to filter on a specific project or set of projects.
- TaskFinishDate – The planned/estimated/scheduled finish date for the milestone.
- TaskActualFinishDate – The actual finish date for the milestone.
- TaskBaseline0FinishDate – The baseline finish date for the milestone.
The first thing I found when I developed my initial query and dropped it into Excel was that I needed to standardize the dates that would appear in the rows of data. For instance, if I had a row indicating 8/15/2011 and a row indicating 8/17/2011, then those two rows wouldn’t easily lend themselves to totaling all of the milestones in the month of August.
If you’re familiar with PivotTables, at this point, you might point out that I could have used the Excel Grouping functionality. True, that sort of works, but what I found is that the running total fields don’t work with Grouping, i.e. the running total or cumulative fields all end up resetting at the beginning of each grouped period. Below is what an early attempt looked like when using the native Grouping functionality.
Hence, after doing some sleuthing on the Web, I decided to create a field (TotalDates) containing all of the Baseline and Finish dates converted to the first of the month so I wouldn’t have to worry about grouping. So I converted both 8/15/2011 and 8/17/2011 to 8/1/2011. I also had to strip out the time element of the date as I found some milestones were scheduled to start at 5PM and some at 8PM, and these wouldn’t get aggregated properly within Excel.
In the query, you’ll see a number of date fields associated with this formula:
DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_EpmTask_UserView.TaskFinishDate + 1 – DATEPART(dd, dbo.MSP_EpmTask_UserView.TaskFinishDate))) AS TaskFinishDate
…which is my inelegant way of stripping out all of the extraneous details and converting everything to begin at 12:01AM on the first of each month.
Combining Two Data Sets
The second challenge I found was that I had two data sets that needed to be combined. To create the an accurate Pivot Table in Excel, I would have to combine a list of all of the Finish dates with a list of all of the Baseline Finish dates. Once I had that complete list, I could map the various summary data to each row.
My first attempt was to create two Pivot Tables in Excel, one with Baseline data, and one with a combination of Planned and Actual dates (as Planned = Actual once Actual has been populated). That looked like this:
I was able to generate the chart the way I liked it, but it seemed a bit kludgey – whenever I wanted to filter data I would have to filter in both tables at the same time. That could get annoying.
After playing around a bit more, I ended up using the UNION ALL function in SQL which essentially combines two different data sets into a larger table. This allowed me to create a set of data with the Planned date values and to create another set using the Baseline date values, then add both sets into a single table.
I set the Baseline fields to null for the Planned data set, and the Planned & Active fields to null in the Baseline data set, as depicted in the table below.
||Set 1 (Planned/Actual)
Here’s what the data looked like in SQL.
Filtering on Baselined Milestones
I should also point out that the query is filtered on baselined milestones only. I did this because 1) the Contoso image doesn’t have many baselined projects, and I figured it wouldn’t be a compelling report if the unbaselined milestones far outnumbered the baselined milestones and 2) because I had some loose assumption that an organization would care at a high level about milestones that were baselined in the beginning of the project, but may not care about extra milestones added subsequently by the project manager to enhance their own control efforts.
If you wished, you could filter on your own flag field, like say “Major Milestones.”
…and I think that’s it for the query. Tomorrow, the Excel component.