We’ve started to more formally track the myriad of projects involving IT at UNE just of late.  I’m not directly involved with the process but think at the last count, there were in excess of 130 currently on the go.  The latest Kimball Design Tip #118 that arrived in the email last week is all about managing project backlogs dimensionally and it got me thinking again…

I’ve wondered for a while whether it would be worth constructing a dimensional model and associated metadata and reporting capability to assist with IT (or indeed any) project tracking.  I think I’d come to the conclusion that it would be very difficult to capture the current state of all projects in a consistent and accurate way at a frequency that improves on the traditional monthly ’stock take’.  That, after all is the problem with traditional project tracking - it tends to be performed periodically - typically in the form of a weekly, fortnightly or monthly status report.  The Project Manager scurries around getting people to tell them how much work has been done or is remaining against each of the tasks and then rolls it all up to some percentage complete figure.  This process is repeated across many projects every time the status report deadline is looming.

A problem with this is that the status report deadlines aren’t necessarily synchronised so the organisation never has a point in time picture of the status of all of its projects.  The only way around this problem I know of is to synchronise the dates in which case there is an end of month (or worse) frenzy for a day or two when no one actually does much productive work while the updates are captured.

An alternative might be for the updates to be provided constantly, trickle-fed into some storage medium (like a warehouse perhaps) on a daily basis, ready for reporting on demand at any time. No end of month frenzy and continual currency of all project data.

I realise that the above might not apply to all organisations, perhaps a monthly reporting frequency is fine and perhaps there are other reasons for needing to use this approach.  I also realise that consulting organisations are serving both internal and client reporting needs which aren’t necessarily aligned - perhaps all the more reason for a more flexible dimensional approach?

If we decided to try and implement such a system, the critical component would be the trickle-feeding of the daily updates into the warehouse.  Using Scrum as our development approach really helps in getting status updates on a current sprint and many of the modern tools, including the one we use, store the tasks and updates in a relational database that could be interrogated by a nightly ETL process.  The project backlog would have to exist and be estimated for the entire known project but of course as that flexed, so too would the multi-project reporting.

Aside from being able to report using BI tools, another nice thing about having project reporting in the warehouse would be around conformed dimension reporting where we exploit pre-existing warehouse content.  This enables reports such as Staff Project FTE : Staff Total FTE by Department Ratio which could illustrate commitment or investment in projects or programs of work at any point in the organisational structure.

Worth some more thinking?  We’ll see how readily we can get access to the project updates and the frequency of these in our brave new world of program and project management, but if this data is available then I think it could be a relatively simple schema to build and quite a powerful one to use.