Carrier Data Warehouse KPI Semantic Layer: One Definition Per KPI for Operating Ratio, Revenue Per Truck, and Empty Miles, With Conformed Dimensions

Why Three Departments Report Three Numbers

The disagreement is structural. Dispatch computes revenue per truck from the TMS at the moment a load is delivered, using the line haul rate and excluding fuel surcharge because dispatchers think of surcharge as a pass through. Finance computes the same metric from invoiced revenue in the accounting system, including fuel surcharge and accessorials, recognized when the invoice posts, which may be days later and in a different week. Safety counts tractors from the equipment master including units in the shop, while dispatch counts only seated tractors. Each department's number is correct by its own definition, and none of the definitions is written down anywhere a system can enforce.

The cost is not just arguments. Decisions get made on the wrong number. A terminal manager who believes empty miles are 12 percent because the TMS report excludes bobtail miles will not chase the 4 points hiding in bobtail. A sales manager who sees revenue per loaded mile with fuel surcharge included will defend a lane that is losing money on line haul. A board that sees an operating ratio built without accruals will be surprised at year end. The remedy is a semantic layer: a place where each KPI is defined once, in terms of warehouse tables and columns, and from which every report draws.

The Semantic Layer Concept

A semantic layer is a governed catalog of metrics and dimensions expressed against the warehouse schema. For each metric it records the name, the business definition in plain language, the numerator and denominator as expressions over fact tables, the grain at which the metric is natively computed, the allowed dimensions for slicing, the filters that are always applied, the source systems feeding the facts, and the owner responsible for changes. For each dimension it records the conformed table, the business key, the attributes, and the change handling rules. Reporting tools, whether a BI platform, a spreadsheet export, or a scheduled PDF, query the semantic layer or the views it generates rather than the raw tables.

Implementation can be a metrics layer product, a set of governed SQL views, a BI tool's shared data model, or a combination. The tooling matters less than the discipline that the definition exists in exactly one place and that nobody computes operating ratio in a spreadsheet formula. In practice the most durable implementation for mid sized carriers is a schema of SQL views named for the metric and grain, such as kpi_operating_ratio_by_month_terminal, generated from a definition table so the view text and the documentation never drift apart. Our SQL data mining practice typically builds that definition table first and the views from it.

Precise Definitions for the Core Metrics

Operating ratio is total operating expense divided by total operating revenue, expressed as a percentage. The disagreements hide in both terms. Does revenue include fuel surcharge? For a ledger based operating ratio it must, because the ledger records the surcharge as revenue and the fuel cost as expense. Does expense include depreciation, interest, and gain or loss on equipment sales? Operating ratio by common usage includes depreciation and excludes interest and non operating items, but each carrier must state its treatment. Does the period use accrued or paid expense? Accrued. The semantic layer definition should be explicit on every one of these points and should name the general ledger account ranges that make up each term.

Revenue per truck per week is total operating revenue for the week divided by the average number of tractors in the fleet during the week. The denominator is where dispatch and finance diverge. The definition must state whether the count is all tractors on the equipment master, tractors in active status, or seated tractors with an assigned driver. Each is a legitimate metric with a different name: revenue per owned tractor, revenue per active tractor, revenue per seated tractor. The semantic layer should publish all three if the business uses all three, each with its own name, rather than letting one name carry three meanings.

Revenue per loaded mile is line haul revenue divided by loaded miles. Whether fuel surcharge and accessorials are in the numerator is the single most common cause of disagreement in carrier reporting, and the right answer depends on the question being asked. For lane pricing decisions, exclude fuel surcharge and accessorials so the metric measures what the rate negotiation controls. For total yield, include them. Publish both as revenue per loaded mile, line haul only, and total revenue per loaded mile, and never let a report label say simply revenue per mile.

Empty mile percent is empty miles divided by total miles. The definition must say whether empty miles include bobtail, miles to and from the shop, and miles to and from the driver's home, and whether total miles come from the mileage system's planned route, the ELD odometer, or the TMS movement records. Deadhead is usually the subset of empty miles between a delivery and the next pickup, which excludes shop and home miles. Utilization is loaded miles or revenue hours divided by available miles or hours, and available must be defined, typically from the hours of service clock or a standard weekly capacity per tractor. Detention capture rate is detention revenue billed divided by detention events eligible under customer contracts, which requires the eligible event to be identified from ELD or telematics dwell time against each customer's free time, not from what was billed.

