Definition

The Observation data model captures coded clinical observations recorded in EMIS Web and made available for OpenSafely analysis. It includes measurements, problem-linked observations, and referral markers.

Information

Each record represents a single observation event for a patient, optionally linked to a consultation and consultation section.

Each record can be uniquely identified using the observation_id and organisation.

• is_deleted - Indicates whether the observation has been soft deleted in the source system.
• problem_is_deleted - Indicates whether the linked problem observation has been marked as deleted.
• is_problem / is_referral - Flags indicating the observation type.
• transform_datetime - Shows when the record was last updated in the data model.

Examples

Recent numeric observations for a specific patient

SELECT
  observation_id,
  patient_id,
  effective_datetime,
  snomed_concept_id,
  numeric_operator,
  numeric_value,
  numeric_unit,
  consultation_id,
  organisation
FROM explorer_open_safely.observation
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND numeric_value IS NOT NULL
  AND NOT is_deleted
ORDER BY effective_datetime DESC

Active problem-linked observations

SELECT
  observation_id,
  patient_id,
  effective_datetime,
  observation_type_description,
  problem_observation_id,
  problem_end_datetime,
  organisation
FROM explorer_open_safely.observation
WHERE
  is_problem
  AND problem_end_datetime IS NULL
  AND NOT is_deleted