Data Analysis, EBM Software, General, Reporting
How AGS brought 6+ data sources and 15+ KPIs into three Power BI dashboards in 18 weeks.
Background
American Glass Services (AGS) lacked a consolidated view of operational performance across its combined Glass and Paint divisions. Reporting depended on fragmented Excel processes, limiting leadership’s visibility into backlog, bid pipeline and labor efficiency.
AGS needed a centralized reporting foundation that could bring its operational data together, standardize KPI logic and give leaders a more reliable view of performance.
Solution implemented
Using a scalable BI platform, the EBM Team integrated data from 6+ sources, including Sage ERP, ADP, SharePoint and Smartsheet, into a consolidated semantic model. When the original model proved insufficient mid-build, the project scope was expanded through a formal change order to create a more durable foundation.
Across the 18-week engagement, the team delivered:
- Three production Power BI dashboards for Executive Summary, Pipeline & Bidding, and Operations reporting.
- A restructured semantic model with a Master Jobs Table and standardized cost-code mapping.
- Validated definitions and calculation logic for 15+ core KPIs.
- Knowledge transfer covering dashboard use, refresh processes, source mappings and model maintenance.
Results
AGS now has one consolidated view of operational KPIs across its Glass and Paint divisions. The three dashboards replaced weekly manual backlog reporting and Excel compilation with a clearer, more consistent reporting experience.
The Executive Summary dashboard brings together total backlog, billings, open purchase orders and labor hours, helping leadership surface cash-flow and profitability risks earlier. Pipeline & Bidding provides the first consolidated bid view across divisions, including total bids, win/loss rates and a conversion funnel based on more than 1,000 historical bids. Operations reporting adds visibility into field hours, remaining hours and project timelines to support workload balancing, scheduling and labor-efficiency decisions.
The restructured semantic model also gives AGS a stronger foundation for future expansion, including other division integrations, historical and year-over-year reporting, forecasting, projected billing and job-level cost analysis.
What made it possible
The project adapted when the original data model could not support the desired future state. A formal scope expansion created room to rebuild the model correctly, while close collaboration with AGS stakeholders helped validate KPI logic and keep the dashboards connected to real operating needs.
The outcome was more than a set of dashboards: it was a scalable reporting foundation designed to support clearer decisions as AGS’s analytics needs grow.
