BI Lead Journey: Hospital Operations Command Center

{
  "source_type": "hl7v2_listener",
  "connection_name": "pinnacle_adt_feeds",
  "config": {
    "listen_port": 2575,
    "message_types": ["ADT^A01", "ADT^A02", "ADT^A03", "ADT^A04", "ADT^A06", "ADT^A07", "ADT^A08"],
    "facilities": [
      {"facility_id": "PDT", "name": "Pinnacle Downtown", "beds": 480},
      {"facility_id": "PEA", "name": "Pinnacle East", "beds": 320},
      {"facility_id": "PME", "name": "Pinnacle Memorial", "beds": 290},
      {"facility_id": "PCM", "name": "Pinnacle Community", "beds": 185}
    ],
    "parsing": {
      "patient_class": "PV1-2",
      "admit_date": "PV1-44",
      "discharge_date": "PV1-45",
      "unit": "PV1-3.1",
      "bed": "PV1-3.3",
      "attending_physician": "PV1-7"
    }
  },
  "destination": {
    "schema": "operations_realtime",
    "table": "adt_events"
  }
}

Department Naming Inconsistencies Across 12 Hospitals
======================================================

"Medical/Surgical" appears as:
  - Med/Surg        (4 hospitals)
  - Medical-Surgical (3 hospitals)
  - Med Surg         (2 hospitals)
  - MedSurg          (2 hospitals)
  - M/S              (1 hospital)

"Emergency Department" appears as:
  - Emergency Department  (5 hospitals)
  - Emergency Room        (3 hospitals)
  - ED                    (2 hospitals)
  - ER                    (2 hospitals)

Total unique department names found: 287
After standardization mapping:       64 standard departments

Resolution: Built standardization mapping in the catalog
  - 287 source names → 64 standard names
  - Mapping stored in reference.department_standardization
  - Applied automatically in the semantic layer

-- Profile bed census data for completeness and accuracy
SELECT
    facility_id,
    COUNT(*) AS total_events,
    COUNT(DISTINCT unit) AS units_reporting,
    SUM(CASE WHEN event_type = 'ADMIT' THEN 1 ELSE 0 END) AS admits,
    SUM(CASE WHEN event_type = 'DISCHARGE' THEN 1 ELSE 0 END) AS discharges,
    SUM(CASE WHEN event_type = 'TRANSFER' THEN 1 ELSE 0 END) AS transfers,
    SUM(CASE WHEN unit IS NULL THEN 1 ELSE 0 END) AS missing_unit,
    SUM(CASE WHEN bed IS NULL THEN 1 ELSE 0 END) AS missing_bed
FROM operations_realtime.adt_events
WHERE event_date = CURRENT_DATE
GROUP BY facility_id;

-- Semantic Layer: Core operational metrics
-- These definitions ensure consistent computation across all dashboards
 
-- Metric: Bed Occupancy Rate
-- CMS-aligned: occupied bed-days / available bed-days
CREATE VIEW semantic.bed_occupancy AS
SELECT
    facility_id,
    unit_standard_name,
    snapshot_hour,
    occupied_beds,
    licensed_beds,
    ROUND(100.0 * occupied_beds / NULLIF(licensed_beds, 0), 1) AS occupancy_pct,
    CASE
        WHEN occupied_beds / NULLIF(licensed_beds, 0) > 0.95 THEN 'CRITICAL'
        WHEN occupied_beds / NULLIF(licensed_beds, 0) > 0.88 THEN 'HIGH'
        WHEN occupied_beds / NULLIF(licensed_beds, 0) > 0.75 THEN 'NORMAL'
        ELSE 'LOW'
    END AS occupancy_status
FROM operations.hourly_bed_census;
 
