Definition

The organisation data model represents a reference data set that’s used to join to other data models, in order to retrieve organisation level information.

Information

The data model is created by identifying a list of organisation IDs (GP practices) that have engaged with the organisations outlined in your data sharing agreement. This list of organsiations is then used to collect the relevant information, which is then consolidated and presented in a single accessible data model.

Status Indicators

In the Organisation model, several indicators are available to distinguish the current status of an organisation:

• is_open - Indicates whether an organisation is currently open or closed. A FALSE value indicates the organisation is closed.
• is_deleted - Indicates whether an organisation has been deleted from the source system.
• is_this_organisation - Identifies the main organisation in the data set.
• transform_datetime - While not directly indicating status, this field shows when the organisation record was last updated in the data model, which can help determine the currency of the status information.

Overview

flowchart TD
    n7["Organisation 123"] --> n10["Patient A"]
    n5["Organisation 98"] --> n10
    n6["Organisation 123"] --> n11["Patient B"]
    n4["Organisation 20"] --> n11
    n8["Organisation 47"] --> n11
    n9["Organisation 123"] --> n12["Patient C"]
    n10 --> n13["Gather Used Organisations"]
    n11 --> n13
    n12 --> n13
    n13 --> n16["Unique Organization IDs: 123, 98, 20, 47"]
    n16 --> n14["Final Event"]

    classDef nodeStyle stroke:#9961a4;
    class n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16 nodeStyle;

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

What’s changing in iPCV v2

Change in Organisation Collection

The organisation data model in iPCV v2 contains significantly more rows than v1, while preserving all organisation IDs from v1.

Why?

iPCV v1 derived organisations primarily from patient interactions and active_orgs tables. In v2, we’ve expanded the collection to include any organisation referenced by users in any table, resulting in more comprehensive coverage.

Customer benefit

This change provides the following benefits:

• More comprehensive organisation reference data
• Better coverage for cross-referencing organisations across models
• Reduced likelihood of missing organisation details when joining tables

Customer action

No immediate action required, but customers should be aware that queries may return more organisation records than before. If your analysis depends on the specific number of organisations or assumes a particular organisation set, you may need to add additional filtering criteria.

Name Column Change

The column used to represent organisation names has changed from ‘organisationnamein’ in v1 to ‘organisationdisplayname’ in v2.

Why>

This change was made to better reflect how organisation names actually appear in the EMIS Web user interface. The V1 column reflected how names were stored internally in the database tables, rather than how users would recognize them.

Customer benefit

This change provides the following benefits:

• Organisation names that match what users see in EMIS Web
• More recognizable and intuitive organisation identification
• Consistent representation with the source system UI

Customer action

Customers should update any queries or applications that specifically reference the ‘organisationnamein’ column to now use ‘organisationdisplayname’ instead.

Address Fields Change

Address field column names have changed from the generic format ‘address_line_[line number]’ to match their respective field names in EMIS Web.

Why?

This change aligns the column names with how they appear in the source system, providing more context about each address component and making the data model more intuitive for users familiar with EMIS Web.

Customer benefit

This change provides the following benefits:

• More descriptive field names that indicate the purpose of each address component
• Easier cross-reference with source system for troubleshooting
• Improved clarity when working with address data

Customer action

Customers should update any queries or applications that reference the old ‘address_line_[line number]’ columns to use the new EMIS Web-aligned field names.

is_open Calculation Change

The calculation of the ‘is_open’ field now occurs during the ETL process rather than being pulled directly from EMIS Web upstream.

Why?

In v1, the organisation status was determined at the point of ingestion from EMIS Web. By recalculating during transformation in v2, the status now reflects the organisation’s state at the time of transformation rather than ingestion, providing more current information.

Customer benefit

This change provides the following benefits:

• More up-to-date organisation status information
• Consistent timing of status calculations across the data pipeline
• Better alignment with other transformed data elements

Customer action

