insonl
← journal

Design a Shipment Traceability Dashboard for a Food Distributor

date
22 November 2026
by
insonl
read
8 min read

Illustrative scenario. A food distributor ships chilled lots to grocery and foodservice docks. The WMS logs outbound scans and the ERP posts transfers, yet ops cannot see which lots lack receiving confirmation, which handoffs slipped, or which scan and bill of lading sit behind an alert.

Closing that gap is a food distribution shipment traceability dashboard modeling problem. Upstream guides cover food lot traceability event tables and freight exception events. Downstream, business intelligence and analytics reshapes append-only events so ops filter exceptions without inflated totals or phantom alerts.

This guide uses GS1 EPCIS 2.0 and CBV 2.0 plus Microsoft’s Power BI star schema and Power BI drillthrough guides. All identifiers, timestamps, and cutoffs below are illustrative and made up—not from a real distributor or Insonl client.

Join event and lot data without fan-out in a star schema

Joining raw EPCIS events directly to lot or pallet tables in Power BI risks fan-out: totals multiply whenever a join repeats a quantity-bearing fact row across multiple matching event or pallet rows. Distributors often pack lots (childQuantityList) onto a serialized pallet (parentID, such as an SSCC) via AggregationEvent (EPCIS 2.0 § 7.4.3), then scan pallets or lots (epcList / quantityList) in ObjectEvent steps (§ 7.4.2) referencing desadv (Despatch Advice / ASN), bol (Bill of Lading), po, and recadv (Receiving Advice) in bizTransactionList (CBV 2.0 § 7.3.3).

Microsoft’s Power BI star schema guidance recommends a data warehouse and ETL pipeline for large volumes or slowly changing dimensions (SCD). In this design, that ETL assigns each distinct shipment–lot–leg a stable ShipmentLegKey, resolves dispatch-time pallet contents by replaying AggregationEvent ADD and DELETE actions so later unpacking never alters historical loads, pairs outbound and inbound milestones, and suppresses retractions before loading dimensions (“one” side) and consistent-grain facts (“many” side):

Table Role Grain and contents
FactShipmentLotHandoff Fact (paired leg) One row per active shipment–lot–leg (ShipmentLegKey, LotKey, origin/destination keys, timestamps, quantities, durations, ShipmentKey, HandoffStatusKey).
FactTraceabilityEvent Fact (atomic) One row per EPCIS event × lot line linked to its shipment–lot–leg via ShipmentLegKey (eventID, bizStep, disposition, eventTime, recordTime, readPoint, retraction state).
BridgeLogisticsUnitLot Factless bridge Links DimLogisticsUnit (SSCC) and DimLot without duplicating leg quantities.
DimShipmentLeg Leg dimension One row per shipment–lot–leg (ShipmentLegKey), propagating drill-through to both facts.
DimShipmentDocument Document dimension One row per unique ShipmentKey resolved in ETL from desadv, bol, and recadv references (avoiding document fan-out on quantity-bearing facts; or keep one reference on the fact table as a degenerate dimension).
DimLot Dimension Flattened category → gtin → tlc_code + tlc_source_key + reporting_uom (LotKey).
DimOriginLocation / DimDestinationLocation Role-playing Separate facility tables (gln, facility_name, temperature_zone), keyed by LocationKey.
DimShipDate / DimReceiveDate Role-playing Separate calendar tables with active relationships to dispatch and receipt dates.
DimHandoffStatus Junk dimension Packed flags (handoff_state, delay_flag, overdue_receipt_flag, capture_lag_flag).

