Custom KPIs in BI Office
By David Novick, Pyramid Analytics Technical Writer
In today’s performance driven world, the use of Key Performance Indicators (KPIs) is becoming increasingly popular throughout all business sectors. Managers are demanding smarter KPI capabilities for tracking employee performance, measuring team production and monitoring sales results. Pyramid Analytics provides a powerful toolset for designing custom KPIs for governed corporate data, including flexible display options, configurable real-time triggers and dynamic banding.
BI Office supports two distinct types of KPIs, each of which operates separately from the other type.
Created within BI Office and support dynamic banding and advanced formatting options. These KPIs appear in the Measures Panel as custom elements with “color-band” icons, as shown below.
Imported into BI Office “as is” from SQL Server Analysis Services. For these KPIs, you can perform basic formatting changes only. SSAS KPIs appear in the Measurements Panel with “traffic light” icons, as shown below. For more info, see the blog titled SSAS KPIs in BI Office.
To create a custom KPI:
- Open the Analytics ribbon in Data Discovery.
- Click the Calc Designer icon.
Now click Custom KPI to open the Custom KPI Designer.
To build a Custom KPI, you will need to configure the following three items.
KPI Status - Shows the comparison between the actual and target values. For each Custom KPI you create, you can specify various status settings, as described below.
Actual Value - Represents the level of actual performance. See “custom members” in the BI Office help for details on how to define members/calculations.
Target Value - Represents the intended target of performance against which the actual value is to be measured. See “custom members” in the BI Office help for details on how to define members/calculations.
Choose the desired style for the in-grid KPI icons.
An example of the “Signs” type is shown below. Note that "Sign" icons indicate status by means of both their color and their direction.
You can place your mouse over an in-grid icon to display KPI statistics, as shown below.
Select the desired KPI gauge style.
An example of the “Radial” style is shown below. For more information on editing gauges, see the blog titled Editing of KPI Gauges.
Choose the desired formula to be used for generating the status value.
In the following equations, [A] stands for actual and [T] stands for target:
- Ratio [A] / [T]
- Reverse Ratio [T] / [A]
- Growth Percentage ([A] - [T]) / [T]
- Variance Percentage ([A] - [T]) / [A]
- Difference [A] - [T]
- Custom Formulation Build a custom equation using [A] and [T].
In the example shown below, the status value (117.04%) is calculated using the Ratio formula.
Select the desired format mask for displaying KPI status alongside the gauge.
NOTE: For a description of the Custom option, see “Custom Value Formatting” in the BI Office help system.
In this example, the status value appears as a percentage (117.04%).
Select an option to determine how many bands/colors will be employed for the KPI gauge display.
Two Tone displays the KPI in two colors (red/green). This example contains two color bands with fixed values, as shown below.
The order of the band colors (red/green) is reflected in the gauge.
The Two Tone Reversed option reverses the colors to be green/red, while leaving the fixed values the same.
The order of the band colors (green/red) is reflected in the gauge.
Standard 1 Tail displays the KPI in three colors (red/yellow/green). This example contains three color bands with fixed values, as shown below.
The order of the band colors (red/yellow/green) is reflected in the gauge.
Reverse 1 Tail reverses the color order to be green/yellow/red, while leaving the fixed values the same.
The order of the band colors (green/yellow/red) is reflected in the gauge.
Standard 2 Tail displays the KPI in five colors (red/yellow/green/yellow/red). This example contains five color bands with fixed values, as shown below.
The order of the band colors (red/yellow/green/yellow/red) is reflected in the gauge.
Reverse 2 Tail reverses the color order to be green/yellow/red/yellow/green, while leaving the fixed values the same.
The order of the band colors (green/yellow/red/yellow/green) is reflected in the gauge.
For each band endpoint in the KPI gauge, you are required to specify a value using any of the four following methods.
- Fixed Value
- Evaluation Set
- Custom Calculation
You can use the same method for all band endpoints in the KPI gauge, or you can mix between the different methods.
In this example, all band endpoints are assigned fixed values. Note that each successive band automatically inherits its start value from the previous band.
- Band 2 inherits its start value of 0.95 from Band 1.
- Band 3 inherits its start value of 1.05 from Band 2.
To learn about Custom KPIs and Dynamic Banding, see the blogs titled: