Relationships and data model

Analytics is a graph of business-owned relational data. The model answers three questions for every query:

What does one row represent?
Which business object owns the row?
Which evidence connects it to other objects?

Core terms

Term	Meaning	Main key
Identity	A stable business object known inside a recognized object scope	`entity_id`
Business entity	A current full object body received from CargoWise	`entity_id`
Component	A repeated or nested object owned by an entity	`component_id`
Property	A scalar value owned by an entity or component	`property_id`
Relationship	An observed, directed connection between two entities or components	`relationship_id`
Grain	What one row in a relation represents	Relation-specific

An identity can exist without a current full entity. This happens when CargoWise supplies an explicit reference to an object whose full body has not arrived.

The graph

reporting.entity_identities resolves every evidence-backed endpoint. A row with is_reference_only = true has no current full object body. reporting.business_entities contains current full recognized objects. It stores shared route, cargo, direction, JobCosting summary, and lineage fields. Subtype relations add the business number or subtype-specific fields:

business_shipments
business_one_off_quotes
business_consols
business_bookings

Ownership through `entity_id`

Every current typed detail row belongs to a business entity through entity_id.

business_entities.entity_id
  |-- entity_dates.entity_id
  |-- entity_locations.entity_id
  |-- entity_staff_assignments.entity_id
  |-- entity_parties.entity_id
  |-- entity_charges.entity_id
  |-- entity_transport_legs.entity_id
  |-- entity_containers.entity_id
  |-- entity_packing_lines.entity_id
  `-- entity_properties.entity_id

This key is the normal join path for business reporting. A displayed job number, quote number, organization code, name, or route is not a replacement for entity_id. An organization code can appear in several party roles. A date code can occur several times. A charge code can repeat. The owning entity and the row’s documented grain preserve the meaning.

Nested ownership through `component_id`

Repeated objects receive a component_id. Nested components also retain parent_component_id. Consider one shipment containing a container and a penalty:

Entity S1
  `-- Container component C1
        `-- ContainerPenalty component P1
              `-- penalty properties

The rows keep these keys:

Container C1
  entity_id = S1
  component_id = C1

Penalty P1
  entity_id = S1
  component_id = P1
  parent_component_id = C1

The typed entity_container_penalties view also exposes container_component_id, which joins directly to entity_containers.component_id. Analytics does not create a component for every XML tag. Repeated meaningful objects become components. Scalar values become properties. Structural wrappers remain visible in source paths.

Properties and the long tail

reporting.entity_properties stores current scalar values that do not need a dedicated typed column. Each property retains:

the owning entity_id
an optional owning component_id
the CargoWise field name and relative path
the original value
safe typed and normalized values when available
source lineage

Properties let you use an uncommon field without waiting for a new report-specific table. They are still limited to recognized entity roots. Values outside those roots remain in the source observation layer.

Entity relationships

reporting.entity_relationships stores one observed directed edge. The important columns are:

Column	Meaning
`from_entity_id`	Directed source endpoint
`to_entity_id`	Directed target endpoint
`observing_entity_id`	Full entity whose XML supplied the evidence
`observing_entity_version`	Version that supplied the evidence
`relationship_type`	Business meaning of the edge
`evidence_basis`	How the source proved the relationship
source path and reference columns	Exact CargoWise evidence

Both endpoints join to entity_identities. The observing entity joins to business_entities.

Quote conversion

CargoWise can place QuoteNumber on a shipment, booking, or consol. Analytics creates a directed edge only from that explicit evidence:

Reference-only or full quote identity
  from_entity_id
        |
        v
entity_relationships
        |
        v
  to_entity_id
Shipment, booking, or consol

The observer is the shipment, booking, or consol whose XML contained QuoteNumber. Analytics never creates this link because a quote number resembles a job number or because route, customer, date, weight, or amount happens to match.

Parent and inherited context

Other entity relationship types include:

Type	Meaning
`contains_subshipment`	A parent entity contains a nested shipment
`subset_completeness`	A top-level source identifies complete nested subset data
`effective_transport_leg`	A shipment uses a leg supplied by another entity
`quote_to_shipment`	A quote explicitly connects to a shipment
`quote_to_booking`	A quote explicitly connects to a booking
`quote_to_consol`	A quote explicitly connects to a consol
`quote_to_business_entity`	A quote explicitly connects to another recognized entity type

