Logistics
Service and cost-to-serve on one shipment spine
Ops and finance review the same shipment — on-time service next to cost-to-serve — in the morning call.
- Operating since 2012
- Microsoft Power BI specialists
- NDA-ready data handling
- Fixed-scope first releases
- Independent — not affiliated with Microsoft
Table of contents
01 · Context
The situation
Mid-size 3PL · Logistics
A mid-size third-party logistics operator ran a TMS for moves, a WMS for warehouse events, and ERP/finance for shipment cost. Customer exceptions lived in a service desk.
Before: ops ran an on-time extract from the TMS. Finance rebuilt cost-to-serve from invoices at week-end. The two files never shared a stable ShipmentId. Detention sat in a yard spreadsheet.
They needed one shipment spine: on-time delivery, carrier performance, cost-to-serve, and named exception owners — ready for the daily stand-up, not a Friday pack.
02 · Solution
What we built
A logistics control tower in Power BI: service exceptions, carrier scorecards, and cost-to-serve on one shipment key — the same grain a power bi logistics dashboard needs.
Shipment spine
TMS, WMS, and finance resolve to one shipment ID before any KPI is calculated.
Service exception queue
Late, damaged, and missed appointments with owners — the morning ops call’s action list.
Cost-to-serve bridge
Finance cost sits on the same shipment so commercial impact is not a separate Friday file. Zeros are not silent: pending cost is explicit.
Ops control
- On-time delivery
- Exception queue
- Carrier performance
Commercial + finance
- Cost-to-serve
- Customer impact
- Lane exceptions
Data sources
- TMS status and appointment events
- WMS ship-confirm and dock events
- Customer-service exceptions
- Finance shipment cost (accrual and invoice)
Connectors
- SQL Server staging
- ETL pipelines
- On-premises Data Gateway (standard)
Model design
Shipment grain with lifecycle timestamps; cost facts after reference cleanup; Import for daily ops. Staging prefers the latest valid event per milestone so corrections do not double-count. Pending-cost state when finance has not posted.
03 · Process
How we delivered
From the real meeting to a live report — with checkpoints, not a big reveal at the end.
- 01
Challenge
Mapped the shipment lifecycle with ops and finance in the same room. Listed every ID (PRO, stop, invoice) that currently failed to join.
- 02
Approach
Staging rules for late and corrected TMS events. Import model on a standard gateway. Dual audience: ops pages first, finance measures on the same spine. RLS roles for shipper vs internal control tower, tested with View as.
- 03
Result (operating change)
The morning call runs from the exception queue. Finance joins the same shipment record instead of bringing a second workbook. Numeric % improvement is not published here until the client confirms it.
- 04
Tools and method
Power BI Import, SQL staging, gateway cluster, OTIF calculated from timestamps and a signed window — not the TMS on-time flag.
04 · Technologies
Stack and why we chose it
Tools that serve the report — not a shopping list of buzzwords.
Power BI
Ops and finance views on one shipment model.
Why: Same meeting, different questions — one underlying truth.
SQL + ETL
Staging for TMS/WMS/finance before Import.
Why: Late corrections and ID mismatches need rules, not hope.
On-premises Data Gateway
Scheduled refresh of on-prem SQL staging.
Why: A dispatcher laptop is not an ops dependency.
05 · Challenges
What got hard — and how we fixed it
Real delivery friction, not a polished success-only story.
Challenge
Corrected TMS events double-counted shipments in early prototypes.
Resolution
Staging rules prefer the latest valid event per shipment milestone; tests cover late arrivals.
Challenge
Finance cost landed days after the move; ops wanted same-day service views.
Resolution
Service KPIs refresh daily; cost measures show “pending cost” until finance posts — never silent zeros.
06 · Results
What changed
- Ops and finance review the same shipment record in the morning call.
- Exception queues drive the stand-up instead of an emailed TMS extract.
- Cost-to-serve is visible beside service — pending cost is labeled, not hidden as zero.
- Shipper vs control-tower access is a tested RLS role, not an app-audience hope.
Continue exploring this topic
Related pages that deepen the same subject—services, industries, integrations, and proof.
Why teams trust Power BI Analytics
Enterprise delivery with clear ownership
Power BI Analytics combines Power BI craft with business process discipline—so leaders get numbers they can act on.
14
Years building software & automation
What to expect in an engagement
- Named business sponsor and delivery ownership
- KPI definitions validated against source totals
- Security-aware workspace and refresh design
- Documented handover, not a black-box model
- First release scoped to prove value quickly
- Response within one business day on qualified enquiries
Have a similar reporting mess?
Book a free scoping call — we will tell you honestly whether Power BI is the right fix.