Definition

Purpose

The observation model extracts clinical observation/measurement data for patients included in recruit studies from the anon extract. The view is scoped to fully anonymised patients via the included patients view.

Information

The Observation model extracts clinical observation and measurement data for patients included in recruit studies. It captures the key identifiers and foreign key relationships linking observations to patients, consultations, codes, and other entities. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

Each observation record is uniquely identified by a combination of emis_observation_guid and organisation.

• emis_observation_guid (PK): The unique identifier for the observation record
• pseudo_registration_guid (FK): Hashed registration identifier - links to patient
• pseudo_patient_id (FK): Hashed patient identifier - links to patient
• emis_registration_organisation_guid (FK): Links to organisation
• emis_authorising_userinrole_guid (FK): Links to user_in_role
• emis_enteredby_userinrole_guid (FK): Links to user_in_role
• emis_parent_observation_guid (FK): Links to parent observation record
• emis_code_id (FK): Links to clinical_code
• emis_problem_observation_guid (FK): Links to problem observation
• emis_consultation_guid (FK): Links to consultation
• study_id (FK): Links to user_studies

Examples

Get all observations for a study

SELECT *
FROM hive.explorer_recruit_anon.observation
WHERE study_id = 'STUDY001'
LIMIT 100;

Find observations for an organisation

SELECT *
FROM hive.explorer_recruit_anon.observation
WHERE organisation = 'CDB-0001'
LIMIT 100;

Get numeric observations with units

SELECT
  emis_observation_guid,
  patient_id,
  emis_code_id,
  numericvalue,
  uom,
  observation_type,
  effective_date
FROM hive.explorer_recruit_anon.observation
WHERE numericvalue IS NOT NULL
  AND organisation = 'CDB-0001'
LIMIT 100;