3Custom 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.
Custom KPIs. vs. SSAS KPIs
BI Office supports two distinct types of KPIs, each of which operates separately from the other type.
Custom KPIs
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.
SSAS KPIs
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.
Creating Custom KPIs
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.
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.
KPI Style (in-grid)
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.
Gauges Style
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.
Formulation
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.
Format String
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%).
KPI Model
Select an option to determine how many bands/colors will be employed for the KPI gauge display.
Two Tone
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.
Two Tone Reversed
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: (High-Medium-Low)
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: (Low-Medium-High)
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: (Low-Medium-High-Medium-Low)
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: (High-Medium-Low-Medium-High)
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.
Range Bands
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
- Variable
- 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.
Learn More
To learn about Custom KPIs and Dynamic Banding, see the blogs titled:
