Centralised Dashboards
for Subcontractor Management
CLIENT
Mid-sized commercial/industrial general contractor
INDUSTRY
Commercial & industrial construction
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
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
- 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 — rank and compare subcontractors across projects.
- Cost & Risk Heatmap — highlight top risk subs by change-order cost and corrective ratio.
- Portfolio Overview — aggregate subcontractor KPIs across regions and time periods.
- 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
Result
Within three months of implementation, the contractor completely transformed how subcontractor performance was tracked and managed.
Before, leadership relied on inconsistent spreadsheets and delayed status updates; today, decision-makers have a real-time, data-driven overview of every subcontractor across projects.
The Snowflake-based data warehouse became the central nervous system of subcontractor management – connecting ERP, scheduling, and field systems into one cohesive source of truth. Automated pipelines ensure every new invoice, delay log, or change order appears in Power BI dashboards within hours, giving executives the ability to react while projects are still in motion.
Project directors use live dashboards to identify vendors showing early signs of delay or quality issues, enabling immediate reallocation or escalation. This proactive oversight reduced subcontractor-related overruns by 17% in the first six months and improved accountability across the entire vendor network.
Reporting cycles that once required nearly a week of manual data collection were cut to less than a day, freeing PMO teams to focus on strategic planning instead of spreadsheet reconciliation. Executive adoption reached 100% within the first month, as leaders gained trust in the accuracy, consistency, and clarity of the data they now use for daily operations and quarterly board reviews.
The company now benchmarks every subcontractor by measurable KPIs, using insights from the Snowflake–Power BI ecosystem to inform future bid decisions and strengthen long-term project outcomes.
Schedule a Free Session with our Data Experts
Flexible, fast, and focused — Sphere solves your tech challenges as you scale.
Luke Suneja
Client Partner
