Balanced scorecarding, a method of measuring activities and outcomes for effective performance management, is one of the most popular concepts in advanced business intelligence (BI) today. In short, it enables executives (as well as employees at every level of the organization) to quickly measure their performance as it relates to the goals and objectives that have been set for them.

Scorecarding can add tremendous value to the management process because the entire team can see what they want to achieve both within their own area of responsibility as well as throughout the overall enterprise, which guides them in determining what needs to be done to reach their goals. They can see how their efforts fit into the overall perspective at all stages of the process and make whatever changes are necessary.

Many companies know that implementing a balanced scorecarding system could strengthen their competitive advantage. However, before beginning, CXOs should understand the level of resource and financial commitment that is required for an effective implementation. Given the value and depth of the information it yields, balanced scorecarding is, by necessity, much more complex than many other BI applications.

Levels of Complexity

Because performance metrics are constantly changing, an effective system must be dynamic and, therefore, database driven. The data warehouses, data marts and other systems that capture the company's data are the fuel that makes scorecarding run.

While typical data warehousing applications effectively capture the organizational facts, balanced scorecarding goes a step further. It serves to interpret those organizational facts relative to objectives, targets and initiatives that contribute to overall performance. In order to accomplish this, an additional layer of measures is applied. This layer serves to quantify the change in the lower level facts, compare that change to the level of change that was expected and then score the performance accordingly.

With a typical data warehouse, it's easy to see data such as revenue and expenses. However, the data warehouse doesn't let you easily identify growth trends or expense trend anomalies that show whether (and where) the company is doing well or poorly –­ or will be in the near future. That's what balanced scorecarding is about –­ it gives company leaders perspective on what they want to measure and what they want to do going forward. It then lets them loop back throughout the year to see if, and how effectively, they are doing what they should be doing.

Employees in different positions and different business units have different performance measures, which adds another level of complexity. For example, a company may set goals for higher revenue, bigger market share and lower turnover. Human resources (HR) managers need to compare their actual turnover data against the target to gauge their success, but they'd have little or no need to measure market share. The CEO would need information on all of the highest level performance measures, plus he or she needs to drill down ­– or across –­ to more detail. Balanced scorecarding is usually deployed from the top down, but data is accumulated from the bottom up. A business can set targets for its branches and aggregate that information up to the top level.

Sourcing the data that goes into the system is complex as well. Information can come from several systems, including data warehouses and data marts; budget/forecasting systems; and ERP, CRM, SCM and HRMS applications. What's more, not all source and/or target data is readily available. This may necessitate new or additional data capture applications.


As mentioned, balanced scorecarding is a method of measuring activities and outcomes for effective performance management. Typically, there can be dozens of base level measures, which become the foundation for higher level performance-oriented measures. These performance measures are categorized into four different perspectives to measure activities and outcomes:

Activity Measures

  • Internal Business Process Perspective (safety violation trend, on-time delivery rate, etc.)
  • Learning and Growth Perspective (training hours/FTE, voluntary turnover, etc.)

Outcome Measures

  • Financial Perspective (market share, profit margin, revenue growth, YTD asset growth rate, etc.)
  • Customer Perspective (attrition rate, brand recognition, high-value customer contact rate, etc.)

There are several steps an experienced database architect would follow to compile these measures. Using extract, transform and load (ETL) products such as Informatica PowerCenter or Business Objects Data Integrator, base measures for the specified time period are extracted from their varied sources (including both actual and target data for all base measures), aggregated to the atomic business unit level and stored in a preliminary data staging area.
From this point on, the data modeling theory is somewhat different from most dimensional models. In a traditional model, measures are stored as multiple columns in one fact record, identified by the appropriate combination of dimensional keys. The balanced scorecard model refines that approach slightly in order to improve query flexibility. Simply put, the refined model pivots the measures columns into additional rows and implements an additional dimension called the "measures" dimension –­ in effect "normalizing" the fact table. Although this approach may seem unusual to some, the concept has been used effectively for many years in several multidimensional online analytical processing (MOLAP) oriented products. Also, contrary to popular modeling methods, an unconventional approach to data storage proves to be the most effective way to accurately depict performance and various levels of the organizational hierarchy. In typical BI applications, data is primarily stored at the lowest level of each dimensional hierarchy and "rolled-up" at query time to aggregate base level data to higher levels in the hierarchy. In a balanced scorecard data mart, base level measures are aggregated to all applicable levels up the dimensional hierarchy in the ETL process and stored in the preliminary data staging area in a pre-aggregated fashion.

Figure 1: Balanced Scorecard Data Model ­– Preliminary Staging

Once the base measures have been staged, more complex measures to quantify growth and performance are compiled. A second staging table is used to accomplish this. To populate this table, ETL routines are executed to read data from various time periods in the preliminary staging area, calculate performance measures and then store all the measures aggregated to all levels of the organizational structure in this staging area. The types of measures calculated in this phase are true performance measures.

