Workforce BI: JW Marriott San Antonio

A Power BI compliance & analytics dashboard for tracking hourly employees at the JW Marriott San Antonio Hill Country Resort & Spa — built as the individual final assessment for DAT-8564 Business Intelligence (MS Business Analytics, Hult International Business School, Spring 2026).

7-day timed assignment. Power BI as the only tool. Real ETL, real ambiguity, real compliance stakes.

---

The problem

The property has two completely disconnected workforce systems:

1. WhenToWork (W2W) — schedules planned shifts for non-represented part-time college student workers. Read-only API. No actual-hours data.
2. Kronos (UKG, 2019 legacy version) — punch-clock for all employees (full-time union + part-time students). No API at all. Exports broken .xls files that HR cleans manually each pay period.
3. Oracle PeopleSoft — receives only summary weekly hours from Kronos for payroll. No shift detail, no compliance monitoring, no planned-vs-actual reconciliation.

Two systems. Zero connection. Maximum risk.

The compliance stakes are real:

• 19-hour weekly cap — exceeding triggers benefits eligibility for student workers, transforming a flexible cost-efficient hire into a benefits-entitled employee.
• 1,000-hour cumulative tenure ceiling — crossing it triggers de facto union membership under the property's CBA.
• Buddy punching — Kronos uses swipeable ID cards (no biometrics), so impersonation is trivially possible.
• Rounding-rule gaming — the 7-minute rounding rule can be exploited by employees who consistently punch right at the boundary.

A global Kronos replacement is on the JW Marriott roadmap, but it isn't projected to go live until Summer 2028. Until then, the gap has to be bridged by analytics.

What this dashboard does

A 5-page Power BI report plus a 1-page executive narrative, built on a star-schema data model spanning 8.5 months (July 2025 – March 2026) and 18 bi-weekly pay periods.

Star schema

Key DAX measures

The 5 dashboard pages

1. Department Manager Dashboard — weekly shift grid, planned vs. actual, 19-hour-cap compliance indicator, department spend YTD.
2. HR Compliance & Punch Audit — missed-punch table, night-punch and marathon-shift anomalies, 19-hr/week and 1,000-hr cumulative trackers, gaming detection.
3. Overtime Tracking — weekly OT summary, OT cost table by employee/department, seasonal OT trend.
4. Absenteeism & Schedule Adherence — planned vs. actual attendance rate, chronic-absenteeism flag, day-of-week patterns, roster-recommendation flag.
5. Executive One-Pager: State of Hourly Workforce — single-screen executive summary covering total hours by employee type, payroll spend split (regular vs OT), at-risk students, property-wide absenteeism trend, and anomaly resolution rate.

Business rules implemented

The 7-minute rounding rule

Every punch time rounds to the nearest quarter hour. If a punch is within 7 minutes of an XX:00 / :15 / :30 / :45 mark, it rounds to that mark. More than 7 minutes away → rounds to the next nearest quarter.

Critically: raw Kronos punches are never used directly. Every hour-based calculation downstream (overtime, weekly totals, cost) starts from RoundedHours.

Overtime — Sunday–Saturday week, 40-hour threshold

OT pays at 1.5× regular rate, applies to both unionized and student workers. Triggered by weekly total > 40, not by daily hours alone (so 4×10-hour days = 40 hrs = 0 OT).

Missed-punch detection

Common pattern: student punches in at 7 AM, forgets to punch out at 10 AM, returns at 7 PM and punches in again. Kronos reads the second punch-in as the first shift's punch-out → reports 12 hours when the truth is 3 hours + a new shift.

The dashboard cross-references Kronos shifts against the W2W planned schedule and flags shifts exceeding a reasonable threshold (16 hours or 10+ consecutive hours) for HR correction before pay period close.

Anomaly detection

• Rounding gaming — flags employees who repeatedly punch in the 6–7 minute window of a quarter-hour mark (suspiciously close to the rounding boundary).
• Impersonation / buddy punching — flags employees punching at identical times for 5+ consecutive shifts (statistically improbable for real humans).
• Night punches — punches between 11 PM and 5 AM flagged for review.
• Marathon shifts — any single reported shift of 10+ hours cross-referenced against W2W planned schedule.

Deliverables in this repo

• marriott_workforce_dashboard.pbix — the 5-page Power BI dashboard with all DAX measures, star-schema model, and anomaly-detection logic.
• executive_summary.docx — the executive narrative addressed to the GM, CFO, and HR Director, covering the state of hourly workforce compliance and recommended next 30-day actions.
• dashboard_blueprint.pptx — page-by-page wireframe used to design the 5 report pages before building in Power BI.
• marriott_theme.json — custom Power BI theme matching JW Marriott's brand palette.

Stack

• Power BI (Power Query, DAX, Star Schema modeling)
• Excel for raw data ETL (Kronos .xls and W2W .xlsx exports)
• Custom theme JSON for branded visuals

Course context

DAT-8564 Business Intelligence · Hult International Business School · Spring 2026 · Section BMBANDD1. Case data and JW Marriott context based on the educational case prepared by the instructor; all analytical work, dashboard design, DAX measures, and recommendations are my own.