Oracle OTM Analytics: 12 KPIs and the Data Behind Them
Oracle OTM analytics should answer four questions quickly: What are we spending? Are shipments on time? Are we using capacity well? Where is performance getting worse?
The difficult part is rarely drawing the dashboard. It is agreeing on the calculation behind each number and mapping that definition to the right Oracle Transportation Management data.
This guide defines 12 practical OTM KPIs, the formulas behind them, and the shipment, cost, stop, and location data needed to calculate them in a warehouse or BI tool. To move that source data into your analytics platform, start with the Supaflow Oracle Transportation Management connector.
The 12 Oracle OTM KPIs at a Glance
| KPI | Recommended formula | Primary OTM data |
|---|---|---|
| 1. Shipment volume | Count of distinct shipments | SHIPMENT.SHIPMENT_GID |
| 2. Total freight spend | Sum of actual shipment cost | SHIPMENT.TOTAL_ACTUAL_COST_BASE |
| 3. Cost per shipment | Total freight spend / shipment count | SHIPMENT |
| 4. Cost per distance | Total freight spend / total movement distance | SHIPMENT |
| 5. Planned-to-actual cost variance | (Actual cost - planned cost) / planned cost | SHIPMENT |
| 6. Accessorial cost share | Accessorial cost / total shipment cost | SHIPMENT_COST |
| 7. On-time pickup rate | Pickups within the committed window / completed pickups | SHIPMENT_STOP |
| 8. On-time delivery rate | Deliveries within the committed window / completed deliveries | SHIPMENT_STOP |
| 9. Average stop dwell time | Average of actual departure - actual arrival | SHIPMENT_STOP |
| 10. Weight utilization | Average shipment weight utilization | SHIPMENT |
| 11. Volume utilization | Average shipment volume utilization | SHIPMENT |
| 12. Empty-distance share | Unloaded distance / total movement distance | SHIPMENT |
Oracle's sample Transportation Intelligence dashboard covers many of the same categories: shipment statistics, cost, on-time carrier performance, utilization, and empty distance. Oracle also makes clear that its preconfigured dashboards are examples to customize, not universal definitions. That is the right mindset for the metrics below: use the formulas as explicit starting contracts, then document the business rules your operation chooses.
1. Shipment Volume
Formula: count distinct SHIPMENT_GID after applying the agreed shipment-status and date filters.
Shipment volume is the denominator behind most transportation metrics. Trend it by day, week, or month, then segment it by:
SERVPROV_GIDfor carrierTRANSPORT_MODE_GIDfor modeSOURCE_LOCATION_GIDandDEST_LOCATION_GIDfor laneSHIPMENT_TYPE_GIDfor shipment type
Decide which date places a shipment in a reporting period. START_TIME, END_TIME, and INSERT_DATE answer different questions. For operational throughput, the shipment start or completion date is usually more meaningful than the date the record was created.
Do not count rows in SHIPMENT_STOP or SHIPMENT_COST as shipments. Both are one-to-many children and will inflate the result after a join.
2. Total Freight Spend
Formula: sum TOTAL_ACTUAL_COST_BASE across the in-scope shipments.
Use the base-currency field when the dashboard combines shipments recorded in multiple currencies. If finance requires a different reporting currency, apply a governed exchange-rate model before aggregation instead of summing mixed local amounts.
Break spend down by carrier, mode, lane, origin, destination, and shipment type. A rising total is not automatically a problem; compare it with volume, distance, and weight so growth is separated from rate deterioration.
3. Cost per Shipment
Formula:
sum(TOTAL_ACTUAL_COST_BASE) / count(distinct SHIPMENT_GID)
Cost per shipment is an executive-friendly efficiency metric, but it is sensitive to the shipment mix. A shift from parcel to truckload can increase the average while cost efficiency actually improves.
Always show the metric with at least one segmentation such as mode, lane, carrier, or weight band. For a network-level scorecard, report both the overall weighted value and segment-level values so low-volume lanes do not distort the conclusion.
4. Cost per Distance
Formula:
sum(TOTAL_ACTUAL_COST_BASE)
/
sum(LOADED_DISTANCE_BASE + UNLOADED_DISTANCE_BASE)
This definition measures cost across the full movement. If the business uses “cost per loaded mile,” exclude UNLOADED_DISTANCE_BASE from the denominator and label the metric precisely.
Use the _BASE distance fields when the source contains multiple units of measure. Exclude zero-distance records from the division and report them as a data-quality count; silently treating them as zero-cost movement hides incomplete lane data.
5. Planned-to-Actual Cost Variance
Formula:
(TOTAL_ACTUAL_COST_BASE - PLANNED_COST_BASE)
/
PLANNED_COST_BASE
This KPI shows whether executed transportation is costing more or less than the plan. Track both the absolute variance and the percentage variance:
- Absolute variance shows budget impact.
- Percentage variance makes differently sized shipments comparable.
Exclude or separately classify shipments with no planned cost. Then segment unfavorable variance by carrier, lane, mode, and accessorial category to find the operational cause rather than stopping at the financial symptom.
6. Accessorial Cost Share
Formula:
sum(SHIPMENT_COST.COST_BASE where ACCESSORIAL_CODE_GID is not null)
/
sum(SHIPMENT_COST.COST_BASE)
Join SHIPMENT_COST to SHIPMENT on SHIPMENT_GID. The cost table provides the detail needed to distinguish accessorials from the shipment total and to analyze codes, categories, and adjustment reasons.
Group accessorial spend by ACCESSORIAL_CODE_GID, carrier, facility, and lane. A network-wide percentage can look stable while detention, redelivery, or another specific charge is growing quickly at one location.
Check how your OTM configuration represents credits, reversals, and informational cost rows before finalizing the denominator. The metric contract should state which COST_TYPE and COST_CATEGORY_GID values are included.
7. On-Time Pickup Rate
Formula: completed pickup stops arriving within the committed pickup window / completed pickup stops.
Use SHIPMENT_STOP.STOP_TYPE to identify pickup stops, then compare ACTUAL_ARRIVAL with the agreed commitment field. APPOINTMENT_WINDOW_END, APPOINTMENT_PICKUP, and PLANNED_ARRIVAL can all be reasonable cutoffs, but they do not mean the same thing.
For example:
on time = ACTUAL_ARRIVAL <= APPOINTMENT_WINDOW_END
Define a grace period if the business allows one, and exclude stops with no actual arrival from the completed-stop denominator. Report those missing actuals separately instead of classifying them as late or on time.
8. On-Time Delivery Rate
Formula: completed delivery stops arriving within the committed delivery window / completed delivery stops.
The calculation mirrors on-time pickup, using delivery stops and the delivery commitment. Keep the two KPIs separate: pickup performance often reflects origin readiness and carrier arrival, while delivery performance also includes transit and downstream-stop effects.
For multi-stop shipments, calculate at the stop level first. If an executive dashboard needs a shipment-level metric, explicitly choose whether a shipment is late when any delivery stop is late or only when the final delivery stop is late.
9. Average Stop Dwell Time
Formula:
average(ACTUAL_DEPARTURE - ACTUAL_ARRIVAL)
Calculate dwell only where both timestamps exist and ACTUAL_DEPARTURE is not earlier than ACTUAL_ARRIVAL. Segment it by LOCATION_GID, stop type, carrier, hour of day, and day of week.
The average alone can hide severe delays. Pair it with the median, 90th percentile, and share of stops above your operational threshold. Joining LOCATION adds facility name, city, region, and time-zone context for a facility scorecard.
10. Weight Utilization
Formula: average WEIGHT_UTILIZATION across the selected shipment population.
OTM also exposes TOTAL_WEIGHT_BASE, which lets you validate the utilization distribution and create weight bands. Segment the KPI by equipment, mode, carrier, and lane; combining unlike equipment types into one average can produce an actionless number.
Use a weighted average only if the business can explain the weighting. A simple shipment average and a distance-weighted average answer different questions.
11. Volume Utilization
Formula: average VOLUME_UTILIZATION across the selected shipment population.
Weight and volume utilization should sit next to each other. A load can reach its weight constraint with unused cube, or fill its cube while remaining light. Looking at only one metric can suggest consolidation opportunities that the other physical constraint makes impossible.
Use TOTAL_VOLUME_BASE, TOTAL_WEIGHT_BASE, and equipment attributes to investigate outliers. Report missing or default utilization values as a separate data-quality measure.
12. Empty-Distance Share
Formula:
sum(UNLOADED_DISTANCE_BASE)
/
sum(LOADED_DISTANCE_BASE + UNLOADED_DISTANCE_BASE)
This KPI measures how much recorded movement occurs without a load. It is especially useful for private fleet and dedicated-capacity operations.
Trend it by carrier, region, equipment, and lane. Validate that both loaded and unloaded distance are populated consistently before using the percentage as a target; incomplete unloaded-distance capture can make the network look artificially efficient.
The Minimum OTM Analytics Data Model
Start with a shipment-grain fact table and add child facts without flattening them into duplicate shipment rows.
| Model | Grain | OTM source | Purpose |
|---|---|---|---|
fact_shipment | One row per shipment | SHIPMENT | Volume, spend, cost variance, distance, utilization, carrier, mode, lane |
fact_shipment_cost | One row per cost line | SHIPMENT_COST | Accessorial and cost-category analysis |
fact_shipment_stop | One row per shipment stop | SHIPMENT_STOP | Pickup, delivery, dwell, and facility performance |
bridge_order_shipment | One row per order allocation | ALLOCATION | Order-level shipment and allocated-cost analysis |
dim_location | One row per location | LOCATION | Facility, geography, and time-zone attributes |
dim_shipment_status | One row per shipment and status type | SHIPMENT_STATUS | Current status filters and completion rules |
The join paths are:
SHIPMENT 1 --- many SHIPMENT_COST
SHIPMENT 1 --- many SHIPMENT_STOP
SHIPMENT 1 --- many SHIPMENT_STATUS
SHIPMENT 1 --- many ALLOCATION many --- 1 ORDER_RELEASE
SHIPMENT_STOP many --- 1 LOCATION
Aggregate each child table to shipment grain before joining it to a shipment-level dashboard. If a shipment has four stops and three cost lines, joining both raw children directly creates 12 rows and overstates spend unless the query compensates for the fanout.
A Dashboard Layout People Can Act On
Organize the 12 KPIs into four pages instead of putting every chart on one screen.
Executive overview
- Shipment volume
- Total freight spend
- Cost per shipment
- On-time delivery rate
- Planned-to-actual cost variance
Cost control
- Cost per distance
- Accessorial cost share and top accessorial codes
- Cost variance by carrier, lane, and mode
- Spend and volume trend together
Service and facilities
- On-time pickup and delivery
- Dwell-time median and 90th percentile
- Worst-performing locations
- Carrier scorecard with a minimum shipment threshold
Capacity and network
- Weight utilization
- Volume utilization
- Empty-distance share
- Lane and equipment breakdowns
Every page should show the reporting period, last successful data refresh, applied filters, metric definition, and data-quality exclusions. Those details prevent two teams from presenting different answers under the same KPI name.
Oracle Transportation Intelligence or a Warehouse Dashboard?
Oracle Transportation Intelligence (TI) provides preconfigured dashboards and standard metrics over OTM's historical data. A warehouse dashboard becomes useful when the analysis must combine OTM with ERP, WMS, finance, telematics, claims, fuel, or customer data.
| Use Oracle TI when | Use a warehouse and BI tool when |
|---|---|
| The audience works primarily inside OTM | The audience uses Power BI, Tableau, Looker, or another shared BI layer |
| Standard transportation metrics cover the decision | KPI definitions require cross-system data or custom semantic models |
| OTM roles and dashboard configuration fit governance needs | The team needs enterprise-wide access, retention, or row-level governance |
| The analysis can use OTM's operational and historical reporting data | OTM data must be joined with orders, invoices, inventory, customers, or budgets |
The two approaches can coexist. Use TI for OTM-native operational analysis and the warehouse for cross-functional reporting, while keeping metric definitions aligned.
Data Rules to Set Before Publishing the Dashboard
Write these decisions into the semantic model, not only into a dashboard description:
- Shipment scope: which status values, shipment types, domains, and test records are included?
- Reporting date: start, end, pickup, delivery, invoice, or record-creation date?
- Currency: local transaction currency, OTM base currency, or a finance reporting currency?
- Distance and weight units: source units or normalized base units?
- On-time commitment: planned arrival, appointment timestamp, or appointment-window end?
- Grace period: is a shipment on time at the exact cutoff, or within a tolerance?
- Multi-stop logic: first pickup, last delivery, every stop, or worst stop?
- Missing actuals: excluded, incomplete, or late?
- Cancelled shipments: removed from all metrics or retained in a separate operational view?
- Refresh SLA: how old can the data be before users should stop trusting the dashboard?
These rules are more important than the chart type. A trusted KPI is a versioned business definition with a named owner, known grain, formula, exclusions, and source lineage.
Getting OTM Data into Your Warehouse
Supaflow's Oracle Transportation Management source currently covers 18 operational tables, including shipments, shipment costs, shipment stops, status history, order releases, allocations, and locations. That is enough to build the 12 KPIs in this guide and connect them to destinations such as Snowflake, Databricks, BigQuery, Redshift, or Postgres.
For extraction setup, authentication, export modes, and incremental-sync behavior, follow the Oracle Transportation Management integration guide and the OTM source documentation. The table references document the exact fields for shipments, shipment costs, shipment stops, locations, and allocations.
Start with the five core tables needed by the first dashboard release. Add order, status, reference, and audit tables when a named business question requires them. A smaller governed model is easier to validate than a large replica with no agreed metric layer.
Frequently Asked Questions
What are the most important Oracle OTM KPIs?
Start with shipment volume, total freight spend, cost per shipment, on-time pickup, on-time delivery, planned-to-actual cost variance, accessorial cost share, dwell time, weight utilization, volume utilization, and empty-distance share. The right priority depends on whether the team owns cost, service, facilities, or fleet capacity.
Does Oracle OTM include analytics dashboards?
Yes. Oracle Transportation Intelligence provides preconfigured, customizable dashboards with standard shipment, financial, carrier, utilization, cost-benefit, and environmental metrics. Access and configuration depend on the organization's OTM analytics setup and user roles.
Which OTM tables are needed for transportation analytics?
Use SHIPMENT as the core shipment fact, SHIPMENT_COST for cost lines and accessorials, SHIPMENT_STOP for pickup, delivery, and dwell analysis, LOCATION for facility and geography attributes, and SHIPMENT_STATUS for status rules. Add ALLOCATION and ORDER_RELEASE when the analysis needs an order-to-shipment view.
How do you calculate on-time delivery in OTM data?
Identify delivery stops in SHIPMENT_STOP, then compare ACTUAL_ARRIVAL with the organization's committed delivery cutoff, such as APPOINTMENT_WINDOW_END or PLANNED_ARRIVAL. Define the grace period, multi-stop rule, and treatment of missing actual timestamps before publishing the rate.
How often should OTM analytics refresh?
Match refresh frequency to the decision. Executive cost dashboards may refresh daily, while dispatch, exception, or facility dashboards may need intraday updates. Publish the last successful refresh time and alert when the agreed freshness target is missed.
Build the First OTM Scorecard
Choose one dashboard audience, assign an owner to each metric, and validate the first month against OTM and finance before expanding the model.
Book a Supaflow demo to review your OTM analytics use case, or start a free trial and connect the shipment, cost, stop, status, and location data needed for your first scorecard.
Ask Claude