KPI Results

A KPI is a calculated question — one derived from other questions by a formula. kpi_results holds the calculated value per entity, KPI and year, and kpi_calculation_trees holds the full tree of inputs behind each result. Many formulae use a COALESCE function, which takes the first resolvable calculation from many. A “resolvable” calculation is one in which all inputs are populated (i.e. no NOT FOUND or UNPROCESSIBLE inputs). If no resolvable branches are possible, then the kpi_result will be marked as UNPROCESSIBLE. In very rare cases this UNPROCESSIBLE label can also be generated because of an unphysical calculation - e.g. division by 0.

Both views are versioned — filter is_current = TRUE (see Conventions).

`kpi_results`

Column	Type	Description
`kpi_result_id`	VARCHAR	Primary key (`kpi_id`, `entity_id` and `year` combined)
`entity_id`	NUMBER	Who. Joins to `entities`
`question_id`	NUMBER	What. Joins to `questions`
`canonical_question_id`	VARCHAR	The KPI’s readable slug, for convenience
`year`	NUMBER	Fiscal year
`start` / `end`	DATE	Exact period the value covers
`value`	FLOAT	The calculated value, in the KPI’s standard unit
`unit`	VARCHAR	Unit of the value

The KPI’s formula and a plain-language description of it live in kpi_formulae.

`kpi_calculation_trees`

The tree of inputs used to compute each KPI result: every input question, the value used, and how they combine. One row per kpi_result_id.

Column	Type	Description
`kpi_result_id`	VARCHAR	The KPI result this tree belongs to. Joins to `kpi_results`
`calculation_tree`	VARIANT	Nested structure of the formula evaluation in `JSON` form.

The calculation_tree column is JSON (VARIANT in Snowflake); use Snowflake’s semi-structured accessors to drill into it, for example calculation_tree:children[0]:value.

Example: a KPI over time, with its working

SELECT kr.entity_id,
       kr.canonical_question_id,
       kr.year,
       kr.value,
       kr.unit,
       ct.calculation_tree
FROM NET_PURPOSE.FOUNDATIONS.KPI_RESULTS kr
JOIN NET_PURPOSE.FOUNDATIONS.KPI_CALCULATION_TREES ct
  ON ct.kpi_result_id = kr.kpi_result_id AND ct.is_current = TRUE
WHERE kr.is_current = TRUE
  AND kr.entity_id = 12345
  AND kr.canonical_question_id = 'co2_total_1_2_and_3'
ORDER BY kr.year;