Key Takeaways
- Consolidated four disconnected systems, ERP, payroll, GPS telematics the 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 the billable hours comparable in a single query for the first time in the company's history.
- Replaced hthe-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 the specialized floor jacks, all made in the USA. Its customers run military, municipal the transit fleets.
Challenge
A company that both manufactures equipment the sends technicians into the field runs on three separate clocks, the payroll clock, the drive clock the the billable clock, the none of them could be compared. Each system delivered data its own way, one of them only as spreadsheet files, the every business question meant somebody assembling exports in Excel.
Solution
- A single BigQuery project with a dataset per source system the a reporting layer of views on top.
- Automated daily extraction from Sage 100 over ODBC, with type htheling the encoding hardened for real-world exports.
- Payroll, telematics the CRM feeds normalised, de-duplicated the 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 the 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%.