The ETL processes involved at this stage include many different calculation scenarios:

  • Year-to-date growth measures compare the current time period to the previous end of year balance.
  • Quarter-to-date measures compare current quarter results to previous quarter results.
  • Month-to-date growth measures compare current time period to a previous time period.
  • Year-over-year measures compare the current time period to the same period last year.
  • Ratios compare multiple current period measures –­ possibly originating from different sources (example: revenue/FTE).
  • Annualized measures adjust current period rates to annualized rates (example: annualized loan spread rate).

It is because of these performance measures that all base level measures are pre-aggregated in the preliminary staging area. Performance measures are not additive and, therefore, cannot be rolled up by typical BI tools using SQL queries. Pre-aggregating, calculating the performance measures at all levels of the organizational hierarchy and storing all measures in the staging area will allow application of the next layer of calculation logic –­ scoring actual performance based on target and variance thresholds and associated weights ­– in the final reporting model.

Figure 2: Balanced Scorecard Data Model –­ Staging

The resulting staging model includes actual and target data for all base and performance measures pre-aggregated and stored at all applicable levels of the organizational structure.

Balancing Act

Scoring actual performance for an individual or group based on target and variance thresholds and associated weights is, in a way, a balancing act. The concept of scoring the results of performance measures has its own inherent complexities. The completed staging area provides a foundation for the balanced scorecard model, but it is not yet complete. In order to effectively score performance of individuals or groups, appropriate measures ­– not all measures –­ need to be associated to that individual or group. A weight for each of these measures must be assigned to represent the contribution of that measure to this group's overall performance.

The total of all weights for a given group should equal 100. Overall performance will be calculated based on the sum of the weighted measure results relative to 100. In addition to the weights, thresholds (implemented as a percentage of the target value) should be set for each measure that is applicable to that group in order to easily identify what performance level is deemed to be unacceptable, acceptable or exceptional.

Multiple business stakeholders typically manage the process of setting weights and thresholds. Data can be captured using standard (or customized) data capture tools and migrated to the balanced scorecard reporting model using the ETL tool. Calculating the result score for each measure is not as trivial as one would think; simply calculating the ACTUAL/TARGET*WEIGHT only applies to one of eight scenarios ­– when a higher value is better and the target is positive. Score calculation scenarios to be considered include those found in Figure 3.

Figure 3: Score Calculation Scenarios

Each scenario requires a unique –­ and, in some cases, very complex ­– calculation method to determine the weighted result score. All result scores are then limited to a floor of 0 and a ceiling equal to the applied weight (100 percent of target).

For example, a CEO's many goals may include a 20-percent revenue increase and a maximum 5-percent employee turnover rate. The revenue increase would be his or her highest priority. Weighting those measures allows the CEO to monitor performance for each individual measure and proportionally apply each score to the CEO's overall performance score. If you have 20 measures, each individual measure would account for approximately five percent of the total if weighted equally. Even if you "blow the doors off" and quadruple revenue, the top score for revenue would still be five –­ capped by the weight. By the same token, if revenue drops 50 percent, its score could go no lower than zero. This encourages the individual or group to maintain a balanced outlook. Overachieving in one perspective does not offset underachieving in another.

An overall performance measure, and aggregate performance in each of the applicable perspectives, can be calculated by simply aggregating and comparing the weighted result score and the weights themselves: SUM (Weighted Result Score)/SUM(Weights) = Overall Performance Score or Overall Performance Score by Perspective.

All of these elements are incorporated into an additional ETL layer. The ETL tool is the best place to overcome these complexities and populate the ultimate balanced scorecard reporting model.

Active Deployment

After all data sourcing, staging, aggregation, performance measure calculation, weighting, threshold setting and performance scoring is complete and populated in the final balanced scorecard reporting data mart, an effective deployment method is required.

Figure 4: Balanced Scorecard Data Model –­ Reporting

An effective balanced scorecard deployment will allow users to leverage many features including:

  • A Web interface: workers are so familiar with the Internet that they can log on at any time and view their performance metrics.
  • Dashboard reporting mechanisms, particularly helpful for at-a-glance communication.
  • Ability to drill down, through or across data.
  • Ability to restrict data available based on user.
  • Ability to allow managers to see scorecards for themselves as well as any subordinate level employee.
  • Ability to dynamically compile scorecards. Only one data- driven scorecard need be developed versus separate scorecard developments for each recipient.
  • Ability to send alerts to appropriate personnel when thresholds are crossed.
  • Ability to represent key metrics as indicators on a dashboard or scorecard report.

Several BI tools meet these criteria. Vendors offering these tools include Informatica, Business Objects and Cognos. The dynamic data model articulated in this article, combined with the appropriate BI tools, enriches users with the ability to navigate enterprise data quickly and effectively to easily identify and correct anomalies in business performance.
Finally, balanced scorecarding is a useful –­ some would say essential –­ business intelligence tool. For the time being, its capabilities and technological demands limit it to use in larger enterprises. Understanding its potential and its complexity can help business users understand the process and determine whether it is practical to help their organization reach its goals.

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