The final Tableau workbook was designed to move from broad performance monitoring to progressively more detailed quality-improvement investigation.
The four dashboards follow this sequence:
Overview
→ Prevention Drivers
→ Case and Surveillance Context
→ Quality Improvement Summary and Next Steps
Rather than connecting Tableau directly to the raw synthetic source tables, I created purpose-built analytical tables in BigQuery first. This kept the surveillance classification, denominator logic, process measures, and QA decisions in SQL and allowed Tableau to focus on visualization and interaction.
The packaged Tableau workbook uses analytical extracts created from the SQL workflow, including:
tableau_quality_fact_final
analysis_unit_summary
analysis_bundle_elements
analysis_device_duration_summary
analysis_device_cohort
analysis_candidate_detail
analysis_surveillance_review_summary
Each table has a specific purpose rather than forcing one highly duplicated table to support every view.
Three parameters control the dashboard experience.
Values:
CAUTI
CLABSI
This parameter determines whether the dashboard displays Foley/CAUTI measures or central-line/CLABSI measures.
Values:
All
ICU
Med-Surg
Telemetry
Default: All
This allows the user to move between hospital-wide and unit-specific views.
Values:
Diabetes
Immunosuppression
Age Group
BMI Category
This parameter is used on the third dashboard to change the patient characteristic displayed in the candidate-rate comparison without requiring four separate charts.
IF SUM([device_days]) = 0 THEN NULL
ELSE
1000 * SUM([candidate_infections]) / SUM([device_days])
END
This calculation recomputes the rate from the aggregated numerator and denominator instead of averaging pre-calculated rates.
IF SUM([total_audits]) = 0 THEN NULL
ELSE
100 * SUM([compliant_audits]) / SUM([total_audits])
END
No sampled audits results in NULL rather than 0% compliance.
SUM([total_audits]) - SUM([compliant_audits])
[infection_type] = [Select Infection Type]
[Select Unit] = "All"
OR [unit] = [Select Unit]
IF [Select Infection Type] = "CLABSI"
THEN "Central Line"
ELSE "Foley Catheter"
END
The trend dashboard compares the monthly rate with a three-month rolling rate.
IF INDEX() >= 3 THEN
1000 *
WINDOW_SUM(
SUM([candidate_infections]),
-2,
0
)
/
WINDOW_SUM(
SUM([device_days]),
-2,
0
)
END
The rolling numerator and denominator were also available as supporting calculated fields:
IF INDEX() >= 3 THEN
WINDOW_SUM(
SUM([candidate_infections]),
-2,
0
)
END
IF INDEX() >= 3 THEN
WINDOW_SUM(
SUM([device_days]),
-2,
0
)
END
The rate therefore represents candidate events and device exposure across the current month and previous two months. It does not average three monthly rates.
100 *
AVG(
IF [is_compliant] THEN 1
ELSE 0
END
)
SUM(
IF [is_compliant] THEN 1
ELSE 0
END
)
SUM(
IF NOT [is_compliant] THEN 1
ELSE 0
END
)
The SQL cohort places device episodes into:
1–2 Days
3–4 Days
5–7 Days
8+ Days
SUM(
IF [duration_band] = "5-7 Days"
OR [duration_band] = "8+ Days"
THEN [device_days]
ELSE 0
END
)
100 *
SUM(
IF [duration_band] = "5-7 Days"
OR [duration_band] = "8+ Days"
THEN [device_days]
ELSE 0
END
)
/
SUM([device_days])
IF
SUM(
IF [duration_band] = "5-7 Days"
OR [duration_band] = "8+ Days"
THEN [device_days]
ELSE 0
END
) = 0
THEN NULL
ELSE
1000 *
SUM(
IF [duration_band] = "5-7 Days"
OR [duration_band] = "8+ Days"
THEN [candidate_infections]
ELSE 0
END
)
/
SUM(
IF [duration_band] = "5-7 Days"
OR [duration_band] = "8+ Days"
THEN [device_days]
ELSE 0
END
)
END
SUM([device_days])
/
WINDOW_SUM(SUM([device_days]))
CASE [Select Patient Context]
WHEN "Immunosuppression" THEN
IF [immunosuppressed_flag] THEN
"Immunosuppressed"
ELSE
"Not Immunosuppressed"
END
WHEN "Diabetes" THEN
IF [diabetes_flag] THEN
"Diabetes"
ELSE
"No Diabetes"
END
WHEN "Age Group" THEN
IF [age] < 40 THEN
"<40"
ELSEIF [age] < 60 THEN
"40–59"
ELSEIF [age] < 80 THEN
"60–79"
ELSE
"80+"
END
WHEN "BMI Category" THEN
[bmi_category]
END
IF SUM([device_days]) = 0 THEN
NULL
ELSE
1000 *
SUM([candidate_infections])
/
SUM([device_days])
END
These patient-context comparisons are descriptive. They were not designed as an adjusted clinical risk model.
SUM(
IF [surveillance_review_status] =
"Needs Review - Device Eligible but NHSN N"
OR [surveillance_review_status] =
"Needs Review - Missing Culture Date"
THEN [surveillance_records]
ELSE 0
END
)
SUM(
IF [surveillance_review_status] =
"Not Device-Assessable"
THEN [surveillance_records]
ELSE 0
END
)
CASE [surveillance_review_status]
WHEN "Consistent Candidate" THEN
"Consistent Candidate"
WHEN "Consistent Non-Candidate" THEN
"Consistent Non-Candidate"
WHEN "Needs Review - Device Eligible but NHSN N" THEN
"Needs Review: Device Eligible / Source N"
WHEN "Needs Review - Missing Culture Date" THEN
"Needs Review: Missing Culture Date"
WHEN "Not Device-Assessable" THEN
"Not Device-Assessable"
END
Dashboard title:
Hospital-Acquired Infection Prevention and Device Safety Overview
Subtitle:
Synthetic 2024 Data | Analytical CAUTI/CLABSI candidates, device exposure, and maintenance bundle performance
The dashboard contains five KPI cards:
Candidate Rate
Candidate Infections
Device Days
Bundle Compliance
Audit Count
It also contains four visualizations:
Unit Candidate Rate
Unit Bundle Compliance
Monthly vs. 3-Month Rolling Candidate Rate
Monthly Bundle Compliance Trend
The infection-type parameter is displayed as the primary control, while the unit logic allows hospital-wide or unit-specific filtering where applicable.
The monthly candidate-rate trend includes:
Month
Unit
Candidate infections
Device-days
Candidate rate per 1,000 device-days
Device type
The purpose was to allow a user to see the numerator and denominator behind a rate rather than interpreting a rate without context.
Dashboard title:
Prevention Drivers and Improvement Opportunities
Subtitle:
Maintenance reliability, device exposure, and outcome patterns
The dashboard includes four KPI cards:
Noncompliant Maintenance Audits
Device-Days in 5+ Day Episodes
5+ Day Exposure Share
5+ Day Candidate Rate
The user can change infection type and unit.
The dashboard contains five primary visualizations.
Bar chart comparing compliance across the maintenance elements applicable to the selected device type.
Tooltips include compliant and noncompliant observation counts in addition to the percentage.
Bar chart showing the number of audits that failed the all-or-none bundle measure in each unit.
The tooltip includes total audits to prevent the failure count from being viewed without its audit-volume context.
Bar chart comparing candidate rates across:
1–2 Days
3–4 Days
5–7 Days
8+ Days
Bar chart showing total device-days by duration group.
Labels also communicate the share of device exposure contributed by each duration group.
X-axis: Bundle Compliance %
Y-axis: Candidate Rate / 1,000 Device-Days
Color: Unit
Size: Device-Days
Detail: Month
The scatterplot was included as an exploratory process-versus-outcome view. It should not be interpreted as evidence that bundle compliance alone causes changes in candidate infection rates.
The dashboard also presents the improvement themes identified from the combined analysis:
ICU central-line prevention as the leading improvement opportunity
CHG bathing and hub disinfection as specific ICU central-line maintenance opportunities
Complete-bundle reliability as more important than any single isolated element
Prolonged device exposure as an area for targeted review
Surveillance data quality as an additional improvement opportunity
Dashboard title:
Case and Surveillance Context
Subtitle:
Clinical context, candidate characteristics, and surveillance QA
The dashboard contains four KPI cards:
Analytical Candidate Events
Median Device Day at Culture
Records Needing Review
Not Device-Assessable
The remaining visualizations are:
Bar chart showing the organisms represented among the analytical candidate events for the selected infection type.
Bar chart controlled by the Select Patient Context parameter.
The user can compare candidate rates by:
Diabetes
Immunosuppression
Age Group
BMI Category
Tooltips provide candidate counts and device-day denominators in addition to the calculated rate.
Highlight table with unit across columns and review category across rows.
The matrix shows where records fall among:
Consistent Candidate
Consistent Non-Candidate
Needs Review: Device Eligible / Source N
Needs Review: Missing Culture Date
Not Device-Assessable
Event-level table containing:
Infection ID
Culture date
Unit
Organism
Device day at culture
Total device days
Primary diagnosis
Age
Additional patient and device context is available in the tooltip, including sex, diabetes status, immunosuppression status, BMI category, and device type.
This dashboard was designed for investigation rather than high-level performance monitoring.
Dashboard title:
Quality Improvement Summary and Next Steps
Subtitle:
Improvement priorities, analytical methods, future directions, and limitations
Synthetic 2024 Data
Unlike the previous dashboards, this page is fixed at the hospital-wide project level and is intended as the synthesis page.
Five scope KPIs summarize the analysis:
865 Device Episodes
3,912 Device-Days
98 Surveillance Records
1,368 Maintenance Audits
31 Analytical Candidates
The dashboard then summarizes four priority improvement opportunities.
ICU CLABSI had:
Highest candidate rate: 14.67 per 1,000 central-line days
Lowest complete bundle compliance: 61.4%
The weakest ICU central-line maintenance elements were:
CHG bathing: 87.9%
Hub disinfection: 89.0%
Suggested next step: focus additional investigation on ICU central-line maintenance reliability, including CHG bathing, hub disinfection, dressing maintenance, and overall completion of the full maintenance bundle.
Approximately:
62.5% of central-line days
61.7% of Foley catheter-days
occurred in device episodes lasting five or more days.
Candidate events were heavily concentrated in these longer-duration device groups.
Suggested next step: strengthen interdisciplinary daily device-necessity review, review continued indications and contraindications, support timely removal when clinically appropriate, and increase attention to prolonged device episodes.
The 5+ day threshold is an analytical grouping and should not be interpreted as a clinical removal rule.
Many individual maintenance elements exceeded 90% compliance, while all-or-none compliance could remain substantially lower.
For example, ICU central-line all-or-none compliance was only 61.4%.
This demonstrates how small failures distributed across multiple maintenance elements can accumulate into poor reliability of the complete process.
Suggested next step: monitor both individual elements and the complete all-or-none bundle and consider more frequent process audits when evaluating an intervention.
The analysis identified:
30 records requiring additional surveillance review
26 Device Eligible / Source N
4 Missing Culture Date
33 records that were not device-assessable
Suggested next step: improve device-event linkage, flag missing or illogical surveillance values earlier, and periodically review classification discrepancies.
Source Data
Patients, encounters, devices, surveillance, and process audits
→
Quality Assurance
Keys, missing values, dates, units, and relational consistency
→
Exposure and Surveillance
Device-days, timing, device association, and candidate classification
→
Process Measures
Maintenance audits, element compliance, and all-or-none bundles
→
Quality Improvement Analysis
Rates, trends, unit variation, duration, patient context, and improvement prioritization
Candidate Rate / 1,000 Device-Days
Analytical candidate events ÷ relevant device-days × 1,000.
All-or-None Bundle Compliance
Audits meeting every applicable maintenance element ÷ total audits × 100.
3-Month Rolling Candidate Rate
Candidate events across the current and previous two months ÷ device-days across the same three-month period × 1,000.
Analytical Candidate Event
A surveillance record meeting the project's available timing, device-association, source-criteria, and classification logic. It is not an independently confirmed NHSN infection.
Prospectively monitor interventions
Follow candidate rates, bundle reliability, device-days, and prolonged-device exposure after process changes using monthly or rolling trends and, in a real QI setting, formal control-chart methods where appropriate.
Strengthen surveillance data capture
Add the clinical criteria and event-date information necessary for more complete NHSN-aligned surveillance rather than relying on culture date and available-field proxies.
Expand process and operational measures
Incorporate insertion practices, device indications and contraindications, unit transfers, staffing or shift context, removal delays, and other potentially actionable workflow variables.
Establish evidence-based performance targets
Use organizational history and appropriate external benchmarks before defining formal infection-rate or compliance goals.
Synthetic dataset
The findings demonstrate QI methodology and should not be interpreted as actual hospital performance.
Analytical candidates rather than confirmed NHSN infections
The available fields cannot independently reproduce every formal surveillance criterion.
Culture date used as a timing proxy
Culture date does not necessarily equal the formal infection Date of Event.
Sampled process audits
Maintenance was not observed continuously or on every device-day. Missing audit information does not mean noncompliance.
Observational associations
Bundle compliance, duration, patient characteristics, and candidate rates can be associated without establishing causation.
Small event counts
Monthly rates can become unstable when one or two candidate events occur over relatively small device-day denominators.
Simplified unit attribution
The synthetic dataset does not fully capture patient movement between units.
Analytical duration bands
Categories such as 5+ days support exploration and should not replace clinical judgment or formal device-indication criteria.
Outcome
CAUTI and CLABSI analytical—or formally adjudicated—rates per 1,000 relevant device-days.
Process
All-or-none maintenance compliance, individual high-priority elements such as CHG bathing and hub disinfection, and daily necessity review.
Exposure
Total device-days and the share of exposure occurring in prolonged device episodes.
Data Quality
Records requiring surveillance review and completeness of device-event linkage.
Balancing
In a real clinical implementation, unintended consequences of earlier device removal and additional workflow burden should also be monitored.