Five star-schema rules keep metrics sound:

  • Integer surrogate keys: Power BI relationships bind one unique column on the “one” side to one column on the “many” side. Because food lots use composite natural keys (assigning location plus lot code, per our food lot traceability guide) and facilities can version, generate integer surrogates in the warehouse (or a Power Query index column on smaller models).
  • Separate role-playing dimensions: Give date and location tables self-describing columns (Ship Date, Receive Date, Origin Facility, Destination Facility) and descriptions to filter dates together without USERELATIONSHIP sprawl.
  • Distinct fact grains and pallet bridge: Keep paired legs separate from atomic scans; use BridgeLogisticsUnitLot for many-to-many DimLogisticsUnit-to-DimLot lookup while ETL expands dispatch-time pallet contents into lot-level legs.
  • Deliberate SCD Type 1 and Type 2: Use Type 1 SCD (non-incremental overwrite) for supplementary contacts. When a source does not retain history and attributes like temperature zones change, maintain Type 2 SCD in the warehouse (StartDate, EndDate empty or 12/31/9999, optional IsCurrent, unambiguous version label)—Power Query cannot detect changes and run time-based lookups alone, though Power BI loads a pre-loaded SCD Type 2 table directly.
  • Flattened hierarchies and explicit DAX measures: Keep DimLot flat (not snowflaked) for shorter filter paths and single-table hierarchies. Hide duration columns and mixed-UOM quantities, publishing explicit DAX measures (Missing Receipt Lots, Delayed Handoff Rate, Median Transit Hours, UOM-scoped variance)—required for Analyze in Excel (MDX) and MDX-designer paginated reports (Desktop live connections still let authors unhide fields).

Expose missing receiving confirmations without false alarms

Flagging every unmatched scan buries real exceptions. Enforce four ETL pairing rules:

  1. Anchor on the outbound step: Open legs from bizStep = shipping—or departing where sites capture granular staging_outbound, loading, and departing steps (CBV 2.0 § 7.1.3 makes shipping mutually exclusive from those three)—or consigning, its own outbound step covering staging_outbound, loading, departing, and accepting with a change of possession and/or ownership, replacing those four granular steps for the same outbound process. Resolve destination from destinationList (location, CBV 2.0 § 7.4.3) and desadv/bol in bizTransactionList.
  2. Separate custody acceptance from inventory receipt: Under CBV 2.0 § 7.1.3, receiving (received into inventory) is mutually exclusive from arriving and accepting (change of possession and/or ownership). Close a custody handoff on accepting only where agreed; use receiving or explicit receipt evidence for inventory-receipt metrics. Treat recadv (CBV 2.0 § 7.3.3) as a Receiving Advice reference and read quantities from that advice or a quantity-bearing event.
  3. Handle partial void_shipping and ErrorDeclaration on both sides: Under CBV 2.0 § 7.1.3, void_shipping applies to the specific objects or quantities declared not shipped, leaving the rest of the leg active on a partial void. Under EPCIS 2.0 § 7.4.1.2, an ErrorDeclaration leaves the prior event unmodified and matches every field except errorDeclaration and recordTime, requiring declarationTime and accepting optional reason (did_not_occur or incorrect_data, CBV 2.0 § 7.5.3) and correctiveEventIDs (in a CBV-compliant document, did_not_occur cannot accompany one or more correctiveEventIDs). Exclude invalidated outbound and inbound events from pairing while keeping originals and declarations in FactTraceabilityEvent for audit.
  4. Define mutually exclusive states and flag overdue yard arrivals: CBV 2.0 § 7.1.3 defines arriving as an object arriving at a location (for example, a truckload entering a yard before receipt or acceptance). Classify six mutually exclusive states in DimHandoffStatus—confirmed_full (confirmed matching shipped quantity), partial_receipt (below shipped quantity), receipt_overage_or_unquantified (confirmed above shipped quantity or without a reported quantity), in_transit (neither arrival nor receipt logged before cutoff), arrived_unreceived (arriving logged without receipt), and missing_receipt (no arrival or receipt past cutoff)—and raise overdue_receipt_flag even after yard arrival if receiving misses its dock cutoff.

Measure delayed handoffs with eventTime vs. recordTime

Transit and recording delays have different owners. EPCIS 2.0 § 7.4.1 defines three time fields:

  • eventTime (xsd:dateTimeStamp): time zone-independent instant the capturing application asserts the event occurred.
  • eventTimeZoneOffset: local UTC offset (+HH:MM or -HH:MM, 00–14 hours, requiring :00 at 14) for local dock display.
  • recordTime (xsd:dateTimeStamp): optional on EPCISEvent, ignored on capture, required on query retrieval; records when the repository stored the event and “does not describe anything about the real-world event.”

