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