Key Takeaways
- Consolidated four disconnected systems, ERP, payroll, GPS telematics and CRM, into one BigQuery warehouse refreshed daily.
- Built unattended extraction from a legacy ERP with no API, running reliably on the client's own infrastructure.
- Made payroll hours, drive time and billable hours comparable in a single query for the first time in the company's history.
- Replaced hand-reconciled spreadsheet exports with SQL that anyone on the leadership team can run or request.
- Reduced infrastructure cost along the way through a storage audit of the production host.
Organization
ARI-HETRA has spent more than 35 years building mobile column lifts, heavy-duty platform lifts, wheel service equipment, brake lathes, exhaust extraction systems and specialized floor jacks, all made in the USA. Its customers run military, municipal and transit fleets.
Challenge
A company that both manufactures equipment and sends technicians into the field runs on three separate clocks, the payroll clock, the drive clock and the billable clock, and none of them could be compared. Each system delivered data its own way, one of them only as spreadsheet files, and every business question meant somebody assembling exports in Excel.
Solution
- A single BigQuery project with a dataset per source system and a reporting layer of views on top.
- Automated daily extraction from Sage 100 over ODBC, with type handling and encoding hardened for real-world exports.
- Payroll, telematics and CRM feeds normalised, de-duplicated and loaded on schedule.
- Time-series GPS data verified across consecutive-day runs for reliability.
- A storage audit of the production host that reclaimed roughly 10 GB and closed a runaway cloud storage charge.
Results
- Four source systems consolidated, with 19 ERP tables synced daily.
- GPS telematics syncing at 100% reliability.
- Roughly 15 hours a month of manual reporting effort removed.
- Technician utilization visibility improved roughly 25%.