Compute dispatch-to-arrival (arrive_event_time - ship_event_time) and dispatch-to-receipt (receive_event_time - ship_event_time) in UTC so cross-time-zone lanes stay exact, and compute repository recording lag separately (record_time - event_time).

(Illustrative, made-up warehouse row—not from a real distributor, carrier, or Insonl client. Cutoffs of 6.0 hours dispatch-to-arrival, 1.0 hour yard-to-receipt, and 30 minutes recording lag are invented for this example.)

{
  "shipment_leg_key": "LEG-20261121-8841-01",
  "shipment_doc_ref": "DESADV:ASN-2026-8841",
  "lot_code": "LOT-20261120-B",
  "origin_location": "DC-NORTH",
  "destination_location": "STORE-DOCK-07",
  "ship_event_time_utc": "2026-11-21T04:00:00Z",
  "ship_tz_offset": "-05:00",
  "arrive_event_time_utc": "2026-11-21T11:30:00Z",
  "arrive_record_time_utc": "2026-11-21T12:15:00Z",
  "receive_event_time_utc": null,
  "as_of_utc": "2026-11-21T13:00:00Z",
  "shipped_qty": 120,
  "received_qty": null,
  "reporting_uom": "CASE",
  "dispatch_to_arrival_hours": 7.5,
  "arrive_record_lag_minutes": 45,
  "handoff_state": "arrived_unreceived",
  "delay_flag": true,
  "overdue_receipt_flag": true,
  "capture_lag_flag": true
}

received_qty stays null because actual received quantity is unknown until receipt posts. Splitting clocks directs follow-up: 7.5 hours (> 6.0 h invented cutoff) routes to dispatch, 1.5 hours awaiting receipt (> 1.0 h invented cutoff) routes to STORE-DOCK-07, and the 45-minute gap (> 30 min invented cutoff) shows a repository recording delay—pointing IT to investigate causes like scanner sync or feed latency rather than blaming the carrier.

Wire drill-through to the underlying records behind every alert

Alert counts help only when ops can open the records behind one exception. Per Microsoft’s Power BI drillthrough guide, use drillthrough (opening a detail page filtered to a selected item), not drill mode (hierarchy levels inside one visual).

Four setup rules:

  1. Key drillthrough to one leg: Bare shipment_doc_ref can return multiple lots, and bare tlc_code can span assigning locations and shipments. Place DimShipmentLeg[ShipmentLegKey] in the Drillthrough filters well (or pass equivalent compound filters reaching both facts); Power BI adds a Back button automatically.
  2. Pair right-click navigation with a drillthrough button: Include the same unaggregated DimShipmentLeg[ShipmentLegKey] in the source alert visual (an aggregate count card alone cannot identify which leg to open), then add a Drillthrough button conditionally formatted on filter context for one-click navigation.
  3. Keep source fields matching, unaggregated, and untransformed: Microsoft’s troubleshooting guide separates two checks: aggregating a field in the source visual (or omitting the matching field) can keep the drillthrough page out of the context menu, whereas duplicating or transforming it inconsistently in Power Query breaks filter propagation after opening.
  4. Show full FactTraceabilityEvent evidence: Display EPCIS event type and fields (eventID, bizStep, disposition, eventTime, eventTimeZoneOffset, recordTime, readPoint, bizLocation, bizTransactionList, plus errorDeclaration with declarationTime, reason, and correctiveEventIDs) alongside warehouse columns (tlc_code, quantity, reporting_uom, erp_doc_url). Across separate reports, publish both to the same workspace, match field names and data types, and enable Cross-report drillthrough in target settings (right-click page order can differ between Desktop and the Power BI service).

Keep pairing, retraction filtering, durations, and quantity checks in deterministic SQL and DAX. Bound AI downstream to verified rows: drafting carrier or dock follow-ups with ASN, lot code, and last readPoint, or summarizing recurring delay notes.

Next step

Before building visuals, map how your WMS, ERP, and partners emit events—the groundwork of Insonl’s business intelligence and analytics practice. Start with Insonl’s free AI workflow audit for a written map showing where deterministic pipelines belong and where AI pays off. The audit is free; Insonl charges only for optional work after it.

Sources