Azure Data Warehouse for a Field Services & Fleet Operator

The problem

The client 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
• ADP for payroll, headcount, and turnover data

Everything else lived in Excel and SharePoint - daily job counts, forward projections, recruiting pipeline.

The business couldn’t 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 for 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 our S.C.A.L.E.™ accelerator pattern, deployed directly into the client’s Azure subscription:

• Azure Data Lake Gen2 with a medallion architecture - Bronze raw, Silver conformed, Gold marts
• Azure SQL Database as the curated dimensional model that Power BI reads
• Azure Data Factory orchestrating twice-daily ingestion from five APIs plus Excel and SharePoint manual feeds
• Microsoft Purview for the business glossary, auto-generated lineage, and sensitivity labels on PII and finance data
• Azure Key Vault + Entra-backed RBAC for credential management and role-scoped access
• Power BI as the single consumption layer - eight modules, ~60 KPIs, built against one certified semantic model
• Fabric-ready by design - ADLS Gen2 Bronze exposes as a OneLake shortcut with no rework, so the roadmap to Microsoft Fabric (Fabric Warehouse, Direct Lake on Power BI, Fabric Copilot on certified models) lands as a configuration change, not a rebuild. We cover the mechanics in Fabric OneLake shortcuts vs ADLS mounts.

The architecture

Five SaaS APIs and two manual file sources flow into ADF pipelines on a twice-daily schedule. Each source lands in the Bronze layer with watermarked incremental extraction. Silver-layer transformations conform master data - customer, job, asset, employee - into shared dimensions. Gold marts are built per function (finance, operations, safety, HR) against the same conformed model, so fleet utilization, TRIR, revenue-per-job, and OTIF mean the same thing on every dashboard.

Power BI connects to Gold via DirectQuery against Azure SQL, so numbers on the dashboard match numbers in the warehouse without an intermediate caching layer getting out of sync.

The dashboards

Eight Power BI modules, delivered in two batches across weeks 7-12, cover ~60 KPIs between them:

1. Financials - P&L by business unit, revenue trending, margin by service line, variance against budget
2. Billing - AR aging, invoice cycle time, collection trends, short-pay rate
3. Trucking - fleet utilization, fuel cost per mile, empty miles, maintenance burn rate
4. Traffic - dispatch board, dwell time, route profitability, driver hours-of-service status
5. Maintenance - asset health, MTTR / MTBF, parts inventory at truck, downtime cost
6. Safety - TRIR, LTIR, DART, near-miss count, observation compliance, training freshness
7. Recruiting & HR - requisition pipeline, time-to-fill, turnover by region, overtime burn, headcount roll-forward
8. Projections - daily job counts, forward-18-month revenue projection by service line

RBAC that matches the org chart

Access is scoped to the persona, not the IT backlog. Implemented via Power BI row-level security, Purview sensitivity labels, and Entra group membership mapped to the company’s org hierarchy.

• Drivers & Field Technicians see their own jobs, assigned assets, HOS status, and parts inventory at the truck. Mobile-first. No cost or margin data.
• Dispatchers & Dock Supervisors see today’s active loads, crew assignments, and maintenance queue. Site-scoped.
• Finance Analysts see P&L, billing, and collections. No employee PII.
• Safety Officers see TRIR rollups and incident trends across sites. Function-scoped.
• Terminal & Regional Managers see terminal-level P&L, driver turnover, customer scorecards. Region-scoped.
• Executives see network-wide dashboards, dollar-denominated, with safety and service-revenue rollups. No raw signals.

The delivery cadence

14 weeks, three phases, eight sprints, agile:

• Weeks 1-4 · Foundation - Azure setup, Key Vault, network security, API credentialing, ADF pipelines, Data Lake Bronze populated
• Weeks 5-6 · Modeling - warehouse schema, SQL transformation logic, KPI definitions, Silver/Gold marts live
• Weeks 7-12 · Visualization - 8 Power BI modules in two batches (financial/billing/trucking/traffic first; maintenance/safety/recruiting/projections second)
• Week 13 · Deployment - UAT, row-level security, performance optimization
• Week 14 · Handover - training, final documentation, go-live, 30-day hypercare

The stack underneath

Source systems: NetSuite (finance), Fleetio (fleet maintenance), Samsara (telematics + HOS), KPA Flex (EHS), ADP (payroll + HR), plus Excel and SharePoint manual feeds.

Azure landing zone: Azure Data Lake Gen2 (Bronze / Silver / Gold medallion), Azure SQL Database (curated dimensional warehouse), Azure Data Factory (orchestration), Microsoft Purview (governance + lineage), Azure Key Vault (credential management).

Consumption layer: Power BI (8 modules, ~60 KPIs, DirectQuery).

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
• 60+ certified KPIs on a single Power BI semantic model - one definition of TRIR, fleet utilization, revenue per service line, 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
• Delivery completed in 14 weeks, three phases, eight sprints - from signed SOW to production go-live
• Fabric on-ramp baked in: Bronze on ADLS Gen2 exposes as a OneLake shortcut, so the client’s future move to Microsoft Fabric (Direct Lake, Fabric Copilot, Fabric Warehouse) is a configuration change rather than a migration project

Related work

This engagement slots directly into Algoscale’s Microsoft Fabric for Logistics & Transportation practice - the same TMS / WMS / ELD / FSM unification pattern applies whether the organization runs on Fabric + OneLake or on an Azure SQL-first estate like this one, and the Bronze layer on ADLS Gen2 makes the two interoperable via OneLake shortcuts. For a broader view of the accelerator that delivered the infrastructure, see S.C.A.L.E.™; for our honest take on Fabric versus the alternatives, see Microsoft Fabric vs Databricks, Honestly. For the integration-heavy side of a multi-SaaS rollout, see our data integration consulting practice.