Custom KPIs designed in Pyramid are comprised of actual values, target values and status values.
Status values are a logical comparison of the actual to the target to express whether the actual is dong better, worse or the same in comparison to the target value. To make the analysis meaningful, the status values need to be reviewed in the context of ranges or 'bands' which are the numerical ranges that describe and define 'better', 'worse' and 'the same'.
Note: Data model driven KPI's in MS OLAP and Tabular models use predetermined bands of -1, 0, 1. They cannot be changed or manipulated from inside Pyramid.
KPI Models and Bands
Start by enabling dynamic banding from the Bands panel int he KPI designer.
In the Custom KPI designer, users first pick a status 'model' to describe the type of comparison logic they wish to employ:
- Dual/Dual Reverse: comprises two bands denoting better or worse, or vice versa
- Tri/Tri Reverse: three bands denoting better, the same, or worse, or vice versa
- Pent/ Pent Reverse: five bands denoting much worse, worse, the same, better, and much better, or vice versa
The default colors for KPI banding are green, yellow, and red. You can change these colors in Discover, once you add the KPI to a query. See Customize KPI Colors and Bands for details.
Once the model is chosen the designer will reflect the banding levels and the direction of the logic (red highlight below). For each band, the user needs to provide the numerical values that will determine the boundaries of each band.
In the example below, the user needs to supply 4 values.
- The lowest value (green arrow)
- The boundaries of the next to bands (orange arrows)
- The highest value (red arrow)
Since the boundary of one band is automatically the boundary of the subsequent band, there is no need to fill in these values (purple arrows).
For each boundary, the user can use 4 methods for supplying a value using the value drop down for each band boundary (red highlight above).
Note: Each boundary can use a different technique.
Static: is a static number. User should ensure that all the static numbers are logically in order for each boundary to avoid errors
Formula: is a dynamic value generated using a custom calculation
Parameter: is a (semi) static value generated from user input during runtime driven off a parameter
Evaluation Set: is a dynamic value generated from querying statistics from a set of figures
These are explained further below.
These are the simplest boundary values to provide for KPI bands. They are simply hard coded, static numbers that represent the boundaries for the different bands in the KPI definition.
Note: care should be taken to ensure that the band values are in numerical order to avoid errors.
Formulas provide the most flexible and powerful technique for defining dynamic bands.
Each boundary element can be set to a custom formula. When selected as such, a new tab appears in the Custom KPI designer (red highlight below).
Click here for details about building formulas.
Parameters provide a UI based element to allow end users to supply values into calculations during runtime. In much the same way, variables can be used to define boundary values for one or more bands. As such, parameters provide a semi-static interface for KPI banding.
Click the folder icon and select the required parameter from the folder tree.
Evaluation sets are powerful technique for creating dynamic boundary values quickly.
First, the user needs to pick a set of elements in the data model from which the values will be drawn (red highlight above). Clicking the button will present the dialog below. Here, the user selects elements from the relevant hierarchy in the model using either hierarchy levels, previously defined custom sets, or a free selection of elements (green highlight below).
IMPORTANT: the Evaluation Set capability is NOT operational without a set definition.
Then, they can set for each boundary the type of statistic to be drawn from the set.
- Select the evaluation set as the boundary logic
- Pick a statistic from the drop down box (blue highlight above) and repeat for each boundary as needed
The statistics available are:
- Max: use the max value as the boundary.
- Min: use the max value as the boundary.
- Average: use the average value as the boundary.
- Median: use the median value as the boundary.
- Tiles: select a percentile and use it as the boundary. You may select from:
Evaluation Set Statistic Values
The values driving the boundaries are derived from running the target value calculation in the KPI definition for each element in the evaluation.