Analytics Dashboard Blueprint

Core diversion KPIs, formulas, benchmarks, sample SQL, and a suggested layout for Power BI or Tableau.

BLUEPRINT
Dashboard Design Principles
1
Exception-First Design

Don't show all data — show only the outliers, anomalies, and exceptions that need attention. Red/yellow/green thresholds on every metric.

2
Drill-Down Hierarchy

System → Unit → Practitioner → Transaction. Start with the organizational view, click down to investigate. Don't bury the user in transaction-level data.

3
Trend Over Snapshot

A single data point is noise. Show 4-week and 12-week trends so you can see developing patterns before they become critical.

4
Actionable, Not Informational

Every metric should answer one question: "Who needs investigation?" or "What needs correction?" If a metric doesn't drive action, remove it.

KPI 1
Override Rate
Formula

(Override removals ÷ Total removals) × 100

Calculated per practitioner, per unit, per medication class

Benchmark

< 10% = Good

10-20% = Monitor

> 20% = Investigate

What It Catches
  • Routine overrides bypassing pharmacist verification
  • Nurses who consistently avoid the audit trail
  • Override clustering on night shifts
  • Admin access transactions (no patient encounter)
Review Frequency

Weekly for exceptions, monthly for trends

Visualization

Bar chart — practitioner override rate with unit average reference line. Color-code by threshold.

Sample SQL Override rate by practitioner — rolling 30 days 
SELECT
    staff_name,
    unit_name,
    SUM(CASE WHEN access_mode = 'override' THEN 1 ELSE 0 END) AS override_count,
    COUNT(*) AS total_removals,
    ROUND(100.0 * SUM(CASE WHEN access_mode = 'override'
        THEN 1 ELSE 0 END) / COUNT(*), 1) AS override_pct,
    SUM(CASE WHEN access_mode = 'admin' THEN 1 ELSE 0 END) AS admin_access_count
FROM cabinet_transaction_log
WHERE transaction_date >= DATEADD(day, -30, GETDATE())
GROUP BY staff_name, unit_name
HAVING COUNT(*) > 10
ORDER BY override_pct DESC;
KPI 2
Waste Rate
Formula

(Waste events ÷ Total administrations) × 100

Calculated per practitioner, per drug, per shift

Benchmark

< 15% of administrations = Normal

15-25% = Monitor

> 25% or > 2 SD above unit mean = Investigate

What It Catches
  • Falsified waste — charting waste that wasn't actually wasted
  • Partial administration with the rest diverted
  • Waste documentation as cover for pocketing
  • Unwitnessed waste (missing witness field)
Review Frequency

Weekly

Visualization

Scatter plot — waste rate vs. admin volume (detects high-volume + high-waste practitioners). Heat map of waste by shift × unit.

SELECT
    staff_name,
    unit_name,
    COUNT(DISTINCT encounter_id) AS encounters,
    SUM(waste_volume_units) AS total_waste_units,
    SUM(administered_units) AS total_administered,
    ROUND(100.0 * SUM(waste_volume_units) /
        NULLIF(SUM(administered_units), 0), 1) AS waste_pct,
    COUNT(CASE WHEN witness_name IS NULL THEN 1 END) AS unwitnessed_waste
FROM mar_admin_records
WHERE admin_date >= DATEADD(day, -30, GETDATE())
GROUP BY staff_name, unit_name
ORDER BY waste_pct DESC;
KPI 3
Dispense vs. Administration Gap
Formula

Total dispensed − (Total administered + Total wasted)

Calculated per drug, per patient encounter, per practitioner

Benchmark

< 2 units (per drug/patient) = Normal

2-5 units = Review

> 5 units = Investigate

What It Catches
  • Drug removed from ADC but not given to patient
  • Unexplained gap between pharmacy and bedside
  • Non-controlled drug diversion (no CS audit trail)
  • PCA discrepancies (pump volume vs. charted volume)
Review Frequency

Daily for C-II, weekly for all CS

Visualization

Table — highest gap practitioners with drill-down to patient/encounter level. Trend line of total organizational gap over time.

SELECT
    pa.staff_name,
    pa.unit_name,
    pa.medication_name,
    SUM(pa.dispensed_units) - (SUM(pa.administered_units) + SUM(COALESCE(pa.waste_units, 0)))
        AS gap_units,
    COUNT(DISTINCT pa.encounter_id) AS encounters_affected
