Sanjay Sainath asked 2 years ago
Title : How can i customize KPI’s made by an analysis in OBIEE?

Question :

How can i customize KPI’s made by an analysis in OBIEE?

1 Answers
nalinnalin Staff answered 2 years ago

Key Performance Indicators or KPI’s are used to track business goals and objectives. They have measurable values with varying parameters based on the dimensions that evaluate metrics against their target values. It is a simplified visual method to observe the trends and patterns of the KPI measures against different dimensions with alerts indicated when targets aren’t met.
 
 
We designed the following KPI and scorecard for our client. Different KPI Type (CSI, RT Same-Day etc.) were to be shown in the same view with the target values (2nd row here) displayed for current and compared with previous financial year plotted against the different regions as dimensionality.
 
 
To augment the requirements of our business client, instead of using the KPI builder provided by OBIEE for performance management, we made a customized KPI from an analysis to show different KPI types in a single analysis.
 
 
We made a customized table WC_SR_KPI which fetched data from our existing DB tables to design this KPI and then integrated it with CSS and HTML to visualize the trends and alerts.
 
STEP1: Designing the ODI Architecture
 
 
The measures in the KPI Analysis is calculated using the expression
 
                                 (Aggregate function on KPI_MEASURE)
KPI =                             —————————————-
                                          (Sum of NUM_CLOSED_SR)
 
 
The KPI_MEASURE is updated in the table in DB based on logic required by the client for each KPI_TYPE selected. The table is updated using UNION of all KPI_TYPE column value selected. This is done in the ODI. This gives us the freedom to pivot and design the KPI according to the client’s requirement. This freedom is not supported by the vanilla KPI builder provided by OBIEE.
 
 

EXAMPLE: When KPI_TYPE is Average DT hrs the KPI_MEASURE is mapped as sum of DT hrs in the source table defined and when KPI_TYPE is Average RT Hrs then the KPI_MEASURE is mapped as sum of RT Hrs in the source table defined.

 
 
STEP2: Setting the BMM logics in RPD
 
 
The denominator NUM_CLOSED_SR in the RPD has a default aggregation rule of sum. The numerator on the other hand is not set with a default aggregation rule. This gives the liberty to set a custom aggregation rule to the numerator at the OBIEE client.
 
STEP3: Building the Analysis at OBIEE client
 
 
The Aggregation rule for the numerator is defined here. Additional conditional formatting is done using CSS codes for the KPI measures to set the status and colour code with respect to the targets defined by the client.
 
 
EXAMPLE: When KPI_TYPE is Average DT Hours then the numerator is calculated as average of KPI_MEASURE and when KPI_TYPE is DT within 24 Hrs then the numerator is calculated as sum of KPI_MEASURE.
 
 
A scorecard can also be obtained using the same table designed at DB.

Cubastion is among a very small number of specialized Siebel CRM Oracle Gold Partners in SME.