Healthcare Operations & Supply Chain Telemetry: SQL Window Functions andtableau Workforce Efficiency Dashboard Suite

This project delivers an end-to-end operational analytics framework designed to evaluate and optimize the workflow efficiency of Material Supply Associates (MSAs) across clinical units at Mount Sinai. MSAs utilize Virtual Manager mobile telemetry systems to perform critical supply chain duties, including par-level counting and stock compliance checks. Hospital inventory locations display extreme dimensional variance, ranging from small 20-bin supply closets to major storage hubs containing over 600 bins. Evaluating performance solely on raw task completion duration introduces substantial bias against associates assigned to larger rooms. To solve this, an enterprise analytics pipeline was engineered to transform, normalize, and contextualize labor telemetry data, culminating in an interactive three-tab Tableau dashboard suite. The data infrastructure begins with an advanced Oracle Data Warehouse SQL view designed to join task event logs with inventory master tables and line-level item scan records using standardized location identifiers. SQL window functions form the mathematical engine of the data pipeline. Specifically, the LEAD window function partitions telemetry records by associate owner and orders them chronologically by task completion timestamps. This enables the automatic calculation of inter-task gap durations (in-between time) across sequential tasks performed on the same calendar day. The pipeline dynamically categorizes these gaps into structured operational buckets: transitions (under 5 minutes), travel (5 to 30 minutes), idle time (30 to 60 minutes), and extended idle periods (over 60 minutes). Standardized performance evaluation required dedicated metric engineering within SQL helper views and custom dashboard calculations. A core efficiency benchmark, Weighted Minutes per Bin, was computed by dividing total task duration by total room bin capacity. This metric evaluates associate efficiency by controlling for assigned room scale rather than unadjusted volume. Additional engineered metrics include scan coverage percentage, non-completion rate tracking (~17.5% across cancelled or incomplete tasks), and scanning velocity (seconds per item scanned). Empirical analysis established a baseline scanning rate of approximately 30 seconds per item end-to-end. This highlighted an operational discrepancy against the previously assumed 5 to 6 second scan time, revealing significant time expenditures in travel, physical evaluation, and system navigation. The Tableau dashboard suite is structured across three functional views for supply chain leadership. The Par Orders Efficiency dashboard features real-time KPI indicators, an unfiltered scatter plot mapping room capacity against task duration to preserve extreme performance outliers for root-cause analysis, and a temporal heatmap (Hour of Day by Day of Week). The heatmap pinpointed operational bottlenecks, showing heavy task concentration and elevator congestion between 7 AM and 10 AM on weekdays. A parameterized trend chart allows executive users to toggle time granularity between daily and weekly views of normalized weighted efficiency metrics, restricted strictly to completed tasks to prevent statistical skew. The In-Between Time Analysis dashboard quantifies non-active shift time using dual-axis visualizations that overlay average gap durations onto maximum gap durations per associate, enabling leadership to distinguish systemic operational delays from isolated incidents. Finally, an unaggregated audit tab provides a drillable table for complete record verification. This system translates raw transactional supply chain logs into actionable operational intelligence.