FROM mar_administration pa
WHERE pa.admin_date >= DATEADD(day, -30, GETDATE())
GROUP BY pa.staff_name, pa.unit_name, pa.medication_name
HAVING SUM(pa.dispensed_units) - (SUM(pa.administered_units)
    + SUM(COALESCE(pa.waste_units, 0))) > 5
ORDER BY gap_units DESC;
KPI 4
Waste Documentation Lag Time
Formula

Waste documentation timestamp − Administration timestamp

Measured in minutes. Calculated per waste event.

Benchmark

< 15 min = Compliant

15-60 min = At risk

> 60 min = Policy violation

What It Catches
  • Waste documented well after the event (batch documentation)
  • Falsified waste — documenting waste that didn't occur
  • Waste documented at end of shift (memory risk)
  • Waste documented without witness (added later)
Review Frequency

Weekly (by practitioner), monthly (by unit)

Visualization

Box plot — lag time distribution by practitioner. Heat map of lag incidents by shift × unit.

SELECT
    staff_name,
    unit_name,
    COUNT(*) AS waste_events,
    AVG(DATEDIFF(minute, admin_time, waste_doc_time)) AS avg_lag_minutes,
    MAX(DATEDIFF(minute, admin_time, waste_doc_time)) AS max_lag_minutes,
    SUM(CASE WHEN DATEDIFF(minute, admin_time, waste_doc_time) > 60
        THEN 1 ELSE 0 END) AS late_documentation_count
FROM waste_documentation_log
WHERE admin_date >= DATEADD(day, -30, GETDATE())
GROUP BY staff_name, unit_name
ORDER BY avg_lag_minutes DESC;
KPI 5
Administrative Access Events
Formula

Count of transactions where access_mode = 'admin' (return-to-stock, inventory adjust, override-all, manager override)

Benchmark

0-2/week for most staff = Normal

3-5/week = Monitor

> 5/week = Investigate

What It Catches
  • Admin-level removals with no patient encounter
  • Return-to-stock transactions where drug wasn't actually returned
  • Inventory adjustments to cover missing drugs
  • Staff with excessive admin privileges
Review Frequency

Weekly — must be reviewed, not just reported

Visualization

Table — admin events by practitioner with drug, date, and type. Trend chart of weekly admin events system-wide.

SELECT
    staff_name,
    unit_name,
    access_mode,
    COUNT(*) AS event_count,
    COUNT(DISTINCT medication_name) AS drugs_involved,
    COUNT(DISTINCT CONVERT(date, transaction_date)) AS days_with_events
FROM cabinet_transaction_log
WHERE access_mode IN ('admin', 'return_to_stock', 'inventory_adjust', 'manager_override')
    AND transaction_date >= DATEADD(day, -30, GETDATE())
GROUP BY staff_name, unit_name, access_mode
ORDER BY event_count DESC;
LAYOUT
Suggested Power BI / Tableau Layout
Header Row — Summary Cards

Total Discrepancies (24h) | Open Investigations | DEA 106s Filed This Month | High-Risk Practitioners (Drill-Down Button)

Left Panel — Exceptions

Override Rate (top 10 outliers) | Waste Rate (top 10 outliers) | Dispense-Admin Gap (top 10 outliers) — all with sparkline trends

Right Panel — Trends

Override rate trend (12 weeks) | Waste rate trend (12 weeks) | Admin access events trend (12 weeks) — line charts with threshold bands

Bottom Panel — Drill-Down Detail

Clicking any practitioner opens: transaction history (30 days), waste events by drug, override events by shift, admin access events, trend chart, and a link to camera footage export. All on one page.

Data Refresh Cadence:

ADC transaction data: hourly. Camera system data: on-demand. Waste documentation: near-real-time. The dashboard should auto-refresh every hour during business hours. Some metrics (trends, benchmarks) can be computed daily.

SCHEDULE
Review Frequency by Metric
Frequency Metrics Reviewer
Daily C-II perpetual inventory, dispense-admin gap (automated alerts), new DEA 106 filings, critical ADC discrepancies Pharmacy technician / Pharmacist
Weekly Override rate (top outliers), waste rate (top outliers), admin access events, waste lag time, all CS discrepancy log Diversion Prevention Officer / Committee
Monthly All metrics with trend analysis, practitioner ranking, unit-level comparisons, corrective action status Diversion Prevention Committee
Quarterly Program effectiveness review, benchmark validation, audit log review (camera access, admin privilege list), policy updates Executive leadership + Committee