Secondment demo — data dictionary
Synthetic demonstration data only. Demo Rivers Health (DRH) — not Dorset HealthCare (RDY).
Field-level definitions for migration scenario artefacts. See README for timeline and regeneration.
Definition options (referral count)
| ID | Rule (plain English) |
|---|---|
| OPT-A | Count Legendary Care cases where ReferralDate falls in the reporting month |
| OPT-B | Count PathwayOne ACCESS_ACCEPTED actions in the reporting month |
| OPT-C | Agreed hybrid: pre-Feb-2026 use old case logic; from Feb-2026 use action rules with admin/duplicate exclusions and rejection-reason handling |
| DASH | Legacy dashboard pipeline — blends feeds without full reconciliation (Mar 2026 shows +2% vs agreed) |
legendary_cmht_cases.csv
| Field | Type | Description | Example | DQ notes | Service question |
|---|---|---|---|---|---|
| CaseId | string | Legacy case identifier | Case LC-10001 | ||
| PatientPseudoId | string | Pseudonymised patient | DRH-PAT-002896 | ||
| ReferralDate | date | Date referral received | 2025-12-08 | When referred? | |
| PathwayStatus | string | Case status | Rejected | Pathway state | |
| PathwayStartDate | date | Pathway start on case | 2025-12-08 | May differ from first contact | When did pathway start? |
| FirstContactDate | date | First clinical contact | 2026-01-10 | Nullable | Contacts / waiting |
| ClosureDate | date | Case closure | 2026-03-20 | Nullable | Has pathway closed? |
| ResponsibleTeam | string | Team code (old or new) | CMHT-N | Remap issues | Which team owns it? |
| ReportingMonth | string | Month for performance reporting | 2026-01 | Ambiguity across systems | Which month to count? |
| OutcomeText | string | Outcome / rejection reason | Wrong service | Outcome | |
| ContactCount | int | Contacts on case | 2 | Activity inclusion | |
| SourceSystem | string | Always LegendaryCare | LegendaryCare |
pathwayone_actions.csv
| Field | Type | Description | Example | DQ notes | Service question |
|---|---|---|---|---|---|
| ActionId | string | Action event ID | ACT-90001 | Duplicates possible | |
| PatientPseudoId | string | Patient link | DRH-PAT-002896 | ||
| ActionType | string | Access/Care/Outcome/Admin | ACCESS_REJECTED | Pathway mapping | |
| ActionDate | date | Event date | 2025-12-08 | Referral / activity timing | |
| RejectionReason | string | If rejected | CLINICAL_TRIAGE | Affects inclusion | Rejected = received? |
| TeamCode | string | Team on action | AA-MH-NORTH-01 | INVALID_TEAM possible | Team ownership |
| Notes | string | DQ / scenario notes | Admin inflate | ||
| IncludeInReferralCount_NewDefault | int | 1=include in default new count | 0 | National vs local rules |
reconciliation_monthly.csv
| Field | Description |
|---|---|
| reporting_month | YYYY-MM |
| old_case_count | OPT-A style count |
| new_action_count_default | OPT-B style count |
| agreed_definition_count | OPT-C agreed count |
| dashboard_displayed_count | What the dashboard shows |
| dashboard_vs_agreed_pct | % difference (Mar 2026 = +14.9% ≈ +2% narrative on dashboard side) |
feed_cutover_log.csv
Documents parallel run (Dec–Jan) vs referral feed cutover (Feb) vs legacy extract end (Mar).
Other files
See data_manifest.csv for grain, row counts and linked HTML pages.
Handover deliverable datasets (new / expanded)
| File | Purpose | Linked page |
|---|---|---|
kpi_definitions_register.csv |
Layered KPI definition chains (service → sign-off) | secondment/deliverables/kpi-definitions-register.html |
source_to_report_map.csv |
Report-first field lineage (report_name, report_field filterable) | secondment/deliverables/source-to-report-map.html |
source_to_report_map_mhsds_view.csv |
MHSDS-like rows filtered from map | secondment/reports/mhsds-like-submission.html |
source_to_report_map_local_demand_view.csv |
Local demand pack rows filtered from map | secondment/reports/local-demand-capacity-pack.html |
fact_mh_referral_episode.csv |
Central episode fact — feeds mandatory and local products | secondment/reports/mhsds-like-submission.html |
mhsds_like_submission_monthly.csv |
Submission header (provider, month, sign-off) | secondment/reports/mhsds-like-submission.html |
mhsds_like_submission_fields.csv |
One row per submission field (MHS23, MHS01, MHS29, DRH-REF-*) | secondment/reports/mhsds-like-submission.html |
mhsds_like_validation_checks.csv |
VAL-MH-* pre-submission checks | secondment/reports/mhsds-like-submission.html |
mhsds_like_reconciliation_summary.csv |
Referral count build-up 151/168/134/154 | secondment/reports/mhsds-like-submission.html |
definition_decision_log.csv |
OPT-A/B/C and rejection/wait-clock decisions | secondment/definition-migration.html |
local_demand_access_pack_metrics.csv |
Local pack metrics from same fact | secondment/reports/local-demand-capacity-pack.html |
reporting_requirements_map.csv |
Stakeholder questions and KPI refs | secondment/deliverables/source-to-report-map.html |
report_catalogue.csv |
Report names, owners, confidence | secondment/deliverables/source-to-report-map.html |
validation_checks_register.csv |
Pre-publish validation rules and Mar 2026 results | secondment/deliverables/reporting-assurance-during-migration.html |
reconciliation_detail_mar2026.csv |
Team/vignette build-up of 154 vs 134 | secondment/deliverables/reporting-assurance-during-migration.html |
migration_risk_register.csv |
Reporting meaning risks during migration | secondment/deliverables/reporting-assurance-during-migration.html |
reporting_confidence_register.csv |
Confidence levels and publish decisions | secondment/deliverables/reporting-assurance-during-migration.html |
demand_capacity_weekly.csv |
Weekly demand, capacity, backlog and wait metrics | secondment/deliverables/demand-capacity-productivity.html |
demand_capacity_insights.csv |
Analysis insights with status and suggested actions | secondment/deliverables/demand-capacity-productivity.html |
ideas_under_test_register.csv |
Ideas tested, promoted, parked or dismissed | secondment/deliverables/ideas-under-test.html |
productivity_by_team.csv |
Contacts per WTE by team and week | secondment/deliverables/demand-capacity-productivity.html |
improvement_benefits_tracker.csv |
Benefits linked to six-month outcomes | secondment/deliverables/index.html |
handover_documentation_register.csv |
Document register for M6 handover | secondment/deliverables/index.html |
change_playbook.csv |
Ten-step reusable migration playbook | six-months-trusted-performance.html#reusable-approach |
senior_brief_sections.csv |
Brief template sections with Mar 2026 text | secondment/deliverables/senior-performance-brief.html |
kpi_definitions_register.csv (layered columns)
| Field | Description |
|---|---|
| service_definition | What the measure means in the real service |
| system_capture | What staff do and what the source system records |
| fact_table_definition | Warehouse fact / derivation logic |
| reporting_use | Mandatory vs local reporting use |
| exclusions_caveats | Inclusion/exclusion rules and migration caveats |
| validation_check | How we know the chain is still intact |
| owners_sign_off | Operational, BI, performance and final sign-off roles |
source_to_report_map.csv (canonical map — one map, many views)
Single source of truth for report-first and source-first lineage. The interactive page at secondment/source-to-report.html filters this CSV client-side; filtered exports (source_to_report_map_mhsds_view.csv, etc.) are convenience downloads only.
| Field | Description |
|---|---|
| map_row_id | STR-001 etc. — stable row identifier |
| report_name | Filter key — e.g. MHSDS-like monthly submission |
| report_type | Mandatory / Local / Assurance / Source impact |
| report_section | Section or table within the report |
| report_field | Output field on the report |
| metric_code | MHS23, MHS01, DRH-REF-OPT-C, KPI-01, etc. |
| kpi_id | KPI-01 when linked to KPI register; else blank |
| service_event | Real-world service event |
| warehouse_fact_or_dimension | Primary fact — usually fact_mh_referral_episode |
| inclusion_rule / exclusion_rule | National vs local counting rules |
| national_or_local_use | National / Local / Both / Assurance |
| related_output_file | CSV or report artefact this row feeds |
| notes | Vignettes, reconciliation notes, preset hints |
| reporting_confidence | High / Medium / Low for Mar 2026 |
Filtered exports (not separate truth): source_to_report_map_mhsds_view.csv, source_to_report_map_local_demand_view.csv.
fact_mh_referral_episode.csv
Central episode grain derived from legendary_cmht_cases + pathwayone_actions. Key fields:
| Field | Description |
|---|---|
| episode_id | Synthetic episode identifier |
| derivation_rule | OPT-C, OPT-A, DASH, etc. |
| mhsds_like_inclusion_flag | 1 = include in MHSDS-like submission roll-up |
| local_reporting_inclusion_flag | 1 = include in local demand pack |
| first_contact_wait_days | Wait clock — definition-sensitive (see DEC-03) |
| reporting_month | YYYY-MM for monthly roll-ups |
Mar 2026 referrals received (agreed OPT-C) = 134; dashboard logic = 154 (reconciliation_monthly / mhsds_like_reconciliation_summary).
reporting_requirements_map.csv
| Field | Description |
|---|---|
| requirement_id | REQ-01 etc. |
| stakeholder | Who needs the answer |
| question | Business question |
| mandatory_or_local | Mandatory / Local |
| kpi_ref | KPI-01 etc. |
| report_ref | Report catalogue ID |
report_catalogue.csv
| Field | Description |
|---|---|
| report_id | CAT-01 etc. |
| report_name | Report title |
| audience | SLT, NHSE, service etc. |
| frequency | Monthly, weekly |
| owner | Role title |
| requirement_ids | Linked REQ IDs |
| confidence_mar2026 | High / Medium / Low |
| data_sources | Primary feeds |
validation_checks_register.csv
| Field | Description |
|---|---|
| check_id | VAL-01 etc. |
| rule | Validation rule |
| source | Table or pipeline |
| expected | Expected outcome |
| mar2026_result | Actual result |
| pass_fail | Pass / Fail |
| action | Follow-up |
reconciliation_detail_mar2026.csv
| Field | Description |
|---|---|
| level | Trust total or team/vignette |
| old_case_count | OPT-A |
| new_action_count | OPT-B default |
| agreed_count | OPT-C |
| dashboard_count | Dashboard displayed |
| notes | e.g. duplicate ActionId |
productivity_by_team.csv
| Field | Description |
|---|---|
| team_code | AA-MH-NORTH-01 etc. |
| week | W10, W21 |
| available_hours | WTE availability |
| contacts_delivered | Care contacts |
| contacts_per_wte | Productivity metric |
improvement_benefits_tracker.csv
| Field | Description |
|---|---|
| benefit_id | BEN-01 etc. |
| benefit | Benefit description |
| baseline | Starting point |
| target | Target state |
| status | Green / Amber / Red |
| owner | Role title |
| evidence_link | Deliverable or report |
| month | M1–M6 |
handover_documentation_register.csv
| Field | Description |
|---|---|
| doc_id | DOC-01 etc. |
| document_name | Artefact name |
| purpose | Why it exists |
| owner | Role title |
| location | Path or URL |
| handover_status | Draft / Ready |
change_playbook.csv
| Field | Description |
|---|---|
| step | 1–10 |
| phase | Listen, map, define etc. |
| activity | What to do |
| artefact_produced | Deliverable produced |
| reuse_note | How to reuse |
senior_brief_sections.csv
| Field | Description |
|---|---|
| section | Bottom line, headlines etc. |
| content_template | Template wording |
| mar2026_example | Filled example |
| confidence | High / Medium / Low |