How christopherharris13/spsops-dashboard works
This repository documents a Springfield Public Schools analytics workflow: DESE CSVs are manually loaded into Azure SQL staging, transformed into a dimensional model and reporting views, then imported into a Power BI report. The report covers operations, finance, and data quality. The graph follows documented SQL stages and capabilities; source excerpts were unavailable, so it does not assume undocumented table-level wiring or dashboard internals.
Main entry points: SPSOps.pbix Power BI report · 02_staging_ddl.sql Raw staging tables · District_Expenditures_by_Spending_Category_20260715.csv Expenditure CSV · Enrollment__Grade,_Race_Ethnicity,_Gender,_and_Selected_Populations_20260715.csv Enrollment CSV
Components 18
Source and loading
- Expenditure CSV
data-raw/District_Expenditures_by_Spending_Category_20260715.csv - Enrollment CSV
data-raw/Enrollment__Grade,_Race_Ethnicity,_Gender,_and_Selected_Populations_20260715.csv - Staffing CSV
data-raw/Staffing__Race_Ethnicity_and_Gender_20260715.csv - Attendance CSV
data-raw/Student_Attendance_20260715.csv - Manual CSV loader
SQL warehouse
- Schema setup
sql/01_schemas.sql - Staging definitions
sql/02_staging_ddl.sql - Raw staging tables
sql/02_staging_ddl.sql - Staging checks
sql/03_verify_staging.sql - Dimensional model load
sql/04_star_schema.sql - Azure SQL Database
- Curated reporting views
sql/05_views.sql - Data-quality views
sql/06_data_quality.sql
Power BI reporting
- Power BI report
pbix/SPSOps.pbix - Operations page
pbix/SPSOps.pbix - Finance page
pbix/SPSOps.pbix - Data Quality page
pbix/SPSOps.pbix
Other components
- Massachusetts DESE
Connections 21
- Massachusetts DESE → Expenditure CSV: publishes
- Massachusetts DESE → Enrollment CSV: publishes
- Massachusetts DESE → Staffing CSV: publishes
- Massachusetts DESE → Attendance CSV: publishes
- Expenditure CSV → Manual CSV loader: loaded from
- Enrollment CSV → Manual CSV loader: loaded from
- Staffing CSV → Manual CSV loader: loaded from
- Attendance CSV → Manual CSV loader: loaded from
- Manual CSV loader → Raw staging tables: imports into
- Schema setup → Azure SQL Database: creates schemas
- Staging definitions → Azure SQL Database: defines tables
- Raw staging tables → Staging checks: checked by
- Raw staging tables → Dimensional model load: transformed from
- Dimensional model load → Azure SQL Database: builds dimensions and facts
- Azure SQL Database → Curated reporting views: serves model data
- Azure SQL Database → Data-quality views: serves validation data
- Curated reporting views → Power BI report: imported by
- Data-quality views → Power BI report: imported by
- Power BI report → Operations page: presents
- Power BI report → Finance page: presents
- Power BI report → Data Quality page: presents