-- Metric: ED Wait Time (Door-to-Provider)
-- CMS ED-1 measure alignment
CREATE VIEW semantic.ed_wait_time AS
SELECT
    facility_id,
    DATE_TRUNC('hour', arrival_time) AS hour,
    COUNT(*) AS ed_visits,
    ROUND(AVG(EXTRACT(EPOCH FROM (provider_time - arrival_time)) / 60.0), 0)
        AS avg_wait_minutes,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP
        (ORDER BY EXTRACT(EPOCH FROM (provider_time - arrival_time)) / 60.0), 0)
        AS median_wait_minutes,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP
        (ORDER BY EXTRACT(EPOCH FROM (provider_time - arrival_time)) / 60.0), 0)
        AS p90_wait_minutes,
    SUM(CASE WHEN EXTRACT(EPOCH FROM (provider_time - arrival_time)) / 60.0 > 240
        THEN 1 ELSE 0 END) AS over_4_hours
FROM operations.ed_tracking
GROUP BY facility_id, DATE_TRUNC('hour', arrival_time);
 
-- Metric: Staffing Ratio (Nurse-to-Patient)
CREATE VIEW semantic.staffing_ratio AS
SELECT
    s.facility_id,
    s.unit_standard_name,
    s.shift_date,
    s.shift_type,
    s.scheduled_rn_count,
    s.actual_rn_count,
    c.occupied_beds AS patient_count,
    ROUND(c.occupied_beds / NULLIF(s.actual_rn_count, 0), 1) AS patients_per_nurse,
    CASE
        WHEN s.unit_type = 'ICU' AND c.occupied_beds / NULLIF(s.actual_rn_count, 0) > 2.0
            THEN 'UNSAFE'
        WHEN s.unit_type = 'MED_SURG' AND c.occupied_beds / NULLIF(s.actual_rn_count, 0) > 6.0
            THEN 'UNSAFE'
        WHEN s.unit_type = 'ED' AND c.occupied_beds / NULLIF(s.actual_rn_count, 0) > 4.0
            THEN 'UNSAFE'
        ELSE 'SAFE'
    END AS staffing_status
FROM operations.staffing_actuals s
JOIN operations.shift_census c
    ON s.facility_id = c.facility_id
    AND s.unit_standard_name = c.unit_standard_name
    AND s.shift_date = c.shift_date
    AND s.shift_type = c.shift_type;

-- Validate platform-computed metrics against CMS-reported values
SELECT
    m.facility_id,
    m.measure_id,
    m.measure_name,
    m.cms_reported_value,
    m.platform_computed_value,
    ROUND(ABS(m.cms_reported_value - m.platform_computed_value), 2) AS absolute_diff,
    ROUND(100.0 * ABS(m.cms_reported_value - m.platform_computed_value)
        / NULLIF(m.cms_reported_value, 0), 2) AS pct_diff,
    CASE WHEN ABS(m.cms_reported_value - m.platform_computed_value)
        / NULLIF(m.cms_reported_value, 0) <= 0.01 THEN 'PASS'
        ELSE 'REVIEW'
    END AS validation_status
FROM quality.metric_validation m
WHERE m.reporting_period = '2025-Q3';

{
  "pipeline_name": "operations_command_center",
  "schedule": "0 * * * *",
  "description": "Hourly operational data refresh for command center dashboards",
  "steps": [
    {
      "step_id": "compute_hourly_census",
      "type": "sql_transform",
      "query_ref": "sql/hourly_bed_census.sql",
      "destination": "operations.hourly_bed_census",
      "write_mode": "upsert",
      "upsert_keys": ["facility_id", "unit_standard_name", "snapshot_hour"]
    },
    {
      "step_id": "compute_ed_metrics",
      "type": "sql_transform",
      "query_ref": "sql/ed_flow_metrics.sql",
      "destination": "operations.ed_flow_hourly",
      "write_mode": "upsert",
      "upsert_keys": ["facility_id", "hour"]
    },
    {
      "step_id": "compute_staffing_metrics",
      "type": "sql_transform",
      "depends_on": ["compute_hourly_census"],
      "query_ref": "sql/staffing_ratios.sql",
      "destination": "operations.staffing_metrics",
      "write_mode": "upsert",
      "upsert_keys": ["facility_id", "unit_standard_name", "shift_date", "shift_type"]
    },
    {
      "step_id": "quality_checks",
      "type": "data_quality",
      "depends_on": ["compute_hourly_census", "compute_ed_metrics", "compute_staffing_metrics"],
      "suite": "operations_quality",
      "checks": [
        { "type": "freshness", "table": "hourly_bed_census", "max_age_minutes": 90 },
        { "type": "range", "column": "occupancy_pct", "min": 0, "max": 100 },
        { "type": "range", "column": "patients_per_nurse", "min": 0, "max": 20 },
        { "type": "completeness", "table": "ed_flow_hourly", "min_facilities": 12 }
      ],
      "on_failure": "alert_and_continue"
    },
    {
      "step_id": "midnight_census_snapshot",
      "type": "sql_transform",
      "condition": "HOUR(NOW()) = 0",
      "query_ref": "sql/midnight_census_snapshot.sql",
      "destination": "operations.daily_midnight_census",
      "write_mode": "append",
      "description": "Official midnight census for regulatory reporting"
    },
    {
      "step_id": "refresh_dashboard_cache",
      "type": "cache_invalidation",
      "depends_on": ["quality_checks"],
      "dashboards": [
        "ops_realtime_census",
        "ops_ed_flow",
        "ops_staffing",
        "ops_quality_summary"
      ]
    }
  ]
}

