The hours star, day by day

Two facts and five dimensions. Time entries at day grain, scheduled hours beside them, and one calendar that knows both the week and the pay period every day belongs to. That double calendar is the whole trick.

RuleKeep the grain at the day. Weeks, periods, months, and windows are all rollups of days; nothing else rolls both ways.

Neverstore hours pre-bucketed by period. The bucket that helps payroll breaks overtime, and the model should serve both.

Two facts, five dimensions. The calendar dimension carries both weeks and pay periods, so the alignment trap becomes a join.

◆ The tablestwo facts, five dimensions, one double calendar.

Table	Grain	Feeds
fct_time_entry	One worker, one day, hours by time type, with approval state and timestamps.	Overtime, reported against paid, lookbacks.
fct_schedule_day	One worker, one day, scheduled hours from the work calendar.	Schedule variance, expected-hours denominators.
dim_worker	Who. Shared with the Core HR star.	Every report's population.
dim_time_type	Regular, overtime, double time, on call: as configured in the tenant.	Type splits and premium hours.
dim_pay_period	The payroll cycle each day maps to.	The paid-hours side of the reconciliation.
dim_org	The department rollup, same as payroll's.	Hours by department that tie to cost.
dim_date	The calendar carrying week and period keys per day.	The alignment join, both directions.

◆ Why the day is the graineverything rolls up; nothing rolls down.

A day belongs to exactly one week and exactly one pay period, so day-grain rows roll into either without loss. Period totals cannot be split back into weeks, which is precisely the delivered gap practitioners bridge by hand. The approval state and its timestamps ride on the entry row, which is what makes the pre-deadline unapproved-hours control on the reconciliation a filter instead of a fire drill.

Extraction runs on the extraction pattern; hours flow onward into the payroll facts the payroll module proves to the ledger, one continuous chain from punch to books.

Use case

Problem

Hours exist pre-bucketed by period, so weekly overtime, trailing windows, and schedule variance all need manual rebuilds.

What we build

A day-grain time entry fact and a schedule fact on a calendar that carries weeks and pay periods together.

What you get

Every calendar question as a join: weeks, periods, windows, and variance from one model that also feeds payroll.

Can your hours roll up both ways, week and period?

We ship the day-grain model where both are just joins.

Talk to us

Terms on this page

HR: Human resources. The Core HR star shares its dimensions with this one.
fact: A table of measured rows. Here, worked and scheduled hours.
grain: What one row means. One worker, one day.
double calendar: The date dimension carrying week and pay period keys per day.
time type: The configured category of an hour: regular, overtime, on call.
approval state: Where an entry stands: entered, submitted, approved.
work calendar: The schedule pattern generating expected hours per day.