BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

« May 2009 | Main | August 2009 »

June 29, 2009

A Fact Qualifier Matrix Example

Here is an example of using a Fact Qualifier Matrix (FQM) for development of a BI solution that we created for managing and optimizing IT project performance:

An FQM is something that I learned about years ago from Steve Hoberman ( who I felt was THE best I have ever heard at explaining data modeling and concepts regarding building data models for data warehouses. That's my plug for Steve, my way of paying back for the great class and book of his that helped me on many projects.

During that training on building data warehouses, we practiced using FQMs as a way to utilize process-oriented requirements engineering and specifications top-down from the perspective of the business users.

Of course, this is somewhat of a no-brainer in the world of software development. But I loved the way this seemed to work in a lab or classroom setting for a technique to build a data warehouse dimensional model that was clearly meeting a busines need and solving business problems.

Haven't we all come across data warehouse designs and dimensions designed that are undocumented, cannot be traced back to requirements and have you scratching your head wondering where this came from and why on earth has it been architected this way?

Not to say that the fact-qualifier matrix is the cure all.

But take a look at the same screenshots I included above. We asked business users the questions from the Excel tab on the first screenshot. Then we mapped the answers to dimensions on the left and facts on the top to create a matrix that can then be used to create a logical design. The notations on the dimensions were used to signify hierarchies in the dimensions and dimensions that may need special handling based on the complexity of the source data.

Posted by Mark Kromer at 11:45 PM | Comments (7)

June 4, 2009

Portfolio-level KPIs

I am a big proponent of the big-picture, business-case, cost-benefit approach to project selection, prioritization and management. In a nut shell: project portfolio management.

Since we are all gathered here today on the BEYE blog, let us spend a moment on how to collect data, measure KPIs at the portfolio level, and optimize project performance from a portfolio manager or PMO's view.

First, recognize that in the end, a portfolio is a collection of projects that are being grouped together for a reason. That can be their strategic purpose, organizational constraints, program initiatives, or other reasons.

But since we are talking about projects, that means that we will need to measure and drive schedules, costs, quality and resources. But we are looking at the project summaries that are rolled-up to a higher level. Which means that we do not want to act as project managers, project controllers, planner, schedulers, etc.

Instead, we want to look at these as if we were business decision makers, financial analysts and business analysts. These measures need to answer these questions about the portfolio:

1. Why are we embarking on this effort?
2. How much will it cost?
3. Do we have the resources for it?
4. What is the return on the investment?
5. Does it align to our business objectives?
6. Will it meet our quality standards?

To keep the blog short, I will focus on #1 thru #4 and return to the other issues a little later.

Before we talk about the KPIs, let's touch on how we collect this data. The data needs to captured through a process of export/import, direct ETL, an MDM hub, or other means that give you a single version of the truth in a warehouse or hub. In the project world, these do not need to be very complicated data marts. The dimensions are very distinct, but the concept of project lifecycle or "spread" data needs to be kept in mind. I have already blogged about this here in Beye and will expand it shortly.

The KPIs can be either calculated fields or metadata on-the-fly metrics, that does not necessarily matter. But displaying these in very simple terms on corporate dashboards is essential. This is because one thing that I hear time and time again about projects, programs and portfolios in customers is that those who are not heads-down in the project or a core part of the project team day-in and day-out do not have enough visibility into project status & performance.

So measure the schedule of each project in portfolio as a % of the projects that are behind schedule or % of projects that have SPI (earned value) < 1. A BI tool like Hyperion or Cognos will let you then drill down the portfolio hierarchy to find the offending projects. But remember that you are looking at the entire portfolio which means that 1 or 2 projects behind schedule may only trigger a warning for the portfolio.

Look at costs as a % of projects over budget or CPI for earned value and the cost/benefit calculation for each portfolio. This will mean that your data warehouse will need to include facts from your financial system that is calculating expected or actual benefits received from the business for each project.

Also take the benefits and calculate a business-approved ROI formula either using NPV or IRR, or whatever formula your finance team approves of. Showing a rolled-up portfolio ROI during the life of the project can be a very enlightening and breakthrough concept for your business.

I like looking at resource availability at the portfolio level BEFORE approving, holding or cancelling projects in a portfolio. This is because if you embark on a project that is not analyzed at the portfolio level, you run a much greater risk of experiencing surprises later in that project in regards to resource availability or the need to spend extra $$ on hiring, transferring, training resource or looking at adding contractors.

Posted by Mark Kromer at 11:30 AM | Comments (5)