The same logical edge can be observed in several versions. Use EXISTS for eligibility or deduplicate the endpoint pair when you need one business relationship.

Component relationships

Nesting and referencing are different. parent_component_id records structural nesting. entity_component_relationships records an explicit reference between components that can live in different parts of the XML tree. Supported component relationships include:

packing_line_to_container
instruction_container_link_to_container
instruction_packing_line_link_to_packing_line

For a packing line reference:

Packing line component P1
  from_component_id
        |
        v
entity_component_relationships
        |
        v
  to_component_id
Container component C1

resolution_status tells you whether the endpoint resolved. The source reference and diagnostic remain queryable when it did not.

Missing relationships

reporting.relationship_projection_audits explains why an expected entity link is present or absent.

Status	Meaning
`resolved`	The relationship was created
`source_reference_unresolved`	CargoWise supplied a reference but no safe endpoint was found
`ambiguous`	More than one possible endpoint remained
`not_observed`	The expected source evidence was absent
`unsupported_shape`	The source shape is not currently mapped

A missing link does not remove either entity or its values.

Current and historical relations

Relation kind	Behavior
`business_entities` and typed detail	Current full or effective current data
`entity_components` and `entity_properties`	Current graph for recognized entities
`entity_relationships`	Historical observed edge evidence
`entity_component_relationships`	Historical observed component references
`relationship_projection_audits`	Historical projection decisions
Payload, delivery, message, and observation views	Historical source evidence

Current detail can come from an earlier valid entity version after a partial update. Join current business queries by entity_id. Do not require the detail version to equal the entity’s current version.

Grain protects totals

A grain is what one row represents:

one row in business_entities means one current full entity
one row in entity_charges means one charge line
one row in entity_packing_lines means one packing line
one row in entity_relationships means one observed edge

Suppose one entity has two matching dates, three matching charges, and two matching staff assignments. A direct join produces 12 rows for the same entity. Use EXISTS when detail only decides whether an entity qualifies:

SELECT
  entities.entity_type,
  count(*) AS entity_count,
  sum(entities.total_weight_kg) AS total_weight_kg
FROM reporting.business_entities AS entities
WHERE EXISTS (
  SELECT 1
  FROM reporting.entity_dates AS dates
  WHERE dates.entity_id = entities.entity_id
    AND dates.date_type_code = 'ETD'
)
AND EXISTS (
  SELECT 1
  FROM reporting.entity_staff_assignments AS staff
  WHERE staff.entity_id = entities.entity_id
    AND staff.staff_role = 'sales'
)
GROUP BY entities.entity_type;

Use a pre-aggregated subquery when you need a detail measure beside an entity measure:

WITH charge_totals AS (
  SELECT
    entity_id,
    sum(sell_local_amount) AS sell_local_amount
  FROM reporting.entity_charges
  WHERE sell_is_posted IS TRUE
  GROUP BY entity_id
)
SELECT
  entities.entity_type,
  count(*) AS entity_count,
  sum(entities.total_weight_kg) AS total_weight_kg,
  sum(charge_totals.sell_local_amount) AS sell_local_amount
FROM reporting.business_entities AS entities
JOIN charge_totals
  ON charge_totals.entity_id = entities.entity_id
GROUP BY entities.entity_type;

Confirm currency before summing charge amounts. Confirm normalized units before summing weight. Continue to Power BI for a reusable semantic model or Query with SQL for query workflows.

Get started

Ingress

Stamping

Analytics

Relationships and data model

Core terms

The graph

Ownership through `entity_id`

Nested ownership through `component_id`

Properties and the long tail

Entity relationships

Quote conversion

Parent and inherited context

Component relationships

Missing relationships

Current and historical relations

Grain protects totals

​Core terms

​The graph

​Ownership through entity_id

​Nested ownership through component_id

​Properties and the long tail

​Entity relationships

​Quote conversion

​Parent and inherited context

​Component relationships

​Missing relationships

​Current and historical relations

​Grain protects totals

Core terms

The graph

Ownership through `entity_id`

Nested ownership through `component_id`

Properties and the long tail

Entity relationships

Quote conversion

Parent and inherited context

Component relationships

Missing relationships

Current and historical relations

Grain protects totals