Definition

The Organisation Location is a bridge model which records the locations each organisation operates from with the main locations flagged.

Information

This model contains every location an organisation operates from, not just the primary one.

This model contains the following key information for locations:

• Record identifiers
• Linking fields to organisation and location

Grain and Scope

Each record is uniquely identified by either combining organisation_id, location_id and organisation or the organisation_uuid and location_uuid column.

This model includes the following key identifiers:

• organisation_id: The unique internal identifier for the organisation record within an organisation in an EMIS Web instance.
• organisation_guid: The GUID for the organisation within an organisation in an EMIS Web instance.
• organisation_uuid: The UUID derived from organisation_id and organisation, providing a stable unique identifier.
• location_id: The unique internal identifier for the location record within an organisation.
• location_guid: The GUID for the location within an organisation.
• location_uuid: The UUID derived from location_id and organisation, providing a stable unique identifier.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether organisation-location link has been deleted at source.
• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• Closed customer organisations are absent. Their locations are removed from this model, so a location referenced by an older record may not resolve.
• Do not assume one location per organisation. Aggregations that join Organisation to Location through this model will fan out unless you filter on is_main_location.
• A location can be shared between organisations, so joining from Location back to Organisation can also fan out.

Overview

graph TD
  A[Organisation]
  B[Location]

  A --> D[Organisation_Location]
  B --> D

  classDef nodeStyle stroke:#9961a4;
  class A,B,D nodeStyle;

  linkStyle default stroke:#117abf,fill:none;

Examples

Get main organisation location

SELECT
  *
FROM
  hive.explorer_ipcv_anon_olympus.organisation_location_v2
WHERE
  is_main_location_flag = TRUE;

Find deleted organisation locations

SELECT
  *
FROM
  hive.explorer_ipcv_anon_olympus.organisation_location_v2
WHERE
  is_deleted = TRUE;

Get organisation with location details

SELECT
  *
FROM
  hive.explorer_ipcv_anon_olympus.organisation_v2 org
  JOIN hive.explorer_ipcv_anon_olympus.organisation_location_v2 org_location ON org.emis_organisation_guid = org_location.emis_organisation_guid;