No immediate action required, but customers should be aware that organisation status may differ from V1 in some cases due to the timing difference in status calculation.

Removal of 3 NULL columns

The following columns have been ‘removed’

• ccg_organisation_id

• parent_organisation_id

• parent_type

Why?

These columns have been removed as they consistently contained null values, offering no meaningful contribution to the data model. This change helps simplify the data set & reduces unnecessary processing:

Customer benefit

This change provides the following benefits:

• Unnecessary processing

• Cleaner data models

• Less processing

Customer action

If these columns are currently used you will need to remove them from your processing.

Removal of the ‘Searchdata’ field

The searchdata field, which was a concatenation of the address columns has also been removed.

Why?

Since this field also provided no additional value and only added complexity to the processing, it is no longer included in the updated model

Customer benefit

These changes aim to enhance the performance of the data model and ensure that only the relevant actionable data is included.

Customer action

If you currently utilise the above columns & fields in your reporting, please make necessary adjustments.

Schema updates for data maintenance

The following columns have been added to enhance data maintenance and provide more comprehensive organisation information:

• organisation_id - Unique identifier for the organisation
• organisation_type_id - Identifier indicating the type of organisation
• speciality_codes - Codes representing the medical specialities associated with the organisation
• commissioner - Information about the commissioning body for the organisation
• is_deleted - Indicates whether an organisation record has been marked as deleted or is no longer active

The following columns have been removed:

• ccg_emis_organisation_id and parent_emis_organisation_id - Deprecated in iPCV v1
• ccg_ods_code, full_address, parent_organisation_type_description, releaseversion and nhs_role - No longer available in iPCV v2

Why?

These schema changes streamline the organisation data model by removing deprecated and unavailable fields while adding new columns that provide better organisation classification and tracking capabilities. The additions enable more granular analysis of organisation types, specialities, and commissioning relationships.

Customer benefit

This change provides the following benefits:

• Enhanced organisation identification and classification through dedicated ID fields
• Improved ability to analyze organisations by type and medical speciality
• Better tracking of commissioning relationships
• Cleaner data model with removal of deprecated and unavailable columns
• More reliable data quality monitoring through the is_deleted flag

Customer action

Customers should take the following actions:

• Update queries and reports that reference removed columns (ccg_emis_organisation_id, parent_emis_organisation_id, ccg_ods_code, full_address, parent_organisation_type_description, releaseversion, nhs_role)
• Leverage new columns (organisation_id, organisation_type_id, speciality_codes, commissioner) to enhance organisation analysis and reporting
• Use the is_deleted column to filter out obsolete organisation records and maintain data quality in your queries

Column naming consistency updates

For consistency across different data flavours, the following columns have been renamed:

• deleted renamed to is_deleted - Provides clearer indication of the boolean nature of this field
• execution_data renamed to _execution_date - Corrects naming convention and adds underscore prefix for consistency.

Benefits

These changes provide the following benefits:

• Enhanced organisation identification and classification capabilities
• Improved data maintenance through deletion tracking
• Better consistency in naming conventions across data models
• More comprehensive organisation metadata for analysis and reporting

Customer Action

• Update your ETL processes and queries to reference the new column names (is_deleted instead of deleted, _execution_date instead of execution_data)
• Leverage the new columns (organisation_id, organisation_type_id, speciality_codes, commissioner) for enhanced organisation analysis
• Use the is_deleted column to filter out obsolete organisation records and maintain data quality

Examples

Get main organisation

select
  *
from hive.explorer_ipcv.srv_organisation
where is_this_organisation = TRUE

Find closed organisations

select
  *
from hive.explorer_ipcv.srv_organisation
where is_open = FALSE

Get organisation and location

select
  *
from hive.explorer_ipcv.srv_organisation organisation
JOIN
  hive.explorer_ipcv.srv_location location
    ON organisation.location_guid = location.location_guid
    AND organisation.organisation = location.organisation