Query with SQL

Your Analytics credential can run arbitrary read-only SELECT queries over reporting.*. SQL uses the same relations as Power BI, but each query chooses its own grain, joins, filters, and result. Read Relationships and data model before combining repeating detail.

Use this process

For each question:

Define what one result row should represent.
Choose the relation with that grain.
Join ownership through entity_id or component_id.
Traverse explicit relationship views when the question crosses independent objects.
Use EXISTS for detail that only filters the result.
Pre-aggregate detail before combining measures from different grains.
Validate codes, roles, currency, units, dates, nulls, and known records.

Do not begin with shipments or one_off_quotes by habit. Begin with the relation that matches the answer.

Choose the starting grain

Question measures	Start from	One row means
Current recognized objects	`business_entities`	One current full entity
Known identities, including references	`entity_identities`	One full or reference-only identity
Charge activity	`entity_charges`	One charge line
Container penalties	`entity_container_penalties`	One penalty term
Packing-line cargo	`entity_packing_lines`	One packing line
Explicit business links	`entity_relationships`	One observed edge
Component references	`entity_component_relationships`	One observed component reference
Source values	`cargowise_xml_observations`	One parsed scalar or attribute observation

Use a wide convenience view only when the report intentionally uses one subtype and its columns.

Example 1: Inventory current entities

This query starts from business_entities because one result row summarizes one entity type:

SELECT
  entities.entity_type,
  count(*) AS entity_count,
  count(*) FILTER (
    WHERE entities.total_weight_kg IS NOT NULL
  ) AS entities_with_normalized_weight,
  min(entities.first_seen_at) AS first_seen_at,
  max(entities.last_seen_at) AS last_seen_at
FROM reporting.business_entities AS entities
GROUP BY entities.entity_type
ORDER BY entity_count DESC, entities.entity_type;

This includes recognized shipments, quotes, bookings, and consols without merging them into one business fact. entity_type keeps their meaning explicit. Add subtype business numbers only when needed:

SELECT
  entities.entity_id,
  entities.entity_type,
  shipments.job_number,
  quotes.quote_number,
  bookings.booking_number,
  consols.consol_number
FROM reporting.business_entities AS entities
LEFT JOIN reporting.business_shipments AS shipments
  ON shipments.entity_id = entities.entity_id
LEFT JOIN reporting.business_one_off_quotes AS quotes
  ON quotes.entity_id = entities.entity_id
LEFT JOIN reporting.business_bookings AS bookings
  ON bookings.entity_id = entities.entity_id
LEFT JOIN reporting.business_consols AS consols
  ON consols.entity_id = entities.entity_id
ORDER BY entities.entity_type, entities.entity_key;

Only the matching subtype relation contributes a business number.

Example 2: Analyze charge lines

This query starts from entity_charges because the result measures charges, not entities:

SELECT
  entities.entity_type,
  charges.charge_code,
  charges.charge_description,
  charges.sell_overseas_currency_code,
  count(*) AS charge_line_count,
  sum(charges.sell_overseas_amount) AS sell_overseas_amount
FROM reporting.entity_charges AS charges
JOIN reporting.business_entities AS entities
  ON entities.entity_id = charges.entity_id
WHERE charges.sell_is_posted IS TRUE
  AND charges.sell_overseas_amount IS NOT NULL
  AND charges.sell_overseas_currency_code IS NOT NULL
GROUP BY
  entities.entity_type,
  charges.charge_code,
  charges.charge_description,
  charges.sell_overseas_currency_code
ORDER BY
  entities.entity_type,
  charges.sell_overseas_currency_code,
  sell_overseas_amount DESC;

The currency remains part of the grouping. Do not sum amounts across currencies. Add entity-level information without changing the charge grain:

SELECT
  charges.charge_id,
  charges.entity_id,
  entities.entity_type,
  entities.entity_key,
  charges.charge_code,
  charges.debtor_code,
  charges.creditor_code,
  charges.sell_local_amount,
  charges.cost_local_amount,
  charges.sell_is_posted,
  charges.cost_is_posted
FROM reporting.entity_charges AS charges
JOIN reporting.business_entities AS entities
  ON entities.entity_id = charges.entity_id
WHERE charges.charge_code = 'FRT'
ORDER BY entities.entity_type, entities.entity_key, charges.sequence;

FRT is illustrative. Inspect your organization’s observed codes before creating a recurring query.

Example 3: Traverse explicit relationships

This query resolves both endpoints through entity_identities. It keeps the observer separate:

SELECT
  relationships.relationship_type,
  from_identity.entity_type AS from_entity_type,
  from_identity.entity_key AS from_entity_key,
  from_identity.is_reference_only AS from_is_reference_only,
  to_identity.entity_type AS to_entity_type,
  to_identity.entity_key AS to_entity_key,
  to_identity.is_reference_only AS to_is_reference_only,
  observer.entity_type AS observing_entity_type,
  observer.entity_key AS observing_entity_key,
  relationships.evidence_basis,
  relationships.source_reference_name,
  relationships.source_reference_value,
  relationships.observed_at
FROM reporting.entity_relationships AS relationships
JOIN reporting.entity_identities AS from_identity
  ON from_identity.entity_id = relationships.from_entity_id
