Dynamic Banding in Custom KPIs - Part 2 (Evaluation Sets)
By David Novick, Pyramid Analytics Technical Writer
When designing Custom KPIs in BI Office, an innovative feature called dynamic banding helps you optimize runtime gauge display. There are four banding methods, as discussed in the blog titled Dynamic Banding in Custom KPIs - Part 1 (Intro).
This blog, Part 2 in the series, explains the "Evaluation Set" method in detail by presenting a simple example which compares current quarter sales to previous quarter sales for 8 different products. In this example, the use of an Evaluation Set ensures that the Custom KPI displays efficiently for ALL potential sales results, including both historical and future data. Without the use of an Evaluation Set, we would have to manually adjust gauge values for each possible data set, a very time consuming task indeed.
When sales results for all 8 products are close to a statistical average, the Evaluation Set causes the gauges to display three color bands with relatively tight knit ranges. Some sample ranges are shown below, but bear in mind that the actual ranges would change according to the content of the Evaluation Set.
- Red Band (81.5 - 94.3%)
- Yellow Band (94.3 - 105.1%)
- Green Band (105.1 - 121.8%)
When sales results for the 8 products are more widespread, the Evaluation Set causes the gauges to display three color bands with broader ranges. Some sample ranges are shown below.
- Red Band (42.2 - 84.7%)
- Yellow Band (84.7 - 121.9%)
- Green Band (121.9 - 164.4%)
Please note that the range numbers in these two samples contain decimals (and not integers). This is because we are working with an Evaluation Set that calculates ranges by averaging a collective set of values.
In order to define a custom KPI, we need to configure three items.
- Actual Value - Represents the level of actual performance.
- Target Value - Represents the intended target of performance against which the actual value is to be measured.
- KPI Status – Compares between the actual and target values, based on a mathematical formulation that we select.
In this example, the Actual Value will be generated based on sales figures for the current member.
In this example, the Target Value will be generated based on sales figures for the previous member.
For KPI Status, we make our KPI Status selections, as shown below. For more information on these fields, see the blog titled Custom KPIs in BI Office.
When designing a Custom KPI in BI Office, there are four methods available for specifying the various band endpoints. Each of the four methods has a unique icon for easy recognition, as shown below.
For this example, we use the “Evaluation Set” method for all six of our band endpoints.
Range Band 1 (Start Point)
For the start point of range band 1, we select the “Minimum” function. At runtime, the query will search for the lowest value among the Evaluation Set elements.
For the end point of range band 1, we select the "Tertile 1" function. At runtime, the query will calculate the optimal Tertile 1 value based on the collective content of the Evaluation Set.
NOTE: Tertiles are the two points that divide a gauge into three parts.
The start point of range band 2 is set automatically as the end point of range band 1.
For the end point of range band 2, we select the "Tertile 2" function. At runtime, the query will calculate the optimal Tertile 2 value based on the collective content of the Evaluation Set.
The start point of range band 3 is set automatically as the end point of range band 2.
For the end point of range band 3, we select the “Maximum” function. At runtime, the query will search for the highest value among the Evaluation Set elements.
Now we click on the Evaluation Set button to specify the elements of the set. It’s important to emphasize that for each Custom KPI, a single Evaluation Set can be defined. All the end points in the Custom KPI will reference the same Evaluation Set.
We now take four actions to specify the Evaluation Set:
- We choose the Dimension named “Product”.
- We choose the Hierarchy named “Product”.
- We click the Free Selection option because in this example we wish to hand pick our Evaluation Set.
- We click the items button to proceed.
In this example, we select 8 products to populate our Evaluation Set. The runtime data values for these 8 products will be used to generate our range bands.
This grid sample displays the 8 selected elements along with the appropriate in-line “Shape” icons. The icon colors match the colors of the bands.
The 8 gauges shown below are driven dynamically by the content of the evaluation set. As we stated earlier, a single Evaluation Set is referenced by all of the end points. For this reason, all 8 gauges display identical color bands and gauge values.
The various gauge items for a Custom KPI are shown here in detail.
One very important point to keep in mind is that the Evaluation Set remains in force as the source of all gauges displayed for the Custom KPI, regardless of which elements we pick in the Selection Panel. In the example below, we use the Selection Panel to pick a single product (Mountain-200 Silver, 38). This product is NOT one of the 8 products in our Evaluation Set, but nonetheless our gauge settings remain intact because they are based on the Evaluation Set for the Custom KPI (and are not influenced by Selection Panel selections).
To learn more about Custom KPIs and Dynamic Banding, see the blogs titled: