Horizon League Budget Dashboard
liveAthletic budget benchmarking platform for 11 NCAA Division I universities
What It Is
A web-based athletic budget benchmarking and analytics platform for 11 NCAA Division I universities in the Horizon League conference. It replaces a Power BI dashboard, providing Athletic Directors with cost-effective access to compare athletic department finances across peer institutions: budgets, basketball operations, staff salaries, NIL/COA compliance, department operations, facilities, and multi-year trends.
The migration from Power BI to Next.js on Azure Static Web Apps eliminates per-user licensing costs, achieving significant cost savings while maintaining the analytical depth ADs need for million-dollar budget decisions.
Architecture
- Frontend: Next.js App Router with server-side rendering and API routes deployed as Azure Functions within Static Web Apps.
- Authentication: Zero-code Azure Entra ID via SWA middleware. Multi-tenant configuration allows staff from partner institutions to authenticate with their own Microsoft accounts. User identity extracted from the SWA client principal header.
- Database: Azure SQL (Serverless 2 vCore) accessed via Managed Identity in production (zero credentials in code) and SQL auth in development. All queries parameterized to prevent injection. Read-only database role enforced.
- Data Model: Star schema with a central survey submission table (per institution, per year, per revision) linking to fact tables for athletics budgets, sport budgets, department budgets, staff salaries, NIL collectives, Alston/COA payments, facilities, and staff counts.
Data Sources
- Annual Athletic Director Survey: Qualtrics-based self-reported financial data from each institution, ingested via Python ETL scripts.
- EADA (Equity in Athletics Disclosure Act): Official federal reporting data imported as a secondary source.
- Data refresh is semi-static (annual survey cycle) with API responses cached for one hour.
Current State
The application is deployed on Azure Static Web Apps with GitHub Actions CI/CD. All eight pages are functional and connected to Azure SQL. Authentication works for both internal staff and partner institution B2B guest accounts. The Stitch design system (Horizon League orange and dark navy) is applied consistently. Remaining polish items include enhanced CSV export, mobile layout testing, custom domain setup, and accessibility audit.
Devlog
With the database populated and the technical spec finalized, I built and deployed the entire Next.js application in a single day. Nine commits between 8:33 AM and 10:25 PM, starting with the full application code and ending with a working production deployment behind multi-tenant authentication.
The initial commit was the complete app: 8 pages, 8 API routes, 4 chart components, navbar, KPI cards, data tables, and the Stitch design system applied throughout. Every page connects to Azure SQL via parameterized queries through a connection pool that auto-detects whether to use Managed Identity (production) or SQL auth (local development). The design system uses Horizon League orange for accents and headings, dark navy for chart data and the header bar, and Material Symbols icons loaded from Google Fonts. Each page has a year selector that filters all data and charts dynamically.
The deployment pipeline took several iterations. The first attempt used Next.js standalone output mode, which is incompatible with Azure Static Web Apps because SWA needs to deploy API routes as Azure Functions. Removing standalone mode and fixing the routing configuration in staticwebapp.config.json got the build working. I added security headers (X-Content-Type-Options, X-Frame-Options) at the same time.
Authentication was the most iterative part of the day. The first Entra ID configuration used a tenant-specific issuer, which only allows users from the SII Azure AD tenant. I switched to the multi-tenant `/common` issuer to allow staff from partner institutions to log in with their own Microsoft accounts, but that caused auth redirect loops. Reverting to tenant-specific fixed the loops but broke guest access. On the fourth attempt, I got the multi-tenant configuration working correctly. The final setup allows both SII internal accounts and B2B guest accounts from any Horizon League institution to authenticate without additional licensing.
All API responses are tagged with one-hour cache headers, which is appropriate for semi-static annual survey data. The GitHub Actions pipeline auto-deploys on push to main, with pull request preview deployments for testing.
The app is now live on Azure Static Web Apps at a cost of $59/month total (SWA $9 + Azure SQL $50), compared to the $250+/month that the Power BI path would have cost.
What's next: Custom domain setup (dashboard.horizonleague.org), mobile responsiveness testing, CSV export feature, and user acceptance testing with the Horizon League Athletic Directors.
Changelog
Added
- Add Next.js 16 application with 8 dashboard pages (League Overview, Basketball, Sport-by-Sport, Salaries, NIL/COA, Dept Ops, Facilities, Trends)
- Add 8 API routes connected to Azure SQL with parameterized queries
- Add Stitch design system: Horizon League orange accents, dark navy data, Material Symbols icons
- Add Navbar with 8 tab navigation and active state highlighting
- Add KPICard component with trend indicators (green up, red down)
- Add chart components: HorizontalBar, BudgetLineChart, ScatterChart, DonutChart (Recharts)
- Add DataTable component with TanStack React Table
- Add YearSelector dropdown filter
- Add Azure Entra ID multi-tenant authentication via SWA built-in middleware
- Add Managed Identity for credential-free Azure SQL access in production
- Add GitHub Actions CI/CD pipeline with auto-deploy on push to main
- Add security headers: X-Content-Type-Options nosniff, X-Frame-Options DENY
- Add API response caching (Cache-Control: public, max-age=3600)
Fixed
- Fix SWA deployment by removing standalone output mode (incompatible with Azure Functions)
- Fix auth redirect loops by correcting multi-tenant issuer configuration (4 iterations)
- Fix routing and CSP configuration in staticwebapp.config.json
Devlog
I spent the first month on this project building the data foundation and evaluating frontend options before writing a line of web application code. The Horizon League Budget Dashboard replaces a manual Excel consolidation process that Athletic Directors at 11 NCAA Division I universities use to benchmark their athletic department finances against peer institutions. The existing workflow was error-prone, had no audit trail, and could not handle mid-year resubmissions.
The database came first. I designed a star schema on Azure SQL with 11 dimension tables and 7 fact tables covering institutions, sports, survey submissions, athletics budgets, sport-level budgets, department operations, staff salaries, NIL collectives, Alston/COA payments, and basketball facilities. Two Python ETL scripts handle data loading: one ingests Qualtrics survey responses (the annual AD survey), the other pulls EADA official federal reporting data. By late February, 55 legacy submissions spanning survey years 2021 through 2025 were loaded along with 670+ staff salary records.
I built three iterations of a Power BI dashboard connected to the Azure SQL database: seven pages covering league overview, basketball deep dive, sport-by-sport, salaries, NIL/COA, department operations, and year-over-year trends. The dashboard worked well functionally, but the licensing math killed it. Power BI Pro costs $10-20 per user per month, and with 20 users across 11 institutions, that is $200-400/month in licensing alone on top of the $50/month Azure SQL cost. A custom Next.js web app on Azure Static Web Apps would cost $9/month for hosting, with the same $50 SQL cost, bringing the total to $59/month. That is 73% savings.
The pivot decision was not just about cost. Power BI requires every user to have a Microsoft license, which creates friction for guest users from partner institutions. Azure Static Web Apps has built-in Entra ID authentication that supports multi-tenant guest accounts out of the box, so institutional staff can log in with their existing Microsoft credentials without any additional licensing.
The technical specification documents (8 spec files covering problem statement, data audit, survey design, schema, pipeline, dashboard design, implementation tasks, and web app exploration) were finalized by February 25, providing the complete blueprint for the Next.js build.
What's next: Building the full Next.js application with all 8 dashboard pages, connecting it to the Azure SQL database, and deploying it on Azure Static Web Apps with Entra ID authentication.
Changelog
Added
- Add Azure SQL star schema: 11 dimension tables, 7 fact tables (db/schema.sql, 360 lines)
- Add supplementary schema for facilities and staff counts (db/schema_additions.sql)
- Add survey_import.py ETL for Qualtrics survey responses and CSV backfill
- Add dashboard_import.py ETL for EADA federal reporting data
- Add 55 legacy submissions (survey years 2021-2025) loaded into Azure SQL
- Add 670+ staff salary records for benchmarking analysis
- Add Power BI dashboard (3 iterations) with 7 pages connected to Azure SQL
- Add 8-document technical specification covering full architecture
- Add cost analysis: Power BI ($250+/month) vs Next.js + SWA ($59/month)
Changed
- Update project direction from Power BI to custom Next.js web application (73% cost savings)