JOIN reporting.entity_identities AS to_identity
  ON to_identity.entity_id = relationships.to_entity_id
JOIN reporting.business_entities AS observer
  ON observer.entity_id = relationships.observing_entity_id
WHERE relationships.relationship_type IN (
  'quote_to_shipment',
  'quote_to_booking',
  'quote_to_consol'
)
ORDER BY relationships.observed_at DESC;

One logical relationship can have several evidence rows. Deduplicate the endpoint pair when the question needs one business edge:

SELECT DISTINCT
  relationships.from_entity_id,
  relationships.to_entity_id,
  relationships.relationship_type
FROM reporting.entity_relationships AS relationships
WHERE relationships.relationship_type = 'quote_to_shipment';

To traverse a packing-line reference to its resolved container:

SELECT
  relationships.from_entity_id,
  packing_lines.packing_line_id,
  containers.container_number,
  relationships.evidence_basis,
  relationships.resolution_scope
FROM reporting.entity_component_relationships AS relationships
JOIN reporting.entity_packing_lines AS packing_lines
  ON packing_lines.component_id = relationships.from_component_id
JOIN reporting.entity_containers AS containers
  ON containers.component_id = relationships.to_component_id
WHERE relationships.relationship_type = 'packing_line_to_container'
  AND relationships.resolution_status = 'resolved';

The component query uses the explicit component edge. It does not join a packing line to a container because their displayed container numbers happen to match.

Filter entities without multiplying them

Use one EXISTS block for each repeating relation that only determines eligibility:

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 dates.date_value_local >= '2026-04-01'
    AND dates.date_value_local < '2026-05-01'
)
AND EXISTS (
  SELECT 1
  FROM reporting.entity_staff_assignments AS staff
  WHERE staff.entity_id = entities.entity_id
    AND staff.staff_role = 'sales'
)
AND EXISTS (
  SELECT 1
  FROM reporting.entity_charges AS charges
  WHERE charges.entity_id = entities.entity_id
    AND charges.sell_is_posted IS TRUE
)
GROUP BY entities.entity_type;

Replace the example date and role codes with values observed in your data.

Pre-aggregate detail measures

If a result needs both entity and detail measures, reduce the detail to one row per entity_id before joining:

WITH posted_sell AS (
  SELECT
    entity_id,
    sell_overseas_currency_code,
    sum(sell_overseas_amount) AS sell_overseas_amount
  FROM reporting.entity_charges
  WHERE sell_is_posted IS TRUE
    AND sell_overseas_amount IS NOT NULL
    AND sell_overseas_currency_code IS NOT NULL
  GROUP BY entity_id, sell_overseas_currency_code
)
SELECT
  entities.entity_type,
  posted_sell.sell_overseas_currency_code,
  count(*) AS entity_currency_rows,
  sum(posted_sell.sell_overseas_amount) AS sell_overseas_amount
FROM reporting.business_entities AS entities
JOIN posted_sell
  ON posted_sell.entity_id = entities.entity_id
GROUP BY
  entities.entity_type,
  posted_sell.sell_overseas_currency_code
ORDER BY
  entities.entity_type,
  posted_sell.sell_overseas_currency_code;

The result grain is one entity type and currency combination.

Inspect codes before filtering

CargoWise roles and codes can differ by organization:

SELECT
  'entity_type' AS field,
  entity_type AS value,
  count(*) AS row_count
FROM reporting.business_entities
GROUP BY entity_type

UNION ALL

SELECT
  'date_type_code',
  date_type_code,
  count(*)
FROM reporting.entity_dates
WHERE date_type_code IS NOT NULL
GROUP BY date_type_code

UNION ALL

SELECT
  'staff_role',
  staff_role,
  count(*)
FROM reporting.entity_staff_assignments
WHERE staff_role IS NOT NULL
GROUP BY staff_role

ORDER BY field, row_count DESC, value;

Inspect descriptions and names when a code is unfamiliar. Use stable source codes in recurring queries after confirming their meaning.

Validate the result

Before sharing an answer, check:

the result still has the intended grain
every join uses a documented key
entity relationships come from explicit edges
codes and roles mean what the business question expects
currencies are not mixed
normalized units are populated for measured rows
local dates and UTC dates are not mixed without a rule
null and unsupported values remain visible
a few known CargoWise records produce the expected result

If the required value is not in a typed relation, continue to Discover fields.

Get started

Ingress

Stamping

Analytics

Query with SQL

Use this process

Choose the starting grain

Example 1: Inventory current entities

Example 2: Analyze charge lines

Example 3: Traverse explicit relationships

Filter entities without multiplying them

Pre-aggregate detail measures

Inspect codes before filtering

Validate the result

​Use this process

​Choose the starting grain

​Example 1: Inventory current entities

​Example 2: Analyze charge lines

​Example 3: Traverse explicit relationships

​Filter entities without multiplying them

​Pre-aggregate detail measures

​Inspect codes before filtering

​Validate the result

Use this process

Choose the starting grain

Example 1: Inventory current entities

Example 2: Analyze charge lines

Example 3: Traverse explicit relationships

Filter entities without multiplying them

Pre-aggregate detail measures

Inspect codes before filtering

Validate the result