Sunday, 12 June 2011

OBIEE 11g Creating Simple and Self Sustaining KPIs


Key Performance Indicators (KPIs) help organisations understand how well they are performing in relation to their strategic goals and objectives. In the broadest sense, a KPI provides the most important performance information that enables organisations or their stakeholders to understand whether the organisation is on track or not.
A KPI represents the result of business measure evaluated against a target for that measure. It is very useful to assess the performance of the strategic objectives and initiatives that form levels of organizational strategy.
KPIs Characteristics?
KPI must have measurable values that usually vary with time; it can be evaluated against targets to determine score and performance status and can be compared overtime for trending purposes for to identify performance patterns
KPI and Scorecard are an integrated BI Components in OBIEE 11g. This blog is an attempt to describe creating simple and self sustaining KPIs by using OBIEE 11g.

Simple KPI

The task is to create a simple KPI, the problem description is as follows

-       Which compares ‘Billed Quantity’ vs. ‘Target Quantity’ with thresholds at 98%, 96% of target
-       Dimensionalized by Yr, Qtr, Month, Products and Offices
-       Create BI Navigation for 96% thresholds
Open KPI by clicking New and then KPI from the OBIEE 11g Dashboard.

Choose the subject area from the list, e.g. ‘Sample Sales’ out of box subject area which comes as default.
Set General Properties
Set Business Owner, which will have the privilege of overriding the status. Set the actual value to ‘Base Fact - > 2-Billed Quantity’ and target value to ‘Base Fact -> 6-Target Quantity’.
Check Enable Trending and select compare to prior ‘Per Name Month’ which sets the grain of the KPI which will be used to compute the trend. The tolerance of 2 % change has been set.

Set Dimensionality
Select the dimensionality of the KPI by choosing the Per Name Year, Per Name Quarter, Per Name Month, Product Hierarchy and Office Hierarchy

Set Thresholds and BI Navigation as Action Link
Set the thresholds to ‘6-Target Quantity * 0.98’ and ‘6-Target Quantity * 0.96’. This is to set conditional formatting to tell system to color anything from 0-96% to red, 96-98% of target to Yellow and over 98% to Green. Use appropriate icons for thresholds to display in KPI.
Add BI Navigation to ‘Target Quantity’ detail report for 96% threshold by using action link option.

Add related document  
Include name and location for the related documents.

To open the KPI, choose open from OBIEE home page and choose the KPI, the KPI output generated as below. The output has dimensional columns, actual, target, status, variance and % variance for build vs. target quantity.

Self Sustaining KPI
In simple KPI design the actual and target values for KPIs exists in the database referenced by the subject area. These values can be calculated using formulas or by entering a literal values also.
To elaborate this further, a self sustaining KPI is defined as below.

-       Which create a KPI  that compares current years billed quantity against a target of last year(s) billed quantity plus 10%

Create duplicate of Simple KPI by going to the catalog browser/UI and highlighting Simple KPI, then choose Copy, the Paste it and Rename it as ‘Example Self Sustaining’
 Open KPI and edit the Target Value to read - > Ago (‘Base Fact. 2-Billed Quantity’,1) * 1.10  , this looks at last year billed quantity and add 10%.

This is called a self-sustaining KPI as it does not require any special target values to be set or specified in the database. This feature is very useful where target values are not fixed and not stored in database.

Oracle Scorecard is the next I would like to blog about.


  1. nice write up. very helpful. Can you post the actual formula for target? I couldn't really make it out in the image... "Open KPI and edit the Target Value to read - > Ago (‘Base Fact. 2-Billed Quantity’,1) * 1.10 , this looks at last year billed quantity and add 10%. "

  2. Hi.. Sorry as you couldn't make it out of the image .. Please read as Ago("Base Facts"."2-Billed Quantity",1)*1.10. I hope you find this useful.

  3. Hi

    I like this post:

    You create good material for community.

    Please keep posting.

    Let me introduce other material that may be good for net community.

    Source: Database KPIs

    Best rgs