Methodology

Data model

Tables

• parameters — project parameters and switches (e.g., base/index modes)
• ratio_norms — norm bands and thresholds per KPI
• years — helper dimension for Latest/Base and filtering
• ratios — measure table: KPIs, YoY, status, colors
• financials_long — fact table (yearly values, long format)
• palette — status colour (hex) for legend dots

Relationships

• Star join — financials_long[Year] → years[Year] (many-to-one).
• Single model — both dashboard pages read from the same model; no page‑specific fields.

Norms and flags

• Soft band — each KPI has a neutral zone (Norm Low…Norm High) set in the model and reflected in visuals.
• Inputs per KPI — Norm Low , Norm High , better_is ∈ {'{higher, lower}'}
• Status buckets — below — value < Norm Low · at_norm — within band · above — value > Norm High
• Directionality — if better_is = higher , moving upward is good; if better_is = lower , moving downward is good.
• Colour mapping — status → hex via the palette ; the same mapping drives badges, legend dots, and bars.

Visual layer

Deneb (Vega‑Lite)

• kpi_bars_numeric.vl.json — bars for absolute‑value KPIs
• kpi_bars_percent.vl.json — bars for percent KPIs
• legend_status.vl.json — compact legend using palette/status
• row_labels.vl.json — left‑aligned KPI labels

Slicer and layout

• Year slicer — single‑select; Latest/Base handled by measures.
• Two‑page setup — v1 — rounded cards (site style); v2 — flat, print‑friendly for Word.

Reproducibility checklist

• Measures — versioned and documented
• JSON specs — stored with report for git‑diff transparency
• Data — anonymised sample for structure only