Definition

The Sharing Agreement model describes the data-sharing agreements that control which organisations’ data you can access. Each row represents one organisation’s participation in a single agreement, along with the status of both parties and summary metrics for the sharing organisation.

Information

The model provides agreements that links a requester — the organisation asking for access — to one or more sharers — the organisations supplying data. Columns are prefixed to tell you which side they describe:

requester_ - The organisation requesting access under the agreement.
sharer_ - The organisation sharing its data under the agreement.
agreement_ - The agreement itself — its name, purpose, type and enabled state.

This model contains the following key information for sharing agreements:

Record identifiers
Agreement status
Requester and sharer context and status
Sharer metadata
Agreement population and user coverage
Agreement and system dates
User information

Grain and Scope

Each record is uniquely identified by agreement_guid, requester_organisation_guid, sharer_organisation_guid, emis_index_schema_id and user_id columns.

This model includes the following key identifiers:

agreement_guid: The unique internal identifier for the agreement record.
requester_organisation_guid: The GUID for the requester organisation.
sharer_organisation_guid: The GUID for the sharer organisation.
emis_index_schema_id: The unique internal identifier for the region that the organisation belongs to.
user_id: The unique internal identifier for the user accessing the data.

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

is_deleted: Indicates whether the sharing agreement 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.

Agreement and participation status

There are several status columns, and they answer different questions:

Is the agreement live? agreement_status

Available values:
- Enabled — The agreement is active.
- Disabled — The agreement is not active.
Has the requester activated its side of the agreement? requester_status

Available values:
- Not Activated — The requester has not yet activated their side.
- Activated — The requester has activated their side.
- Deactivated — The requester previously activated it, but it is not active now.
- Deleted — The requester’s side has been removed.
Has the sharer activated its side of the agreement? sharer_status

Available values:
- 01. Ready for Activation — The agreement is visible in EMIS Web Data Sharing Manager and is waiting for the Data Controller to review and approve it. No data is being shared yet.
- 02. Activated — The Data Controller has approved the agreement and data sharing is now live. Their data will appear in your Explorer environment.
- 03. Deactivated — The Data Controller has withdrawn their approval for data sharing. The agreement is still in place on EMIS Web Data Sharing Manager and they can reactivate it at any time. While deactivated, their data will not be processed.
- 04. Deleted — The Data Controller has been permanently removed from the agreement. This typically happens when a contract or data protection agreement has ended, or the Data Controller was listed in error. The agreement is no longer visible to them and cannot be reactivated.
- 05. Closed Site — The Data Controller has permanently closed (for example, due to a merger, closure, or move to another supplier). Data already extracted to your local servers will remain, but their data will be removed from your Explorer environment.
sharer_status values are deliberately prefixed with a sort key so that alphabetical ordering matches lifecycle order. Match on the full string, including the prefix.
Is the requester activated right now? requester_is_activated

Available values:
- TRUE — The requester is currently activated.
- FALSE — The requester is not currently activated.
Has the requester ever been activated? requester_was_activated

Available values:
- TRUE — The requester has been activated at some point.
- FALSE — The requester has never been activated.
Is the sharer activated right now? sharer_is_activated

Available values:
- TRUE — The sharer is currently activated.
- FALSE — The sharer is not currently activated.
Has the sharer ever been activated? sharer_was_activated

Available values:
- TRUE — The sharer has been activated at some point.
- FALSE — The sharer has never been activated.
Is data actually flowing for this requester/sharer pairing? can_process

Available values:
- TRUE — Data can currently be processed for this pairing.
- FALSE — Data is not currently being processed for this pairing.

Overview

flowchart TD
    DSA_01["Agreement 01"]
    DSA_02["Agreement 02"]
    EX_DSA_01["Explorer Agreement 01"]
    EX_DSA_02["Explorer Agreement 02"]

    org_01["Organisation 1001"]
    org_02["Organisation 1002"]
    org_03["Organisation 1003"]
    org_04["Organisation 1004"]
    EMIS_INDEX([EMIS INDEX])
    DSA[Data Sharing Agreements]
    ACCESS_GUARD([AccessGuard])
    usr_01["Explorer User 1"]
    usr_02["Explorer User 2"]
    Sharing_model[["Sharing Organisation Model"]]

    org_01 --> DSA_01
    org_02 --> DSA_01
    org_03 --> DSA_01
    org_03 --> DSA_02
    org_04 --> DSA_02

    DSA_01 --> EMIS_INDEX
    DSA_02 --> EMIS_INDEX

    EMIS_INDEX --> DSA
    DSA --> ACCESS_GUARD
    ACCESS_GUARD --> EX_DSA_01
    ACCESS_GUARD --> EX_DSA_02
    ACCESS_GUARD --> usr_01
    ACCESS_GUARD --> usr_02
    usr_01 --> EX_DSA_01
    usr_02 --> EX_DSA_02
    EX_DSA_01 --> Sharing_model
    EX_DSA_02 --> Sharing_model

    classDef nodeStyle stroke:#9961a4;
    class DSA_01,DSA_02,EX_DSA_01,EX_DSA_02,org_01,org_02,org_03,org_04,EMIS_WEB,DSA,ACCESS_GUARD,usr_01,usr_02,Sharing_model nodeStyle;

    linkStyle default stroke:#117abf,fill:none

Things to be aware of

Each record is per agreement, per requester organisation, per sharer organisation, per EMIS Index schema, per user.
Results are user specific. This model is filtered to the signed-in user. You will only ever see the agreements and organisations linked to your own account, and two users querying the same view can legitimately get different results. Do not compare row counts between users, and do not cache results as if they were global.
The organisation column is different on this model. It does not identify the EMIS Web instance the record came from as it does on other models.
organisation provides the sharer’s customer database number.
organisation = 'EMIS' is set when is_deleted = TRUE.
Join on sharer_organisation_guid or requester_organisation_guid against organisation_guid on the Organisation model to identify the EMIS Web instance of the sharer or the requester.
The grain includes user_id, so use COUNT(DISTINCT agreement_guid) rather than COUNT(*) when counting agreements.
Counts come from EMIS Index, not from your extract, so they will not tie exactly to row counts in the clinical models.
The *_is_activated and *_was_activated pairs let you distinguish an organisation that has never activated from one that activated and later withdrew.

Examples

Get active sharer organisations (non-deleted)

SELECT
  agreement_name,
  sharer_name,
  sharer_ods_code,
  sharer_status,
  active_user_count,
  active_patient_count
FROM
  hive.explorer_ipcv_vanilla.sharing_agreement_v2
WHERE
  is_deleted = FALSE
  AND sharer_status = '02. Activated';

Agreements modified in timeframe

SELECT
  agreement_name,
  sharer_name,
  sharer_status,
  last_modified_datetime
FROM
  hive.explorer_ipcv_vanilla.sharing_agreement_v2
WHERE
  is_deleted = FALSE
  AND last_modified_datetime >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
ORDER BY
  last_modified_datetime DESC;

Get closed or deactivated organisations

SELECT
  agreement_name,
  sharer_name,
  sharer_ods_code,
  sharer_status,
  sharer_close_datetime
FROM
  hive.explorer_ipcv_vanilla.sharing_agreement_v2
WHERE
  is_deleted = FALSE
  AND sharer_status IN ('03. Deactivated', '05. Closed Site');