Synthetic MHSDS local dictionary (demonstration only)
Synthetic demonstration logic only. This is not an official Dorset HealthCare or Trust data dictionary. It is invented for this personal demonstration site and is not for operational use, submission or reporting.
Real Trust metric definitions are IG-controlled, versioned and maintained by an Information Lead. Any figure used in a live report must be validated against the approved local dictionary and signed off by the accountable owner.
RiO is named below as a plausible PAS for demo context only — not asserting real Dorset HealthCare schema, forms or warehouse objects.
Synthetic data lineage (Metric 1 — demonstration only)
End-to-end fictional lineage for Synthetic All Age Mental Health Access Contacts:
| Layer | Fictional artefact |
|---|---|
| Source system | RiO contact / appointment record (synthetic demo reference — not live Trust) |
| Source form / fields | DemoMHContactForm; fields ContactStatusCode, ContactDate, AppointmentType, TeamCode |
| Staging extract | StgMHSDSContactExtractDemo — nightly RiO → warehouse staging |
| Team mapping | DimMHSDSServiceTeamDemo — maps TeamCode to All Age MH inclusion flag (AllAgeMHFlag) |
| Fact table | FactMHSDSContactDemo — one row per contact event after ETL and deduplication |
| Filters | ContactStatusCode = 'ATT' (attended); ContactMonth = ReportingMonth; AllAgeMHFlag = 1 |
| Dedup logic | ROW_NUMBER() on (PersonID, ContactDate, TeamCode) keeping earliest attended row (synthetic rule) |
| Late recording flag | LateRecordedFlag = 1 where contact date falls in a prior month but record created in reporting month |
| Reporting output | Aggregate count → data/synthetic_mhsds_sme_demo.csv |
Lineage flow:
RiO contact / appointment record (DemoMHContactForm)
→ StgMHSDSContactExtractDemo
→ FactMHSDSContactDemo (join DimMHSDSServiceTeamDemo)
→ filter: attended + reporting month + All Age MH teams
→ aggregate count
→ synthetic_mhsds_sme_demo.csv
Metric 1: Synthetic All Age Mental Health Access Contacts
| Field | Definition |
|---|---|
| Metric name | Synthetic All Age Mental Health Access Contacts |
| Plain-English definition | Count of attended contacts recorded in the reporting month for selected all-age mental health access services in this synthetic demo. |
| Source system | RiO (synthetic demo reference) |
| Source form | DemoMHContactForm |
| Key fields | ContactStatusCode, ContactDate, AppointmentType, TeamCode, PersonID (synthetic demo field names) |
| Extract table | StgMHSDSContactExtractDemo |
| Fact table | FactMHSDSContactDemo |
| Mapping table | DimMHSDSServiceTeamDemo |
| Numerator | Attended contacts where ContactStatusCode = 'ATT' and ContactDate falls within the reporting month |
| Denominator | Not applicable (count metric) |
| Dedup rule | ROW_NUMBER() on (PersonID, ContactDate, TeamCode) — keep earliest attended row per synthetic demo rule |
| Included synthetic services | Demo Community MH Access Team; Demo All Age Crisis Resolution Team; Demo Primary Care Liaison MH Service (via AllAgeMHFlag = 1 in DimMHSDSServiceTeamDemo) |
| Excluded synthetic services | Inpatient wards; LD/autism specialist teams; IAPT/Talking Therapies (separate pathway in demo); admin-only contacts |
| Reporting period | Calendar month (e.g. 2026-02, 2026-03) |
| Known caveats | Synthetic PAS extract logic; late-recorded contacts may appear in a later month (LateRecordedFlag); duplicate contact rules not fully validated in demo |
| Owner / sign-off role | Synthetic demo: Information Lead (MHSDS accountability) and Performance Manager |
Pseudologic (synthetic demo):
SELECT COUNT(*)
FROM FactMHSDSContactDemo f
JOIN DimMHSDSServiceTeamDemo d ON f.TeamCode = d.TeamCode
WHERE f.ContactStatusCode = 'ATT'
AND f.ContactMonth = @ReportingMonth
AND d.AllAgeMHFlag = 1
Metric 2: Synthetic First Attended Contact Rate
| Field | Definition |
|---|---|
| Metric name | Synthetic First Attended Contact Rate |
| Plain-English definition | Proportion of referrals received in the reporting month where a first attended contact occurred in the same month (synthetic demo logic). |
| Numerator | First attended contacts in the reporting month (FirstContactFlag = 1 in FactMHSDSContactDemo) |
| Denominator | Referrals received in the reporting month (from synthetic referral extract — demo only) |
| Included synthetic services | Same as Metric 1 |
| Excluded synthetic services | Same as Metric 1; referrals closed before first contact with no attended contact are excluded from numerator but remain in denominator |
| Reporting period | Calendar month |
| Known caveats | Sensitive to referral timing within the month; not comparable to national MHSDS published measures without mapping validation |
| Owner / sign-off role | Synthetic demo: Information Lead and Performance Manager |
Metric 3: Synthetic Referral-to-First-Contact Median Wait
| Field | Definition |
|---|---|
| Metric name | Synthetic Referral-to-First-Contact Median Wait |
| Plain-English definition | Median number of days from referral date to first attended contact for referrals reaching first contact in the reporting month (synthetic demo logic). |
| Numerator | Days from referral to first attended contact (per referral reaching first contact in month) |
| Denominator | Count of referrals reaching first attended contact in the reporting month (for median calculation) |
| Included synthetic services | Same as Metric 1 |
| Excluded synthetic services | Same as Metric 1; referrals with no first attended contact in the month are excluded |
| Reporting period | Calendar month (based on first contact date) |
| Known caveats | Does not reflect full pathway wait for referrals still waiting; affected by late recording and DNA/rebook patterns |
| Owner / sign-off role | Synthetic demo: Information Lead and Performance Manager |
Demo data reference
Aggregate figures for these metrics are in data/synthetic_mhsds_sme_demo.csv.
Public MHSDS source references are in public-data/mhsds-source-pack-register.csv.