TWENDE PMEAL Dashboard Prototype

What this demonstrates

The TWENDE Project generates monitoring data across four implementing partners using KoBoToolbox, SurveyCTO, and Excel, but these are not integrated within a unified framework. This prototype demonstrates the proposed PMEAL system architecture: a star-schema data model with 6 dimension tables and 5 fact tables, feeding 6 dashboard pages with GIS integration and differentiated user access.

Star schema: dim_landscape, dim_county, dim_partner, dim_indicator, dim_period, dim_intervention
Fact tables: achievement (1,525 rows), beneficiary (5,000), training (400), workplan (180), GRM (80)
Global slicers (period, landscape, partner) filter across all pages
Row-level security designed for 5 user tiers
GIS integration via Azure Maps with county-level bubble visualisation

Power BI Desktop DAX Star Schema Azure Maps Python (data gen) KoBoToolbox SharePoint

Dashboard pages

Each page maps to a PMEAL module. Click any screenshot to view full size.

Page 1

Project Overview

KPI cards for beneficiaries reached (5,000), hectares under restoration (1.05M), female percentage (51%), and overall log frame completion (112%). Quarterly achievement trend lines by indicator level. Beneficiaries by landscape bar chart and progress-by-component donut.

Page 2

Beneficiary Database

Individual-level tracking across 5,000 records. Reach by county, gender distribution (51% female), age group breakdown (60% adult, 25% youth, 15% elder), vulnerability status (16%), and reach by intervention type across all eight categories.

Page 3

Spatial Map

Azure Maps visualisation plotting beneficiary reach as proportional bubbles on county centroids across Kenya's ASAL region. County summary table showing beneficiary count and training participant totals grouped by landscape (Chyulu, Mid-Tana, Sabarwawa).

Page 4

Indicator Performance Tracking Table (IPTT)

Cumulative achievement versus target for each log frame indicator (32 indicators across 3 components + cross-cutting). Designed for traffic-light conditional formatting enabling the MEAL team to identify on-track, at-risk, and behind-schedule indicators.

Page 5

Partner Performance

Activity completion and average indicator achievement compared across all four implementing partners (IUCN, MoAI-SDLD, NDMA, Conservation International). Workplan status stacked bar chart showing completed, in-progress, delayed, and not-started activities per partner.

Page 6

Risk and Grievance (GRM)

80 cases tracked across four categories (social, operational, environmental, fiduciary). Resolution pipeline from open through escalated to resolved. Full register table with GRM ID, severity, county, partner, status, and reporting/resolution dates.

Data model & DAX

The Power BI solution is built on a star schema designed for cross-filtering, row-level security, and performance at scale.

Star Schema

Dimension Tables

dim_landscape - Chyulu, Mid-Tana, Sabarwawa

dim_county - 11 ASAL counties with GPS centroids

dim_partner - IUCN, MoAI-SDLD, NDMA, CI

dim_indicator - 32 log frame indicators, 3 components

dim_period - Quarterly reporting calendar

dim_intervention - 8 intervention categories

Fact Tables

fact_achievement - 1,525 rows: indicator results vs targets by period

fact_beneficiary - 5,000 records: individual-level reach with demographics

fact_training - 400 events: capacity building with pre/post scores

fact_workplan - 180 activities: status tracking per partner/quarter

fact_grm - 80 grievance cases: category, severity, resolution pipeline

DAX Measures

Selected DAX measures powering the dashboard KPIs and conditional formatting.

// Total unique beneficiaries reached
Total Beneficiaries = DISTINCTCOUNT(fact_beneficiary[beneficiary_id])

// Achievement rate with safe division
Achievement % = DIVIDE(
    SUM(fact_achievement[actual_value]),
    SUM(fact_achievement[target_value]),
    0
)

// Female participation rate
Female % = DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(fact_beneficiary[beneficiary_id]),
        fact_beneficiary[gender] = "Female"
    ),
    [Total Beneficiaries],
    0
)

// IPTT traffic light (conditional formatting)
RAG Status = SWITCH(
    TRUE(),
    [Achievement %] >= 0.9, "Green",
    [Achievement %] >= 0.7, "Amber",
    "Red"
)

// Year-on-year change with DATEADD
YoY Change = 
    VAR CurrentPeriod = [Total Beneficiaries]
    VAR PriorPeriod = CALCULATE(
        [Total Beneficiaries],
        DATEADD(dim_period[date], -1, YEAR)
    )
    RETURN DIVIDE(CurrentPeriod - PriorPeriod, PriorPeriod, 0)

Design Decisions

Row-Level Security

5 tiers: Global admin, Programme manager, Landscape coordinator, Partner focal point, Donor (read-only). Each tier filters fact tables through DAX RLS expressions on dim_partner and dim_landscape.

Global Slicers

Period, landscape, and partner slicers are synced across all 6 pages. Users drill through from Overview to any detail page without losing filter context.

Power Query M

Data ingestion from SharePoint lists and CSV files via Power Query. Custom M transformations handle date parsing, column typing, and partner name standardisation before loading to the model.

Data note: All data in this prototype is synthetic, generated using a deterministic Python script with realistic distributions. No real beneficiary data, GPS coordinates, or identifying information are present. The data model, dashboard layouts, and DAX measures are production-ready and designed to connect to live SharePoint data sources.

← Back to Portfolio