In the current economic environment where IT project cost or schedule overruns have a much greater impact than during normal or positive economic times, falling behind on your IT projects can quickly lead to overall failure or projects being eliminated. These times call for increasing IT project predictability as project performance becomes critical to successful project delivery. Keeping execution on time and on budget calls for a solution that can provide trends, scorecards and forecasts so that your project management office (PMO) is able to forecast impending changes and manage project ebb and flow.

A carefully blended solution of data collected from disparate project tool sources (Microsoft Excel, Microsoft Project, ERP systems, ticketing systems, etc.) can provide you with the means for leveraging BI solutions by way of key performance indicators and data analysis. It is helpful to examine common ways in which project data from spreadsheets and databases on past projects can be used to provide a comprehensive view into project performance. These techniques can be used to achieve improved control and management of your projects to more consistently reach successful completion.

A good starting point is to consider a key aspect to ensuring that your projects stay on schedule, within budget and scope. That is to employ the best practices of what is commonly referred to as the earned value management (EVM) technique. Although EVM in its purest practice is more common in government and construction projects, there are best practices that can be and should be used from earned value controlled projects that are very useful in IT projects. Instead of turning this article into a treatment or training on EVM, I will leave that to Project Management Institute (PMI), certified project management professionals (PMPs) and the many books and blogs about EVM that can explain it much better than I am able to here. But what we should do to allow BI data collection, metrics and dashboards to drive project performance is practice a few key areas of EVM for our purposes. Starting with measuring and ongoing monitoring of key metrics that come from EVM, you will find these aspects of project control very useful for your projects. These can and should be centrally tracked and dispersed from your project management office or PMO. And while on the topic of PMO, let me also state that the most effective method to put those KPIs to good use is through business performance management from a centralized PMO where you can drive performance through project KPIs, center of excellence guidance and leveraging of common, shared resources across enterprise IT projects that can only be performed from a centralized PMO. A siloed or vertical PMO will have limited scope, control and access to cross-organizational knowledge that can lead to efficiencies, lessons learned and improved project performance.

Before we look at the nuts and bolts of a BI solution to provide performance management for projects, let us start at the very beginning of the process and look at key project metrics. In order to have the data available to drive project performance, you must first set goals and measurements. The four primary measurements that we will focus on are schedule performance index (SPI), cost performance index (CPI), planned value (PV) and earned value (EV). With these measurements, we can then monitor indices that will track variances from what was planned and how we are performing. In order to make any of these possible, from a PMO COE perspective, you must insist that project managers develop a complete schedule project baseline to establish project scope and planned costs. That baseline should not change and will be used to track against the progress of the constantly updated living project schedule. In the IT industry, completed and detailed work breakdown structures (WBS) or properly defined milestones and detailed activities are often lacking when compared against an industry that fully embraces project control mechanisms from the power and utility industries or construction projects. But the tremendous accuracies and efficiencies found in those industry projects provide glimpses into best practices that we will utilize in IT projects.

With our IT projects, there are tasks and activities that do not appear to have direct costs such as contracting hours or an hourly consulting rate. Instead, use a full-time equivalency or the common practice of a fully burdened rate for in-house employees. Blend that approximation with an activity-based costing method to come up with a cost for activities and project roles that into account salaries, benefits, infrastructure, etc. With the project baseline and scheduling in place, you will then begin the process of centralized tracking, controlling and measuring of the project throughout the remainder of project lifecycle. Most project managers will enter schedules, journals, activities and costs. in tools such as Microsoft Excel or Microsoft Project and then use enterprise project management tools for the more advanced scheduling, budgeting and reporting requirements. In addition, it is quite common to also interface with enterprise financial systems to control budgets, ERP for manufacturing control and BI tools for additional reporting that may include data mining, predictive analytics and drill down from program level to project level down to activity level for resources, costs and schedules.

This is where performance management becomes a key BI tool. BI enterprise performance management vendors make it very quick and easy to build these KPIs as measures in scorecards that can be tracked over time. A common good practice is to establish centralized variance thresholds from the PMO for areas such as schedule variance and cost variance. A core strength of a centralized PMO is exploited here by gaining centralized visibility into on going projects as well as projects in the pipeline and project history. Essentially, the PMO acts as a COE. In this capacity, you can advise and enforce the proper watermarks that will be used for these scorecards. A threshold for well-performing or exceptional projects must be determined as well as those that are underperforming or on-track. These levels will be entered into the scorecard builder tool as a manual entry for each KPI. Then to build upon the foundation and strength of a centralized PMO, those scorecards become part of the enterprise data portal so that entire organizations can share in the knowledge gained from this sort of visibility into project performance. While on this topic, let us not forget that evidence has shown that once a project’s cost, as measured by CPI (a key metric in earned value management) has exceeded the baseline budget at just the 15 percent project completion point, you cannot recover those cost overruns. So it is imperative to have these metrics tracked frequently and made available to the project team as well as the project stakeholders because you must create a constant feedback loop through scorecards to the project teams, mainly focused on project managers or project controllers. Realizing that a schedule or budget will slip at the 70 percent complete point, for example, is far too late. This is not to say that you must fully enact EVM control in its strictest form to have successful performance management for IT projects. In fact, most PMOs in the IT world will put emphasis on the tracking of metrics, costs, schedule and resources as the primary areas of focus. The performance management early warning indicators from EVM best practices can be very useful for a project management office, such as those outlined by one of the fathers of earned value, Quentin Fleming. In his 2002 book titled The Earned Value Book of Knowledge, Fleming talks at length about utilizing CPI and SPI metrics in order to forecast estimates at completion. By monitoring these values, you can create an early warning signal when measured against the planned budget at completion. Some of this sounds rather basic, but it does leave out some of the more detailed nuances of EVM such as control account plans or TCPI. Instead, get your three axes of project control (schedule, resources and costs) into a BI tool for good project performance management.