Days sales outstanding, or DSO, is accounts receivable at period end divided by average daily credit revenue over a stated trailing period, commonly 90 days. The disagreement is usually the trailing window and whether unapplied cash is netted. Driver turnover is drivers who left during the period divided by average driver headcount, annualized. The definition must state whether it includes drivers who left during a training or probationary window, whether it counts owner operators and company drivers together, and how average headcount is computed. Two carriers quoting turnover with different probation rules are not comparable, and neither are two departments inside one carrier.

Conformed Dimensions

A conformed dimension is a single dimension table shared by every fact table in the warehouse. The date dimension holds one row per calendar day with fiscal period, week ending date, holiday flags, and the carrier's operating week definition, so that a dispatch week and a payroll week and a billing week can all be related. The customer dimension holds one row per customer with a surrogate key, the business key from the TMS, the accounting system's customer identifier, the parent account grouping, sales representative, and segment. The lane dimension holds origin and destination at whatever geographic grain the carrier prices, such as three digit postal code pairs or market pairs. The driver dimension holds driver identity, terminal, fleet, pay plan, and status. The tractor dimension holds unit, model year, ownership type, and assigned terminal. The terminal dimension holds the operating locations and their roll up to regions.

Conformance means the dispatch facts, the billing facts, the payroll facts, the fuel facts, and the safety facts all join to the same customer row by the same surrogate key. That requires a master data step that resolves the different identifiers each source system uses for the same customer or driver and records the mapping. Without it the warehouse has three customer tables and the revenue and incident report for one customer silently drops the incidents filed under a slightly different name. The conformance step is unglamorous and is the single largest determinant of whether cross functional KPIs are trustworthy.

Slowly Changing Dimensions for Drivers and Customers

Drivers move between terminals, switch from company driver to owner operator, and change pay plans. Customers change names, merge, move to a new sales representative, and change credit terms. If the dimension row is overwritten each time, every historical fact attached to that driver or customer is reattributed to the new attributes. Last year's terminal revenue report changes when a driver transfers this year. The fix is type 2 slowly changing dimension handling: when a tracked attribute changes, close the current row with an end date and insert a new row with a new surrogate key and a start date. Facts loaded during each period carry the surrogate key in effect at that time.

The design decision is which attributes trigger a new row. Terminal, fleet, and pay plan for drivers; sales representative, segment, and parent account for customers. Attributes that are corrections rather than changes, such as a misspelled name, should overwrite in place as type 1. The semantic layer definitions must then be explicit about whether a KPI uses the historical attribute or the current one. Revenue per terminal uses the terminal at the time of the load. A current roster of drivers by terminal uses the current row. Both are valid, both must be named differently, and the view that implements each must join to the dimension accordingly.

Grain Decisions

Grain is the level of detail at which a fact table records events, and every KPI has a native grain below which it cannot be computed. Revenue facts are naturally at the invoice line, which rolls up to load, customer, and period. Movement facts are at the stop to stop leg, which carries loaded and empty miles and rolls up to load, tractor, driver, and week. Fuel facts are at the purchase transaction. Payroll facts are at the driver pay period line. Ledger facts are at the journal line by account and period. Operating ratio can be computed at month and company from ledger facts, and at month and terminal only if the ledger carries a terminal segment on every revenue and expense line, which many do not. The semantic layer should state the lowest grain at which each KPI is valid and refuse to compute it below that, instead of allocating ledger expense to tractors with a formula and presenting the result as measured.

Allocation is sometimes necessary, for example to produce a margin per tractor when insurance and overhead are booked at the company level. When it is used, the semantic layer should name the allocated metric distinctly, record the allocation basis, and keep the unallocated metric available. A report reader should never have to guess whether a cost per tractor is measured or allocated. Grain discipline also drives the fact table design for detention capture rate, which needs a stop level fact combining telematics arrival and departure, the customer's free time from the contract dimension, and any detention billed on the invoice, because none of the three source systems holds all three.

