Construction
Centralised Dashboards for Subcontractor Management
- Client
- Mid-sized commercial/industrial general contractor
- Service
- Subcontractor data integration|Dashboard design & deployment|Real-time performance monitoring|Executive visibility
Overview
The client’s subcontractor ecosystem was fragmented: each project team used separate spreadsheets, vendor portals and project-management tools to track subcontractor commitments, performance, change-orders and payments. Executives lacked a unified view of subcontractor health, cost impact and risk. Leadership was making decisions with outdated or incomplete data, leading to delayed responses, cost overruns and scheduling conflicts.
The contractor engaged Sphere to break down data silos, unify subcontractor-related information across ERP and field updates, and deliver a live dashboard layer. So leadership could make faster, more confident decisions regarding subcontractor performance, allocation and cost.
Challenges
- Scattered subcontractor data: Subcontractor agreements, commitments and performance lived in the ERP; field updates and punch lists were managed in separate project systems or spreadsheets. No single repository or view of subcontractor cost vs. performance across projects.
- Manual and inconsistent reporting: PMOs compiled subcontractor performance and cost data manually each quarter; reporting lagged by 5–7 business days. Inconsistent metrics and definitions (e.g., “subcontractor delay” or “corrective cost”) reduced trust in the data.
- Risk and cost exposure: Without early warning mechanisms, subcontractor issues (poor performance, high change-orders, corrective work) caused ripple effects on schedule and budget. Cross-project benchmarking of subcontractor performance was impossible.
- Limited visibility for leadership: Executives lacked up-to-date metrics on subcontractor schedule adherence, change-order frequency, corrective work or cost variance. Delays and overruns often surfaced too late to be addressed proactively.
Our Solution
We delivered a 10-week engagement broken into key phases:
Phase 1 — Data Integration & Database Creation with Snowflake Lakehouse
- Data Collection and Staging: We connected to the client’s ERP (financials, POs, invoices), and Excel field logs using Fivetran and custom Python ETL jobs. Raw data was staged in a Snowflake Lakehouse environment hosted on AWS.
- Database & Schema Design: Built a star-schema model with dimension tables for subcontractors, trades, projects, and cost categories. Created fact tables for commitments, change-orders, corrective work, and schedule adherence. Designed cross-system ID mapping to standardize subcontractor names and vendor codes.
- Establishing the Data Pipeline: Implemented automated nightly ingestion jobs via Airflow orchestration, ensuring near real-time updates. Applied data-quality checks (row counts, schema drift detection, duplicate subcontractor logic). Each dataset versioned and logged using Snowflake Streams and Tasks, maintaining lineage and traceability.
Outcome: A single governed data warehouse in Snowflake served as the authoritative source for subcontractor analytics — replacing spreadsheets and manual exports entirely.
Phase 2 — Dashboard Development & Visualization via Power BI
- Metric Framework: Defined key metrics with project leadership — on-time performance by subcontractor/trade, change-order frequency and total value, corrective work hours and cost impact, subcontractor cost contribution by project, and a vendor reliability index (composite score combining schedule, cost, and quality metrics).
- Dashboard Implementation: Built interactive Power BI dashboards using Snowflake’s direct connector (live query). Developed three executive views: Subcontractor Scorecard, Cost & Risk Heatmap, and Portfolio Overview. Embedded dashboards into Microsoft Teams and mobile Power BI app for field access. Added automated alerts and weekly email summaries for under-performing vendors.
Outcome: Executives gained live access to accurate subcontractor data, refreshed nightly. Field directors could track under-performing subs in real time, and PMOs no longer prepared static Excel reports.
Phase 3 — Governance, Training & Change Management
- Trained project directors, PMOs, and procurement teams to interpret dashboards and submit data correctly.
- Introduced data-entry validation rules in ERP and schedule tools to enforce subcontractor ID consistency.
- Established bi-weekly review meetings with project leaders to discuss dashboard insights and risk forecasts.
- Created an internal “Subcontractor Health Index” used in future bid evaluations.
Why This Approach Works
- Rather than retrofitting multiple dashboards, we started by integrating all subcontractor-related data to ensure consistent, trusted metrics.
- Focused on how leadership views subcontractor risk and cost, not just operational tracking.
- Without enforcing consistent data practices and roles, dashboards lose reliability.
- The dashboard model can be extended to scheduling risks, resource allocation and overall subcontractor health beyond cost.
Key Achievements
- Unified data across ERP, scheduling, and field systems exposed hidden inefficiencies and recurring change-order patterns, allowing leadership to act early and contain costs.
- Automated ETL pipelines in Snowflake and live Power BI dashboards replaced manual roll-ups, delivering real-time visibility into subcontractor performance and project health.
- With consistent, accurate data and intuitive visual dashboards, executives and project directors began using the system daily for decision-making and risk oversight.
- Snowflake Lakehouse consolidated financial, operational, and schedule data to create a governed, scalable foundation for future analytics, forecasting, and vendor benchmarking.
