Dynamic Banding in Custom KPIs - Part 1 (Intro)
By David Novick, Pyramid Analytics Technical Writer
When designing Custom KPIs in BI Office, a powerful feature called "dynamic banding" allows you to pre-configure KPI gauges such that the band ranges will auto-adjust at runtime based on specified conditions, variables, calculations, logical triggers and data values. Dynamic banding helps you to present business results in a more fluid fashion, without the need to build multiple KPI gauges for different scenarios and occurrences.
This blog provides a brief overview of dynamic banding, while additional blogs in this series present detailed examples for working with each of the four banding methods.
There are four methods available for specifying the various band endpoints in a Custom KPI. Each of the four methods has a unique icon for easy recognition, as shown below.
The simplest user approach is to employ a single method for all band endpoints in a Custom KPI. However, you are free to mix the four methods when necessary, applying different methods to different endpoints.
Band endpoints can be specified using fixed values such as 10%, 43, 0.5 or 2.17. The big advantage of fixed values is having full control over the range bands and the knowledge that the bands will not suddenly change. For example, if you have three bands, you could specify the values shown below to create a neat and orderly gauge which will always display with the same range bands.
- Red Band (-10% to -5%)
- Yellow Band (-5% to 5%)
- Green Band (5% to 10%)
For an example of fixed values, see the blog titled Custom KPIs in BI Office.
The "Evaluation Set" method provides a quick and elegant approach for addressing many KPI scenarios. This method allows you to specify an underlying set of evaluation elements on which your Custom KPI will be based. To specify your band endpoints, you then choose from a list of built-in functions which operate on the evaluation set. The functions include:
- Get min/max value in evaluation set.
- Find average/median of evaluation set.
- Divide gauge into 3/4/5 equal parts based on evaluation set.
- Divide gauge using percentile groupings (10/20/30/40/50/60/70/80/90) based on evaluation set.
Variables are used throughout the BI Office for many purposes. Regarding band endpoints, you can reference existing variables or create new ones. Variables that you define for a Custom KPI can be used to change the gauge display interactively at runtime. Since these changes are performed manually by the user, this method is referred to as semi-dynamic.
One simple example would be the use of variables to provide runtime control over a three-band KPI gauge. Each of the following variables could be controlled separately at runtime by the user.
- B1-START (40/60/80%) User selects 40, 60 or 80 as band 1 start point.
- B1-END (80/90%) User selects 80 or 90 as band 1 end point (and band 2 start point).
- B2-END (110/120%) User selects 110 or 120 as band 2 end point (and band 3 start point).
- B3-END (120/140/160%) User selects 120, 140 or 160 as band 3 end point.
NOTE: When creating a variable, the user may assign any desired variable name. The names shown above are arbitrary.
The most flexible banding method involves the creation of a separate calculation set for each of the individual banding endpoints in a Custom KPI. For example, if your Custom KPI has three bands, you could define a separate calculation set for each of the following endpoints.
- Band 1 From (calculation set)
- Band 1 To (calculation set)
- Band 2 To (calculation set)
- Band 3 To (calculation set)
The good news is that you don't need to know MDX or to be a programmer in order to use this feature. Our drag-n-drop interface is easy to use without any need for coding. For each calculation set that you build, you can select one or more functions/items from the following groups:
- Data Points
- Numeric Functions
- Other Functions
- Numeric Values (integers, decimals)
- Mathematical Functions ( + - * / < > = <> %)
- Boolean Functions (AND/OR/NOT)
To learn more about Custom KPIs and Dynamic Banding, see the blogs titled: