Definition

The Problem data model represents clinical problems and conditions recorded within EMIS Web for registered patients. A problem is a specific type of observation that categorises a clinical finding as a long-term or significant condition, enabling clinicians to track and manage the patient’s health history over time.

Information

The Problem data model provides a view of all problems associated with patients across healthcare organisations. Problems are derived from the observation data and include clinical coding (SNOMED CT, Read v2), dates, significance, status, and the linked consultation in which they were recorded.

Each record in this model can be uniquely identified using the combination of observation_id (the observation identifier for the problem record) and organisation.

Key Data Fields

Some of the key data fields are as follows:

Problem Identifiers

• observation_id: The identifier of the parent problem or linked problem observation, used when a problem is linked to another observation.
• organisation: The unique identifier for the healthcare organisation (in CDB format) where the problem was recorded.

Dates

• effective_datetime: The clinical effective datetime of the problem.
• end_date: The date the problem was resolved or ended, if applicable.
• availability_datetime: The date the problem was recorded in EMIS Web.

Status and Significance

• problem_status_description: The current status of the problem (e.g., Active, Inactive).
• problem_significance_description: The EMIS-specific significance rating of the problem (e.g., Major, Minor).

Overview

flowchart TD
  org1["Organisation 1"] --> patA["Patient A"]
  org1 --> patB["Patient B"]
  org2["Organisation 2"] --> patC["Patient C"]

  patA --> prob1["Problem: Hypertension"]
  patA --> prob2["Problem: Diabetes"]
  patB --> prob3["Problem: Asthma"]
  patC --> prob4["Problem: COPD"]

  prob1 --> gather["Gather Problem Data"]
  prob2 --> gather
  prob3 --> gather
  prob4 --> gather
  gather -->|Unique IDs: id + organisation| etl["ETL"]
  etl -->final["Final Consolidated Data"]

  classDef nodeStyle stroke:#9961a4;
  class org1,patA,patB,org2,patC,prob1,prob2,prob3,prob4,gather,etl,final nodeStyle;

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

Changes in iPCV v2

Problem Observation Link Improvement

In iPCV v2, problem-observation linking has been improved to include the first linked problem across all episodes, whereas v1 only included problems from associated episodes. This ensures that clinically significant problem relationships are captured even when problems are recorded on follow-on episodes.

Why?

This improves the accuracy and completeness of problem-observation linkages, ensuring all relevant problems are surfaced.

Customer benefit

• More complete problem data when analysing observation-problem relationships
• Reduced risk of missing clinically significant problem links

Customer action

• No action needed

Uncoded Observations Included in v2

In iPCV v1, only coded observations (those with a clinical code) were processed in the problem model. In iPCV v2, uncoded observations are also included, as some parent observations are uncoded.

Why?

Ensures completeness of the problem dataset, including cases where clinicians record problems without a formal code.

Customer benefit

• More complete problem records, including previously missing uncoded entries

Customer action

• Update any processes that assume all problem records will have a clinical code to handle records where code fields may be null

Removal of Redundant or Deprecated Fields

In iPCV v1, several attributes of other entities were directly included in the problem model. In iPCV v2, the below fields have been removed from the problem data model, and relevant IDs are provided to retrieve this information from dedicated extended data models.

Why?

This change simplifies the core problem model, making it lighter and easier to manage. By moving specific attributes to their own models, data redundancy is reduced, and maintainability is improved. This allows for more focused information within the extended models, that increases the performance of iPCV v2.

Note: These decommissioned columns will be removed in a future iPCV release; plan migrations accordingly

Decommissioned Column	Relevant ID/ Column	Reason / Extended Data Model to be Referred
other_code	code_id	codeable_concept_v2
other_code_system	code_id	codeable_concept_v2
other_display	code_id	codeable_concept_v2
readv2_code	code_id	codeable_concept_v2
snomed_concept_id	code_id	codeable_concept_v2
snomed_description_id	code_id	codeable_concept_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
emis_last_review_userinrole_guid	last_review_user_in_role_id	user_in_role_v2
last_review_user_in_role_guid	last_review_user_in_role_id	user_in_role_v2
sensitive_patient_flag	patient_id	patient_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	Removed. Instead, use regular_patient_flag`
regular_patient_flag	patient_id	patient_v2
processing_id		Always NULL as it is deprecated in v2
data_filter		Removed as it is deprecated in v2
_record_version		No longer available in v2
_update_date		Changes tracked differently in v2
_update_hour		Changes tracked differently in v2

Customer benefit

• Improved Performance: A leaner problem model can lead to faster query performance.
• Enhanced Scalability: Decoupled models are easier to maintain and extend independently.
• Data Consistency: Centralizing specific attributes in their own models ensures a single source of truth.

Customer action

• Review all reports and queries that rely on the decommissioned fields listed above
• Update them to join with the appropriate extended data models using the new IDs provided in the table
• Test thoroughly to ensure data accuracy and query performance with the new model structure

Addition of New Columns

The v2 Problem model introduces additional columns to clinical context. These include:

• observation_id
• organisation_id
• patient_id
• entered_by_user_in_role_id
• last_review_user_in_role_id
• authorising_user_in_role_id
• consultation_guid
• consultation_uuid
• parent_problem_uuid
• consultation_source_original_term
• emis_encounter_guid
• exa_encounter_guid
• exa_parent_observation_guid
• observation_organisation_id
• observation_organisation_guid
• code_id
• problem_significance_description

Why?

These columns have been added to enhance the data model:

• Identifier and key columns: Provide stable integer surrogate keys for efficient joins, replacing GUID-only approaches from v1; integer user-in-role IDs align with the user_in_role model’s primary key
• Relationship and context columns: Supply richer consultation and observation linkage context unavailable in v1, and UUID5-derived identifiers that are stable and reproducible across processing runs

Customer benefit

• Efficient joins using integer surrogate keys instead of GUIDs
• Direct access to consultation source labels without additional lookups
• Richer observation and consultation context for deeper analysis

Customer action

• Use observation_id, organisation_id, and patient_id for efficient joins to their respective data models
• Replace GUID-based joins with entered_by_user_in_role_id, last_review_user_in_role_id, and authorising_user_in_role_id when joining to the user_in_role model
• Leverage consultation_uuid and consultation_source_original_term to directly access consultation context without separate lookups
• Use exa_encounter_guid, emis_encounter_guid, and exa_parent_observation_guid to trace observation and encounter relationships
• Validate downstream systems or reports to ensure they can utilize the new columns effectively
• No immediate action required if consuming the fields directly from the v2 model

Examples

Retrieve all active problems for a patient

SELECT
    emis_observation_guid,
    emis_patient_id,
    emis_original_term,
    effective_date,
    problem_status_description,
    emis_problem_significance
FROM
    hive.explorer_ipcv_vanilla.problem_v2
WHERE
    emis_patient_id = 12345
    AND is_deleted = FALSE
    AND problem_status_description = 'Active'
ORDER BY
    effective_date DESC

Find problems linked to a specific consultation

SELECT
    emis_observation_guid,
    emis_original_term,
    effective_date,
    problem_status_description
FROM
    hive.explorer_ipcv_vanilla.problem_v2
WHERE
    emis_encounter_guid = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
    AND is_deleted = FALSE