Fleetline

Freight brokers manage load data across a TMS, carrier emails, shipper portals, and a shared spreadsheet that's always two days behind. Margin visibility requires manual calculation. Carrier performance is institutional memory. Exceptions surface through phone calls, not dashboards.

A dimensional load intelligence stack — Fact_Load as the single source of truth for every load from book to deliver. Carrier and lane dimensions enable slice-and-dice across any combination. Exception rows are FK-linked to loads, not tracked in a separate tracker. Every margin calculation is in the schema, not a spreadsheet.

Operational dashboard with live load table, 12-month revenue trend, lane performance heatmap, carrier scorecard, open exception log, and a star schema architecture built on the same DDL dimensional methodology used across every other analytics engine in the portfolio.

CREATE TABLE Fact_Load (
  load_id  INT  PRIMARY KEY
  carrier_id  INT  REFERENCES Dim_Carrier
  lane_id  INT  REFERENCES Dim_Lane
  shipper_id  INT  REFERENCES Dim_Shipper
  period_id  INT  REFERENCES Dim_Period
  mode  VARCHAR(3)   -- FTL | LTL | PTL
  status  VARCHAR(20)   -- BOOKED → PICKED_UP → IN_TRANSIT → DELIVERED
  quoted_rate  DECIMAL(10,2)  
  carrier_pay  DECIMAL(10,2)  
  margin_pct  DECIMAL(5,2)   -- (quoted - carrier_pay) / quoted
  on_time_flag  BOOLEAN  
  exception_id  INT  REFERENCES Fact_Exception NULLABLE
-- Margin is a stored computed field — calculated once at DELIVERED status
-- Exception rows FK to Fact_Load — no separate tracker sheet
-- Carrier score is a DIM, not a fact — refreshed nightly from Fact_Load aggregation