Application Developer

Quickly Define KPIs and Scorecards That Use Them

Build key performance indicators and scorecards in your business intelligence metadata with Oracle Business Intelligence Enterprise Edition 11g.

By Mark Rittman Oracle ACE Director

January/February 2011

Organizations seeking to manage and improve performance often define key performance indicators (KPIs) to measure their progress. Comprising a measure and a target and usually analyzed by dimensions such as organization, customer, product, and geography, KPIs can help organizations quickly focus on activities that have the greatest impact on business performance.

Another significant trend in corporate performance management is the introduction of scorecards. Scorecards help organizations define overall goals—to improve corporate profitability or increase customer satisfaction, for example—and break those goals down into a series of objectives that will enable them to meet the goals. Each of these objectives and initiatives is associated with one or more of an organization’s KPIs, so progress toward each objective can be measured.

The recent release of Oracle Business Intelligence Enterprise Edition 11g provides a means of defining KPIs within your business intelligence (BI) metadata and of defining scorecards that make use of those KPIs. This column shows how KPIs are defined in this release and how they can be used to create a basic scorecard that measures progress toward a corporate goal.

The example in this column uses Oracle Business Intelligence Enterprise Edition 11g with the sample sales (SH) schema that comes with Oracle9i Database and later releases. If you want to walk through the steps and practice the techniques described in this column, download KPI_ORAMAG .rpd from and install it, using Oracle Fusion Middleware’s Control feature in your environment. (See "Using Fusion Middleware Control to Upload a Repository and Set the Oracle BI Presentation Catalog Location" in Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 [11.1.1]—at—for information about how to perform this installation.)

For this column, suppose your organization has the following goals: improve performance by increasing sales, and improve the profit on those sales. These goals require monitoring performance against the targets for both sales and the margins on those sales, defining these as KPIs, and managing them through a scorecard. You will use Oracle Business Intelligence Enterprise Edition 11g to monitor these goals.

Creating KPIs

Begin the process of meeting your organization’s goals by defining KPIs for monitoring sales and margins. To create the KPIs, take the following steps:

  1. Log in to Oracle Business Intelligence Enterprise Edition 11g, using your username and password. From the home page, select New -> KPI from the common header area. When prompted to select a subject area, select Sales History.
  2. The KPI Entry screen will then appear. This screen enables you to define the actual value and target value measures for the KPI, as well as an optional time offset to show performance as a trend. In this example, enter "Sales and Costs Fact"."Amount Sold" as the Actual Value measure and "Sales and Costs Fact"."Amount Sold Target" as the Target Value measure, and leave the Enable trending checkbox unchecked, as shown in Figure 1. Click Next to proceed to the Dimensionality screen.


    Figure 1: Defining the actual and target values for a KPI
  3. In this example, you want users to be able to switch the product category for which the KPI is displayed but you want the year, 2001, to stay constant (or "pinned"). To do this, click the Add button on the Dimensionality screen, select the "Times"."Calendar Year" attribute, and select 2001 for Value. Then click Add again to add the "Products"."Product Category" attribute column, but leave Value as Not Pinned. Click Next to proceed to the next step in the process: defining the thresholds.
  4. On the Thresholds screen, you can specify values that will determine whether the performance achieved warrants an OK, Warning, or Critical indication. In this example, performance will be considered OK if it is within 90 percent of the target and critical if it falls below 70 percent. To define these thresholds, enter "Sales and Costs Fact"."Amount Sold Target" * .90 as the first threshold value and"Sales and Costs Fact"."Amount Sold Target" * .70 as the second, as shown in Figure 2.


    Figure 2: Defining the high and low thresholds for a KPI

  5. Click Next to proceed to the next step, where you can define related documents for the KPI. This enables users to reference other analyses, external Web pages, and documents that can be used to support the KPI. For this example, however, there are no related documents, so click Finish to move to the final steps, where you can name and save the KPI.
  6. Now repeat this process to create the second KPI, which will be used to monitor the margin made on sales. Enter the "Sales and Costs Fact"."% Unit Margin" measure for Actual Value, but this time enter the literal value 40 for Target Valu (because you want to ensure that sales have a target margin of 40 percent). Use the same attribute columns for the KPI dimensionality, and enter the values 25 and 10 as the two thresholds. Once complete, save the KPI to the Web catalog, using the name Margin Maintenance.
