How dynamic KPIs provide better views into constantly changing datasets

November 12, 2020

KPIs play a crucial role by providing an instant and intuitive graphic view of a business’s key performance metrics. In order to effectively create KPIs, analysts must be able to define values for actual, target, status, and thresholds for success bands—preferably with the ability to base all these inputs on live data that is dynamically calculated. Pyramid provides deep support for dynamic KPIs by with actual, target, status, and success band definitions that flex and adapt to changing business conditions.

The problem

Static definitions for KPIs are helplessly inadequate as analysts frequently need to define KPI metrics using dynamic values that adjust to the reality of the current dataset. As data patterns change over time, previously defined static measures become irrelevant and ineffectual, requiring new analysis and definitions to be defined, undermining the entire purpose of creating KPIs.

This results in KPIs being relegated to only solving simple problems and not being a good fit for common, everyday requirements. Many BI tools provide a weak KPI framework—usually driven off static or simplistic metric definitions—making KPI content “lite” at best and “stale” at worst, often requiring constant KPI ‘recalibration’.

Pyramid’s solution

Pyramid provides total flexibility by offering quick, easy-to-use definitions for basic KPIs and a comprehensive approach for more demanding requirements. Separate dynamic definitions for actual, target, status, and success thresholds facilitate easy, powerful KPI definitions. This expedites dynamic definitions that automatically change values as data patterns change over time.

Dynamic parameters can be used to define thresholds, providing a run-time ability to fine tune KPIs for a more attuned banding of success that is drawn from the data itself. Metrics like percentiles, minimum, average, median, and maximum values can be applied with multiple logical inputs, while free-form formulas, using Pyramid’s PQL language, can also be used to determine values for actual, targets, status, and success thresholds with exacting logic.

Business case

Malcolm is a BI Analyst for Stollingers Asset Managers. They use SAP BW for their data warehouse and Pyramid for their analytics. Malcolm is building a dashboard with KPIs for the traders’ clients to view daily movements for their clients’ portfolios. He can quickly and effortlessly build a KPI by defining the daily close price as the actual value, the open price as the target value—and measuring the movement between the two as the KPI “status” (positive, negative and by how much). Pyramid automatically creates the growth percentage calculation as a default value for the status value, but any formula can be used to create the status value using Pyramid’s formula building tool.

At first, Malcolm creates a static definition for the various success bands where the first band is negative 1 up to 0 percent growth, the second band is from 0 to 1.5 percent growth, the third 3 percent, and so on.

By selecting the KPI that he has created, Malcolm views a portfolio of stock returns in a grid per sector and is able to view results with a color coding that accentuates stock performance. By hovering the tooltip over Citrix, we see the growth of 4.5 percent is correctly indicated by the 5th KPI band.

This serves Malcolm well for a period, but after a significant shock in the market, he finds that his conventional approach to evaluating success and failure no longer conforms with current standards. Malcolm’s charts do not immediately indicate performance, as all stock performance is severely depressed.

Malcolm decides to change the method used to define the bands, replacing the static definitions with an evaluation set that will be used to dynamically calculate color and indicator thresholds. Malcolm uses quintiles to define uniform bands.

For the evaluation set, Malcolm selects the company name to dynamically calculate the quintile banding, depending on the dataset and hierarchical grouping in the Discovery.

This provides Malcolm with meaningful thresholds that will always adjust to provide relevant dashboards, regardless of the market conditions. In this Discovery, the companies are grouped by sector, so the bands are dynamically calculated with each sector having quintile banding based on the companies in its sector.

Citrix Systems, with a growth of 1.6% falls into the 4th band for the technology services sector, whereas CSX Corp, with a smaller growth of 0.9% belongs to the 5th band within its relatively weaker-performing transportation sector.

Malcolm would also like a separate dashboard item that measures performance compared to stock prices 90 days ago. Pyramid lets him create a KPI where the target value can be defined dynamically. In this case, the target is the closing price, where the date is 90 days ago (lag).

Using this definition, Malcolm can create a table that displays the current close price (KPI Value), the price 90 days ago (KPI Target), and the percentage growth compared to 90 days ago (KPI Status). Importantly the results are dynamically calculated and display thresholds appropriate to the context of records selected (in this case, sector), so Malcolm doesn’t have to create new KPI ranges for each.


KPIs provide a graphical view of key performance indicators for businesses. Many third-party BI tools only provide static definitions that often require separate KPI definitions per element. Static definitions are inadequate when constantly changing conditions require new definitions to be relevant and meaningful. The result is KPIs that are not used for everyday requirements. Effective KPIs require definitions for actual, target, status, and thresholds for success bands, with the ability to base all values on dynamically calculated, non-static values.

Pyramid offers comprehensive static actual, target, status, and color threshold definitions with multiple KPI styles that can be configured with easy point and click actions. In addition, Pyramid provides dynamic methods for all definitions, so variables like previous periods can be used to define targets and values can change automatically as data patterns change over time. Parameters can also be used to define success thresholds to allow for run-time fine-tuning. Metrics like percentiles, minimum, average, median and maximum values can be applied per dimension or hierarchy. Free-form formulas, using Pyramid’s PQL language, can also be used to determine values for actual, targets, status, and color band thresholds.

With KPIs in Pyramid, users gain a more nuanced understanding of their data. It’s these KPIs that point the way to better decisions, even on complicated, constantly changing data.

Get the latest insights delivered to your inbox