About the company.
ECM Energy Services runs a fleet-heavy field services business across multiple US regions, dispatching crews, trucks and equipment to industrial and oilfield customers every day.
Five SaaS platforms each owned a slice of the operation: NetSuite for finance, AR and revenue recognition; Fleetio for fleet maintenance, asset health and parts inventory; Samsara for telematics, hours-of-service and driver safety events; KPA Flex for EHS, incident logs and safety-observation compliance; and ADP for payroll, headcount and turnover. Everything else lived in Excel and SharePoint: daily job counts, forward projections, the recruiting pipeline.
The problem.
The business could not see any of these together. Finance closed the books weeks after the shifts that drove the numbers. Dispatch scheduled crews without visibility into equipment availability or technician utilization. Safety sat on TRIR in a quarterly PDF.
The CFO asked the same question three ways and got three different answers, depending on which system was pulled.
The objective.
Build a single source of truth on Microsoft Azure so Finance, Operations, Safety and HR all read off the same certified data, with twice-daily refresh, role-based access from drivers and field technicians through to the C-suite, and Power BI on top. Decision-making had to shift from “which spreadsheet do we trust” to “what does the dashboard say”.
The approach.
Algoscale stood up the warehouse on Microsoft Azure using the S.C.A.L.E.™ accelerator pattern, deployed directly into the client’s own Azure subscription.
Microsoft Purview holds the glossary, the lineage and the sensitivity labels. Azure Key Vault and Entra-backed RBAC scope access by role.
Five SaaS APIs plus Excel and SharePoint, through a medallion lakehouse, out to one certified semantic model.
Azure Data Lake Gen2 on a medallion architecture. Bronze raw, Silver conformed, Gold marts, with watermarked incremental extraction on every source.
Azure SQL Database as the curated model. The dimensional warehouse Power BI reads through DirectQuery, so the dashboard and the warehouse cannot drift apart.
Azure Data Factory orchestrating ingestion. Twice-daily pipelines across five APIs plus the Excel and SharePoint manual feeds.
Microsoft Purview for governance. Business glossary, auto-generated lineage, and sensitivity labels on PII and finance data.
Azure Key Vault and Entra-backed RBAC. Credential management and role-scoped access, mapped to the company org hierarchy.
Fabric-ready by design. Bronze on ADLS Gen2 exposes as a OneLake shortcut with no rework, so a future move to Microsoft Fabric is a configuration change rather than a rebuild.
The dashboards.
Eight Power BI modules, delivered in two batches across weeks 7 to 12, covering roughly 60 KPIs between them.
- Financials — P&L by business unit, revenue trending, margin by service line, variance against budget.
- Billing — AR aging, invoice cycle time, collection trends, short-pay rate.
- Trucking — fleet utilization, fuel cost per mile, empty miles, maintenance burn rate.
- Traffic — dispatch board, dwell time, route profitability, driver hours-of-service status.
- Maintenance — asset health, MTTR and MTBF, parts inventory at truck, downtime cost.
- Safety — TRIR, LTIR, DART, near-miss count, observation compliance, training freshness.
- Recruiting and HR — requisition pipeline, time-to-fill, turnover by region, overtime burn, headcount roll-forward.
- Projections — daily job counts and an eighteen-month forward revenue projection by service line.
Access that matches the org chart.
Access is scoped to the persona, not to the IT backlog. Implemented with Power BI row-level security, Purview sensitivity labels, and Entra group membership mapped to the company hierarchy.
Drivers and field technicians. Their own jobs, assigned assets, HOS status and parts inventory at the truck. Mobile-first. No cost or margin data.
Dispatchers and dock supervisors. Today’s active loads, crew assignments and the maintenance queue. Site-scoped.
Finance analysts. P&L, billing and collections. No employee PII.
Safety officers. TRIR rollups and incident trends across sites. Function-scoped.
Terminal and regional managers. Terminal-level P&L, driver turnover and customer scorecards. Region-scoped.
Executives. Network-wide dashboards, dollar-denominated, with safety and service-revenue rollups. No raw signals.
Delivery in fourteen weeks.
Three phases, eight sprints, agile throughout.
- Weeks 1–4 · Foundation — Azure setup, Key Vault, network security, API credentialing, ADF pipelines, Bronze populated.
- Weeks 5–6 · Modeling — warehouse schema, SQL transformation logic, KPI definitions, Silver and Gold marts live.
- Weeks 7–12 · Visualization — eight Power BI modules in two batches.
- Week 13 · Deployment — UAT, row-level security, performance optimization.
- Week 14 · Handover — training, final documentation, go-live and a 30-day hypercare window.
The outcome.
One source of truth across five SaaS platforms. Plus Excel and SharePoint. Finance, operations, safety and HR now read the same numbers with the same as-of timestamp.
Sixty-plus certified KPIs on one semantic model. One definition of TRIR, fleet utilization, revenue per service line and OTIF.
Twice-daily automated refresh. Across every source system, replacing manual aggregation that had been running on spreadsheets.
Row-level security for every persona. From driver to executive, enforced at the data layer rather than in the report.
Fourteen weeks from signed SOW to go-live. Three phases, eight sprints, with a 30-day hypercare window after handover.
A Fabric on-ramp baked in. Bronze on ADLS Gen2 exposes as a OneLake shortcut, so the move to Microsoft Fabric is a configuration change rather than a migration project.