First, load that project schedule baseline with resource costs and budgets into staging tables. At the very minimum, the schema should account for schedule planned start and finish, actual dates, projected finish, resource utilization, resource per unit costs, baseline budget and projected costs. Having this core data will allow you to calculate variances based on baselines, budgets and plan as well as to set up triggers and alarms when we reach the scorecard part of the solution. When loading up the star schema from staging, some important calculations are:

  • Budget at completion (BAC): The total planned value at the end of the project.

  • Cost variance (CV): Earned value mius actual cost. Positive numbers indicate that you are under budget.

  • Cost performance index (CPI): Earned value divided by actual cost. Positive numbers are good. But conspicuously high numbers can be an indication of improper data loading.

  • Schedule performance index (SPI): Earned value divided by planned value. Positive values are ahead of schedule.

Figure is a sample of what a star schema for project performance metrics could look like for this purpose. Keep in mind that this is just a skeleton concept of a star schema to use for building BI solutions for project performance optimization. A full-blown performance management solution from your PMO will need much more project, cost and schedule detail in order to provide project optimization using the previously described disciplines.

Follow typical patterns in BI and data warehousing to load the database. Start with importing Excel schedules and costs into an operational data system or staging table (not presented in this article) and then transform those into a star. Some common areas to focus on will be matching up the cost estimates to activities and resources. Once you have associated costs at those levels (activity being equal to task or work package, the lowest possible WBS level,) you can then perform roll ups and time-phased queries in MDX such that you are able bring together the combined Excel-based data from the field with the project manager’s master schedules in Microsoft Project. Both of these sources will be file-based, so they should be loaded once the files are stored in a shared file location or FTP sever where you will perform ETL. Additional information that will be gathered may come from complex, advanced scheduling and resourcing tools that may include SAP or Oracle in the ERP world of supply chain management and materials management in high-tech discrete manufacturing.

Once this data is loaded into a star schema, put up a corporate scorecard that is published centrally through the PMO to all corporate stakeholders. The sample reports in the list that follows are what things begin to look like once you have formulated the calculations listed above either in the ETL from ODS to star or on-the-fly with MDX or SQL queries. In most cases, these project performance management dashboards do not have IT performance requirements that would dictate storing precalculated fields in the database. Instead, the focus and goal should be to "democratize" the knowledge and make it available to the entire enterprise via reporting portal dashboards. Some key reports to include in such a portal dashboard:

  1. CPI and SPI Index over the lifetime of the project.

  2. Cost and schedule variances, which you will show as deltas between current project data (current activities, work packages, estimates) and the original baseline from the original WBS.

  3. Estimate to completion and estimate at completion from a project-by-project current snapshot perspective. This gives an overall summary view of the performance of project groups by portfolios or programs.

  4. Earned value measures that are calculated in the OLAP engine as value of work completed. Augment this with a predictive analytic time series model to show estimates of earned value over the next two reporting periods (quarters, months, etc.) based on recent reporting trends for each project over time.

  5. Scorecards. Make sure you have captured the PMO’s expected plans for each project in terms of schedule, cost and resources. The scorecard should have indicators of what the current snapshot value is as a stop-sign style indicator against the plan numbers from the last executive planning review. Lastly, include the future predictions from number four on the list to also show trend indicators in the form of an arrow up, down or on track.

By following these basic guidelines for a performance management solution for project optimization and IT PMO, you will develop a shared enterprise tool all project teams can use for status, visibility and to hold those teams accountability on a continuous basis to strict goals and objectives for schedules and costs. Visibility into other enterprise project teams can create a competitive situation as well as a big picture view into an entire portfolio of projects or programs. This can elevate a project team’s awareness of the impact their tasks and milestones may have on the organizations bottom line success. And when times are tough, as they are, economically speaking, today, optimizing project schedule and cost performance with a constant eye toward payback and benefit for the entire organization is even more vital and can be optimized through business intelligence.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access