Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- BI Explained
- Developing a Data Dictionary
- BI Security
- Gathering BI Requirements
- Source System Extracts and Transforms
- ETL Mechanisms
- Business Intelligence Landscapes
- Business Intelligence Layouts and the Build or Buy Decision
- A Single Version of the Truth
- The Operational Data Store (ODS)
- Data Marts – Combining and Transforming Data
- Designing Data Elements
- The Enterprise Data Warehouse — Aggregations and the Star Schema
- On-Line Analytical Processing (OLAP)
- Data Mining
- Key Performance Indicators
- BI Presentation - Client Tools
- BI Presentation - Portals
- Implementing ETL - Introduction to SQL Server 2005 Integration Services
- Building a Business Intelligence Solution, Part 1
- Building a Business Intelligence Solution, Part 2
- Building a Business Intelligence Solution, Part 3
- Tips and Troubleshooting
- Additional Resources
Key Performance Indicators
Last updated Mar 28, 2003.
I've explained several back-end concepts in this series regarding Business Intelligence. There are the transactional reports and queries, ODS reporting layers, Data Marts, Data Warehouses, and On-Line Analytical Processing systems. A combination of information from all of these sources creates a comprehensive, strategic view of the company. Using Data Mining, as I explained in the last article, management can parse through large data sets looking for patterns that assist in decision-making.
All of these methods are usually reserved for upper-level management, since they make the majority of strategic decisions for the organization. Once the managers have this information, they make choices that affect all areas of the company. Although the servers, databases, tools and information we've been studying help simplify the amount of data managers have to evaluate, it's still quite a bit to sort through, especially when they have several areas to evaluate.
What is needed is a set of measurements over the top of all the information that management can use to compare against a defined goal or standard. Using these measurements, a simple graph, chart or icon displays the results of current status to goals or standards.
Such discreet measurements are called Key Performance Indicators, or KPIs. They represent the hard numbers that show success factors for an organization or a part of an organization
That concept is easy to explain, but it is harder to define in a given situation. It is at this point that politics begin to enter into the equation, because you're not just measuring numbers anymore, you're making a statement about someone's performance. Keep in mind that when a measurement is evaluated by upper management, there's a lower-level manager in charge of that area. Although they are probably measured in some way today, it's usually a less automated, face to face meeting. There's a chance to explain, pass off, or otherwise mitigate the numbers.
When the Business Intelligence system implements Key Performance Indicators, there's less room for explanation. You either made the numbers or you didn't. From the point of the lower manager, it's even more problematic because the Key Performance Indicator measurements are instantly available to upper management, at all times of the day or night.
Because no one likes to have this level of accountability, don't be surprised to find a little pushback from some of the company when you try to implement KPIs. Remember to be sensitive to these feelings, and make sure you work with upper level management to develop the KPI measures.
That brings us to the design of the metrics. Although you won't develop the numbers themselves, you will help management understand their parameters. First of all, all Key Performance Indicators need to be measurable. This sounds logical, but one of the most basic errors in KPI development is that the numbers don't relate to anything deterministic. What might appear as a perfect KPI at first may in fact turn out to be a terrible measurement.
For example, customer traffic at a particular branch sounds like a good KPI. On further investigation, however, this number might be affected by conditions out of that area of control. Perhaps that branch carries a smaller selection of higher-revenue items. A better KPI in this situation would be in-store net profit.
Does that mean that customer traffic isn't a good KPI ever? No, it just means that the KPI should measure things that are relevant to performance.
Another important KPI consideration is that they be consistent. Measuring something differently from month to month is confusing, and leads to an incorrect evaluation. Incorrect evaluations at this level could lead to a store or site being shut down unnecessarily.
KPIs must be goal-oriented. Measuring for measurement's sake is pointless. The KPI should show a goal, and then measure the progression towards that goal. The numbers should always be arranged such that positive numbers represent better values. This is obvious with things like sales numbers, but it is a bit trickier when you measure defects or errors. In those cases you want to measure error decreases to show positive numbers.
Keep the KPIs realistic. When upper management is presented with this powerful tool they may decide to increase sales by 50% in a week, or some other improbable scenario. Make sure that you explain that what may look like a small gain often represents a huge effort.
KPIs need periodic reviews to ensure their efficacy. What may be pertinent today might not still be an effective measurement in the next two quarters. If you've added locations, removed stock, changed your demographic or made other massive changes, you should ask for an evaluation of all KPI measurements so that they are still useful.
Key Performance Indicators can be presented in multiple formats. You can use simple numbers on a screen, bar charts, graphs or other graphics, but most of the time they are presented using a simple icon with colors or shapes indicating a numerical comparison.
For numbers that show a trend, use shapes that indicate growth, decline or stasis. For KPIs that show straight comparison, use an icon with red, yellow and green showing a match or near match to a good, neutral or poor indicator.
One of the worst KPIs I've seen from a self-esteem standpoint is a letter grade, such as those used at U.S. schools. Showing an A+ on a company portal might be a great thing, but F- grades are a great way to irritate someone. They also don't always reach the goal of engendering positive change.
You'll normally develop two presentation displays using KPIs, one for higher management and the other for each individual manager. You may decide to use two graphics, one that better represents multiple areas on a single screen better, and another with more granularities for a single measurement.
As a final note on KPIs, you'll want to create a "drill-through" effect. This allows the user to click their way through to greater levels of detail. This means more work for you, but allows the evaluator to "hear" the virtual explanations for the numbers they see.
Informit Articles and Sample Chapters
In his book Object-Oriented Data Warehouse Design: Building A Star Schema, William Giovinazzo also covers Key Performance Indicators. You can read a chapter from it here.
Jason Burby has a great article on Defining Key Performance Indicators that you can read here.