Governance and Change Control

Definitions change for good reasons: a new customer segment, a new fuel surcharge treatment, a decision to exclude trainees from turnover. Without governance the change happens in one report and not others, and the three number problem returns. A workable governance process has a metric owner for each KPI, usually the executive accountable for the result, a data steward who maintains the definition table and views, and a change request that records the proposed definition, the business reason, the effect on historical values, and the approval. Changes take effect at a stated period boundary, prior periods are either restated or marked as computed under the old definition, and the definition table carries version and effective dates so any historical report can be reproduced.

Publish the definitions where the readers of the reports can see them. A metric catalog page per KPI, generated from the definition table and linked from each dashboard, removes most of the hallway debate. When an executive asks why the number changed, the answer is the catalog's version history rather than an analyst's memory. This governance work is a core part of what our fractional freight CIO engagements establish for carriers that do not yet have a data function.

Validation Against the General Ledger

The ledger is the arbiter for revenue and expense. Each period the semantic layer should produce total operating revenue and total operating expense from its own fact tables and compare them to the sum of the corresponding ledger accounts. Differences must be explained by a reconciliation list: revenue recognized in the warehouse at delivery but invoiced in the following period, ledger entries in non operating accounts excluded by definition, intercompany entries, and manual journal adjustments. An agreed tolerance, stated as a percentage of revenue, determines whether the KPIs for the period can be published as final or must be marked preliminary.

Non financial KPIs need their own controls. Total miles from the movement facts should be compared to ELD odometer totals by tractor by week, with the difference tracked as a data quality metric. Tractor counts should be reconciled to the equipment master and to the insurance schedule. Driver headcount should be reconciled to payroll. Each reconciliation is a stored query with a threshold and an exception report, run as part of the period close rather than when someone notices a problem. Reconciliation results belong in the data quality section of the metric catalog so that a reader can see not only the number but how well its inputs tied out.

Tooling Neutral Guidance

The architecture described here does not depend on a specific warehouse, metrics product, or BI tool. It requires a relational store with fact and dimension tables, a definition table that is the source of truth for metric logic, generated views or a metrics layer that implements the definitions, a master data process for conformance, type 2 handling for the dimensions that need it, and a scheduled reconciliation step. Carriers already running a commercial BI platform can implement the definitions in that platform's shared model, as long as the model is the only place the definitions live and spreadsheet users consume exports from it. Carriers with a small team and a SQL database can implement the whole layer as views. The failure mode to avoid is the same in both cases: a definition that exists in two places.

Sequence the work so that value arrives early. Start with the conformed date, customer, and tractor dimensions and the two or three KPIs that cause the most argument, usually operating ratio, revenue per truck, and empty mile percent. Publish their definitions, generate their views, reconcile them for one period, and replace the competing reports. Then add driver and lane dimensions, type 2 handling, and the next tier of metrics. A carrier that tries to define forty KPIs before publishing one will still be defining when the next budget cycle starts. Configuring the TMS to capture the fields these definitions need, such as stop level arrival and departure times and empty move reasons, is often a prerequisite and is handled in our TMS implementation work.

Related practice areas for this work include SQL Data Mining Fractional Freight CIO TMS Implementation Business Process Optimization. Each practice page describes scope, deliverables, and how an engagement starts with a short discovery call.

How Strong Tower Consulting Helps

Strong Tower Consulting helps carriers build the semantic layer, conformed dimensions, and reconciliation controls that turn competing departmental reports into one set of governed numbers. The work starts with the handful of KPIs that cause the most disagreement, documents the definition each department has been using, negotiates a single published definition with a named owner, and implements it as governed views reconciled to the ledger. Strong Tower Consulting, LLC and Exspeedite LLC are separately organized sister companies under common ownership. That separation keeps consulting advice vendor neutral and focused on the carrier operation.

To discuss the reports your departments disagree about, the systems that feed them, and which KPI to define first, call 763-244-1373 and ask for Scott Elliott. A short call is enough to identify where the definitions currently live and what the first reconciled metric should be.

Frequently Asked Questions