-- Midnight census snapshot -- official daily count for CMS reporting
-- Must run at exactly 00:00 local time for each facility's timezone
INSERT INTO operations.daily_midnight_census
SELECT
    facility_id,
    CURRENT_DATE AS census_date,
    unit_standard_name,
    service_line,
    COUNT(*) AS patient_count,
    SUM(CASE WHEN patient_class = 'OBS' THEN 1 ELSE 0 END) AS observation_count,
    SUM(CASE WHEN patient_class = 'INP' THEN 1 ELSE 0 END) AS inpatient_count,
    SUM(CASE WHEN los_days > 14 THEN 1 ELSE 0 END) AS long_stay_count,
    SUM(CASE WHEN isolation_flag = TRUE THEN 1 ELSE 0 END) AS isolation_count
FROM operations.current_census
WHERE snapshot_time = DATE_TRUNC('day', CURRENT_TIMESTAMP)
GROUP BY facility_id, unit_standard_name, service_line;

-- Identify seasonal patterns for capacity planning
SELECT
    EXTRACT(MONTH FROM census_date) AS month,
    facility_id,
    ROUND(AVG(occupancy_pct), 1) AS avg_occupancy,
    ROUND(MAX(occupancy_pct), 1) AS peak_occupancy,
    COUNT(CASE WHEN occupancy_pct > 95 THEN 1 END) AS crisis_days,
    ROUND(AVG(ed_boarding_hours), 1) AS avg_boarding_hours
FROM operations.daily_summary
WHERE census_date >= CURRENT_DATE - INTERVAL '2' YEAR
GROUP BY EXTRACT(MONTH FROM census_date), facility_id
ORDER BY month;

Operations Command Center -- Dashboard Suite
==============================================

  ┌─────────────────────────────────────────────────────────────────┐
  │                    WALL-MOUNTED DISPLAYS                       │
  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────┐  │
  │  │ Real-Time    │  │  ED Flow     │  │  System Overview     │  │
  │  │ Bed Census   │  │  Board       │  │  (12-hospital map)   │  │
  │  │              │  │              │  │                      │  │
  │  │ By unit,     │  │ Arrivals,    │  │ Occupancy heat map,  │  │
  │  │ bed-level    │  │ wait times,  │  │ diversion status,    │  │
  │  │ status       │  │ boarding     │  │ key alerts           │  │
  │  └──────────────┘  └──────────────┘  └──────────────────────┘  │
  │                                                                 │
  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────┐  │
  │  │ OR Schedule  │  │  Staffing    │  │  Quality Metrics     │  │
  │  │ & Utilization│  │  Dashboard   │  │  Scorecard           │  │
  │  │              │  │              │  │                      │  │
  │  │ Room status, │  │ RN ratios,   │  │ Readmissions,        │  │
  │  │ case times,  │  │ agency use,  │  │ infections,          │  │
  │  │ turnover     │  │ overtime     │  │ mortality, falls     │  │
  │  └──────────────┘  └──────────────┘  └──────────────────────┘  │
  └─────────────────────────────────────────────────────────────────┘

  ┌─────────────────────────────────────────────────────────────────┐
  │                    EXECUTIVE DASHBOARDS                        │
  │  ┌──────────────────────────┐  ┌────────────────────────────┐  │
  │  │ Financial Performance   │  │  CMS Star Rating Tracker   │  │
  │  │                         │  │                            │  │
  │  │ Revenue, expenses,      │  │  Current rating, projected │  │
  │  │ margin by service line, │  │  rating, improvement areas │  │
  │  │ budget variance         │  │                            │  │
  │  └──────────────────────────┘  └────────────────────────────┘  │
  └─────────────────────────────────────────────────────────────────┘