Displaying KPIs, Using a KPI Watchlist

Now that you have defined two KPIs, you can give users a means of displaying these on a dashboard through a KPI watchlist. To create a KPI watchlist, take the following steps:

  1. From the Oracle Business Intelligence home page, select New -> KPI Watchlist.
  2. From the Catalog view on the left-hand side of the screen, drag and drop the two KPIs you created previously onto the watchlist on the right. The selected KPIs will then be displayed in a grid format, showing the actual and target values, along with the variance and the percentage variance, as shown in Figure 3.


    Figure 3: The KPI watchlist, showing performance across all product categories

  3. The KPI watchlist will be pinned to the year 2001, per your KPI definitions, but it will enable users to select the product category. To select a specific product category, use the list next to the "Sales History"."Products"."Product Category" label above the KPI watchlist and select a category such as Photo or Peripherals and Accessories. Note how the KPI values are recalculated in response to your product category changes in this dimension and how the status changes to reflect the values compared to the thresholds you chose earlier.
Creating a Scorecard

Now that you have defined your KPIs, you can arrange them into a simple scorecard to help achieve your organization’s goals. Using this scorecard, you will define an objective, "Improve Performance," that breaks down into two contributing objectives, "Increase Sales" and "Maintain Margins." Users will be able to use this scorecard to view progress toward the organization’s goal, either for all product categories or for any one category in particular.

To create the scorecard, take the following steps:

  1. Log in to Oracle Business Intelligence Enterprise Edition 11g using your username and password, and select New -> Scorecard. When prompted, name the scorecard Organizational Performance and click OK to accept the default scorecard settings.
  2. With the new scorecard open, navigate to the Strategy panel at the top left corner of the screen and click the Create Objective button. Name the new objective Improve Performance, and click the Save button just above the text box to save the new objective to the scorecard definition.
  3. With the objective you just created selected in the Strategy panel, repeat this process to create another objective, naming it Increase Sales (this new objective should be located under the Improve Performance objective, to indicate that it contributes to the broader objective).

    When adding this new objective, assign a KPI to it. To do so, use the Catalog panel on the bottom left of the screen and drag and drop the Sales Performance KPI onto the Objectives and KPIs list for the objective. Click OK to save this objective.

  4. Now repeat the previous step, with the Improve Performance objective initially selected—but this time creating a third objective, Maintain Margins—and associate the Margin Maintenance KPI with it.

Your Strategy panel within the scorecard should now have a primary objective, Improve Performance, with two contributing objectives, Increase Sales and Maintain Margins, under it. To complete the initial scorecard, you will now create a strategy tree to show how these objectives are related.

To create a new strategy tree, navigate to the Strategy panel at the top left of the screen and this time click the View Strategy Tree button. When the strategy tree appears, expand the entries under the objectives to see the KPIs and their values, as shown in Figure 4.


Figure 4: Displaying scorecard objectives in a strategy tree

This strategy tree shows that the Sales Performance objective is being met across all product categories but the Maintain Margin objective is not, contributing to a warning about the overall objective. Use the dimension selector above the strategy tree to focus on individual product categories to see how each of them is performing against the targets and the thresholds you defined earlier.


Organizations often use KPIs and scorecards to measure their performance in terms of reaching an objective, setting targets and thresholds to enable them to measure progress across the organization. Oracle Business Intelligence Enterprise Edition 11g gives you the ability to define these objectives and strategies within your BI environment, bringing this important measure of corporate performance to the desktops of everyone on your team.

Next Steps

sample code for this column
Oracle Business Intelligence Enterprise Edition 11g

 READ more about
Oracle Business Intelligence Enterprise Edition 11g
Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1)

Photography by Matic Kozinc, Unsplash