Data model, SQL/ETL and automation

The relationships in the verified Power BI model

The implemented report uses Application Data as its case-level hub. This is the actual model, distinct from the conceptual warehouse layers below.

Single-direction filters. Received date is active; closure date is inactive and activated by the relevant measures. Keep application totals at case grain; do not multiply them by joining several child event tables.

SQL example: validate the intake baseline

SELECT COUNT(*) AS applications,
  ROUND(AVG(CAST(complete_at_intake_flag AS REAL)), 4)
    AS first_pass_complete_rate,
  ROUND(AVG(CAST(manual_touch_count AS REAL)), 2)
    AS average_manual_touches
FROM fact_applications;

Applications

2,400

First-pass complete

0.5804 → 58.0%

Manual touches

9.61 → 9.6

Re-executed against the project’s read-only SQLite database on 5 September 2026. The result supports investigating intake rework; it does not establish causation or demonstrate achieved improvement.