{
  "dashboard_suite": "pinnacle_ops_command_center",
  "dashboards": [
    {
      "id": "ops_realtime_census",
      "title": "Real-Time Bed Census",
      "refresh_interval_seconds": 300,
      "display_mode": "wall_mounted",
      "panels": [
        {
          "title": "System Occupancy",
          "type": "gauge",
          "metric": "semantic.bed_occupancy",
          "aggregation": "system_average",
          "thresholds": { "green": 75, "yellow": 88, "red": 95 }
        },
        {
          "title": "Occupancy by Facility",
          "type": "heatmap",
          "metric": "semantic.bed_occupancy",
          "group_by": "facility_id",
          "color_scale": "green_yellow_red"
        },
        {
          "title": "Available Beds by Unit Type",
          "type": "stacked_bar",
          "metric": "semantic.bed_availability",
          "group_by": ["facility_id", "unit_type"]
        }
      ]
    },
    {
      "id": "ops_ed_flow",
      "title": "Emergency Department Flow",
      "refresh_interval_seconds": 300,
      "panels": [
        {
          "title": "Door-to-Provider Time",
          "type": "line_chart",
          "metric": "semantic.ed_wait_time",
          "fields": ["median_wait_minutes", "p90_wait_minutes"],
          "threshold_line": 240
        },
        {
          "title": "ED Boarding Count",
          "type": "big_number",
          "metric": "SELECT COUNT(*) FROM operations.ed_tracking WHERE status = 'BOARDING'",
          "alert_threshold": 15
        },
        {
          "title": "Patients by Acuity (ESI)",
          "type": "donut_chart",
          "metric": "semantic.ed_acuity_distribution"
        }
      ]
    },
    {
      "id": "ops_staffing",
      "title": "Staffing Dashboard",
      "refresh_interval_seconds": 900,
      "panels": [
        {
          "title": "Nurse-to-Patient Ratios",
          "type": "table",
          "metric": "semantic.staffing_ratio",
          "highlight_rule": "staffing_status = 'UNSAFE'",
          "highlight_color": "red"
        },
        {
          "title": "Agency Staff Usage",
          "type": "bar_chart",
          "metric": "semantic.agency_utilization",
          "group_by": "facility_id"
        }
      ]
    }
  ]
}

{
  "alert_suite": "ops_command_center_alerts",
  "alerts": [
    {
      "name": "ed_wait_critical",
      "description": "ED wait time exceeds 4 hours",
      "condition": "semantic.ed_wait_time.p90_wait_minutes > 240",
      "check_interval_minutes": 15,
      "severity": "critical",
      "channels": ["ops_center_display", "sms"],
      "recipients": ["ed_director", "cno", "ops_center"],
      "escalation": {
        "if_not_acknowledged_minutes": 30,
        "escalate_to": ["cmo", "coo"]
      }
    },
    {
      "name": "bed_occupancy_critical",
      "description": "Facility bed occupancy exceeds 95%",
      "condition": "semantic.bed_occupancy.occupancy_pct > 95",
      "check_interval_minutes": 15,
      "severity": "critical",
      "channels": ["ops_center_display", "sms", "email"],
      "recipients": ["bed_management", "cno", "ops_center"],
      "auto_action": "trigger_surge_protocol_review"
    },
    {
      "name": "staffing_unsafe",
      "description": "Nurse-to-patient ratio exceeds safe threshold",
      "condition": "semantic.staffing_ratio.staffing_status = 'UNSAFE'",
      "check_interval_minutes": 30,
      "severity": "high",
      "channels": ["email", "ehr_inbox"],
      "recipients": ["unit_manager", "staffing_office", "cno"]
    },
    {
      "name": "data_freshness",
      "description": "Operations data older than 90 minutes",
      "condition": "operations.hourly_bed_census.max_snapshot_age_minutes > 90",
      "check_interval_minutes": 30,
      "severity": "medium",
      "channels": ["email"],
      "recipients": ["aisha.williams@pinnaclehealth.org", "data_ops"]
    }
  ]
}

