Healthcare

Appointments

One row per scheduled appointment, capturing how a visit was booked and whether the patient actually showed up. Each row links a patient, a provider and a department, records the scheduled date and time, how far in advance it was booked (lead time), how long the patient waited past that time, whether they were a no-show, and the appointment's final status. This is the front door of the clinical and revenue cycle — every encounter and claim downstream starts as a booked appointment here.

Fields

SAFE_DIVIDE(COUNTIF(is_no_show), COUNTIF(status IN ('completed', 'no-show')))

CASE WHEN lead_time_days <= 3 THEN '0-3 days' WHEN lead_time_days <= 14 THEN '4-14 days' WHEN lead_time_days <= 30 THEN '15-30 days' ELSE '31+ days' END

Relationships

Bed Census (daily)

One row per department per day, tracking inpatient bed capacity and patient flow over time. Each row records how many beds were staffed and how many were occupied that day, alongside the day's admissions and discharges. This is the operational pulse of capacity management across the health system.

Fields

SAFE_DIVIDE(SUM(occupied_beds), SUM(staffed_beds))

SAFE_DIVIDE(SUM(occupied_beds), COUNT(DISTINCT census_date))

Relationships

Claims

One row per claim submitted to a payer for a clinical encounter — the revenue-cycle record that turns care into cash. Each claim tracks the billed, payer-allowed and actually paid amounts, its submission and payment dates, current status, the denial reason when one applies, and the number of days it has sat in accounts receivable. This is the mart that answers how well the health system gets paid for the care it delivers, and how quickly.

Fields

SAFE_DIVIDE(COUNTIF(status = 'denied'), COUNTIF(status IN ('paid', 'denied')))

SAFE_DIVIDE(SUM(IF(status = 'paid', paid_amount, 0)), SUM(IF(status = 'paid', allowed_amount, 0)))

SAFE_DIVIDE(SUM(IF(status = 'paid', paid_amount, 0)), COUNT(DISTINCT IF(status = 'paid', encounter_id, NULL)))

Relationships

Department

Reference list of the clinical departments that make up the health system, each with the clinical specialty it serves and its staffed-bed capacity. Departments are the organizational unit behind appointments, provider assignments and daily bed census. Staffed beds here is each unit's standing capacity; the day-by-day count, net of beds taken out of service, and the occupancy it supports are in Bed Census (daily).

Fields

Encounters

One row per clinical encounter — the actual visit that followed a scheduled appointment. Each encounter records its type (outpatient, inpatient or emergency department), the primary diagnosis, admission and discharge timestamps, the resulting length of stay, and whether it was an unplanned readmission within 30 days. This is where the clinical story lives: what patients are being treated for, how long they stay, and how often they come back unexpectedly. What each insured encounter was billed and paid is in Claims.

Fields

SUM(IF(encounter_type = 'inpatient', length_of_stay_days, 0))

SAFE_DIVIDE(inpatient_days, COUNTIF(encounter_type = 'inpatient'))

Relationships

Patient

One row per patient, the demographic and coverage anchor for every appointment, encounter and claim in the system. Each patient carries a birth year and gender for age-banded analysis, a postal code for geographic reach, an insurance type (commercial, Medicare, Medicaid or self-pay) that determines how their care gets billed, a risk-stratification tier used by care management to flag patients who need closer follow-up, and the date they first registered with the system.

Fields

Payer

Reference list of the insurance payers and plans that reimburse the health system for care, each identified by name and plan type — HMO, PPO, EPO or government (Medicare/Medicaid). Every claim is billed to one of these payers, so this mart is the lookup behind any payer-mix or reimbursement analysis.

Fields

Provider

One row per clinician, the roster behind every appointment and encounter. Each provider is identified by name, clinical specialty, home department, and National Provider Identifier (NPI) — the standard identifier used to credential and bill for a clinician's services. This is the lens for looking at care delivery by clinician: who is seeing patients, in what specialty, and out of which department.

Fields