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 withoutUSERELATIONSHIPsprawl. - Distinct fact grains and pallet bridge: Keep paired legs separate from atomic scans; use
BridgeLogisticsUnitLotfor many-to-manyDimLogisticsUnit-to-DimLotlookup 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,EndDateempty or12/31/9999, optionalIsCurrent, 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
DimLotflat (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:
- Anchor on the outbound step: Open legs from
bizStep=shipping—ordepartingwhere sites capture granularstaging_outbound,loading, anddepartingsteps (CBV 2.0 § 7.1.3 makesshippingmutually exclusive from those three)—orconsigning, its own outbound step coveringstaging_outbound,loading,departing, andacceptingwith a change of possession and/or ownership, replacing those four granular steps for the same outbound process. Resolve destination fromdestinationList(location, CBV 2.0 § 7.4.3) anddesadv/bolinbizTransactionList. - Separate custody acceptance from inventory receipt: Under CBV 2.0 § 7.1.3,
receiving(received into inventory) is mutually exclusive fromarrivingandaccepting(change of possession and/or ownership). Close a custody handoff onacceptingonly where agreed; usereceivingor explicit receipt evidence for inventory-receipt metrics. Treatrecadv(CBV 2.0 § 7.3.3) as a Receiving Advice reference and read quantities from that advice or a quantity-bearing event. - Handle partial
void_shippingandErrorDeclarationon both sides: Under CBV 2.0 § 7.1.3,void_shippingapplies 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, anErrorDeclarationleaves the prior event unmodified and matches every field excepterrorDeclarationandrecordTime, requiringdeclarationTimeand accepting optionalreason(did_not_occurorincorrect_data, CBV 2.0 § 7.5.3) andcorrectiveEventIDs(in a CBV-compliant document,did_not_occurcannot accompany one or morecorrectiveEventIDs). Exclude invalidated outbound and inbound events from pairing while keeping originals and declarations inFactTraceabilityEventfor audit. - Define mutually exclusive states and flag overdue yard arrivals: CBV 2.0 § 7.1.3 defines
arrivingas an object arriving at a location (for example, a truckload entering a yard before receipt or acceptance). Classify six mutually exclusive states inDimHandoffStatus—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(arrivinglogged without receipt), andmissing_receipt(no arrival or receipt past cutoff)—and raiseoverdue_receipt_flageven 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:MMor-HH:MM,00–14hours, requiring:00at14) for local dock display.recordTime(xsd:dateTimeStamp): optional onEPCISEvent, 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:
- Key drillthrough to one leg: Bare
shipment_doc_refcan return multiple lots, and baretlc_codecan span assigning locations and shipments. PlaceDimShipmentLeg[ShipmentLegKey]in the Drillthrough filters well (or pass equivalent compound filters reaching both facts); Power BI adds a Back button automatically. - 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 aDrillthroughbutton conditionally formatted on filter context for one-click navigation. - 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.
- Show full
FactTraceabilityEventevidence: Display EPCIS event type and fields (eventID,bizStep,disposition,eventTime,eventTimeZoneOffset,recordTime,readPoint,bizLocation,bizTransactionList, pluserrorDeclarationwithdeclarationTime,reason, andcorrectiveEventIDs) 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
- GS1, EPCIS Standard, Release 2.0 (Ratified, June 2022): https://ref.gs1.org/standards/epcis/
- GS1, Core Business Vocabulary (CBV) Standard, Release 2.0 (Ratified, June 2022): https://ref.gs1.org/standards/cbv/
- Microsoft Learn (updated 30 Dec 2024), Understand star schema and the importance for Power BI: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- Microsoft Learn (updated 2 Mar 2026), Drillthrough in Power BI Reports: Navigate to Detailed Insights: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough
- Insonl, Business Intelligence and Analytics: https://insonl.com/services/enterprise/business-intelligence-analytics
- Insonl, How to Model Food Lot Traceability Events in Retail Operations: https://insonl.com/journal/retail-food-lot-traceability-event-mapping
- Insonl, A Shipment Event Model for Freight Exception Alerts: https://insonl.com/journal/freight-shipment-exception-event-model
- Insonl, Free AI Workflow Audit: https://insonl.com/ai