Operations Data Quality Report -- November 15, 2025
=====================================================

Data Source Freshness:
  ADT Feeds (Epic):       2 min old     OK
  ADT Feeds (Cerner):     4 min old     OK
  Staffing (Kronos):      12 min old    OK
  ED Tracking:            3 min old     OK
  Financial (Workday):    18 hours old  OK (daily sync)

Data Completeness:
  Facilities reporting:   12/12         OK
  Units with census data: 186/192       WARNING (6 units missing)
  Staffing data complete: 11/12         WARNING (Pinnacle Rural delayed)

Metric Accuracy (vs manual audit):
  Bed census:             99.4% match   OK
  ED wait time:           98.8% match   OK
  Staffing ratios:        97.2% match   OK (rounding differences)

Action Items:
  1. Investigate 6 units missing census data (Pinnacle Memorial OB units)
  2. Follow up on Pinnacle Rural staffing data delay
  3. Schedule quarterly metric accuracy audit for Q1 2026

Predictive Bed Management -- Pilot Results
============================================

Pilot sites: Pinnacle Downtown, Pinnacle East, Pinnacle Memorial
Period: October 2025 (4 weeks)
Model: 24-hour census forecast using admission patterns, OR schedule,
       and historical discharge timing

Accuracy:
  4-hour forecast:   +/- 3 beds (95% CI)
  12-hour forecast:  +/- 7 beds (95% CI)
  24-hour forecast:  +/- 12 beds (95% CI)

Operational Impact (pilot vs control hospitals):
  ED boarding time:    3.1 hours (pilot) vs 4.4 hours (control)    -30%
  Diversion hours:     8 hours/month (pilot) vs 22 hours (control)  -64%
  Bed turnaround time: 42 min (pilot) vs 58 min (control)          -28%

Decision: Expand to all 12 hospitals in Q1 2026

Self-Service Analytics Pilot -- 8 Department Managers
======================================================

Access provided: Filtered views of command center dashboards
  - Each manager sees only their department's data
  - Pre-built drill-down paths (occupancy → unit → patient class)
  - Natural language query for ad-hoc questions

Adoption (4-week pilot):
  Managers with daily login:         6/8  (75%)
  Ad-hoc queries submitted:         142
  Reports previously requested:     34 (now self-served)
  Time saved (estimated):           18 analyst-hours/week

Top self-service queries:
  1. "Show me overtime hours by unit for last 2 weeks"
  2. "Compare my unit's length of stay to system average"
  3. "Which days had the highest admits this month?"
  4. "What is my patient satisfaction trend vs last quarter?"

Decision: Roll out to all department managers with training program

Command Center Impact on ED Boarding -- 6-Month Assessment
============================================================

                        Pre-Dashboard    Post-Dashboard    Change
Mean boarding time:       4.2 hours        3.1 hours       -26%
Median boarding time:     3.4 hours        2.6 hours       -24%
P90 boarding time:        8.1 hours        5.8 hours       -28%
Diversion hours/month:    28 hours         14 hours        -50%
Patient complaints (ED):  142/month        98/month        -31%

Contributing factors:
  1. Real-time visibility:  Bed management sees openings immediately
  2. Proactive planning:    Predicted discharges enable pre-assignment
  3. Accountability:        Visible metrics drive faster turnaround
  4. Alert escalation:      Critical thresholds trigger immediate action