One star for pay: the snapshot and the events

Compensation adds one question to the workforce model: what is this worker’s pay, against what band, as of any date. The star answers it with a daily snapshot fact, a pay change event fact, and the versioned grade dimension both share.

RuleKeep pay as a daily snapshot beside an append-only change event fact. As-of questions read the snapshot; why-did-it-change questions read the events.

Neverstore only current pay. A ratio with no history cannot show what the cycle did, and a refresh rewrites every trend behind you.

Two red facts, five shared dimensions. The snapshot answers as-of questions; the events answer what changed; the grade dimension prices both.

◆ The two factsstate and change, kept apart on purpose.

Fact	Grain	Carries
fct_comp_snapshot	one row per worker per day	Base, target variable, grade key; the state every report reads as-of.
fct_comp_event	one row per pay change	Old value, new value, reason, effective date, entry date; the ledger.

Every report in the module is one of two questions. Where does pay sit: the ratios, the mix. What moved it: the cycle, the promotions, the ledger. One fact each.

◆ The shared dimensionsthe same tables the rest of the catalog joins.

Dimension	Role here
dim_worker	Who, versioned, shared with Core HR and payroll; the 351 on every page.
dim_comp_grade	The bands and ranges, versioned with validity dates; every ratio’s denominator.
dim_org	The department rollup, the same tree the payroll module costs against.
dim_plan	Merit and bonus plans with targets and funding rules.
dim_date	The calendar, including the working days the payroll accrual counts.

◆ What the star provesthe ties that make the module checkable.

Because the facts share dimensions with payroll, the module’s numbers are claims another module can verify. The cycle’s 48,090.00 a month is the bridge’s merit driver. The salary basis reconciles to posted wages on the salary-versus-payroll page. The backdated correction is the restatement’s 3,200.00.

-- as-of pricing: each snapshot day against the range valid that day
SELECT f.snapshot_date, f.worker_id,
       f.base_annual / g.range_mid AS compa
FROM fct_comp_snapshot f
JOIN dim_comp_grade g
  ON f.grade_key = g.grade_key
 AND f.snapshot_date BETWEEN g.valid_from AND g.valid_to

Use case

Problem

Compensation data lives as current values in the tool: no history, no event trail, and numbers no other system can confirm.

What we build

A daily snapshot fact and an append-only event fact on the catalog’s shared dimensions, with the grade dimension versioned and joined as-of.

What you get

A model where every report is a rerunnable query, every trend survives a range refresh, and payroll can confirm the module’s numbers to the cent.

Want compensation data payroll can vouch for?

We build the two-fact star on your cloud, tied to the payroll module to the cent.

Talk to us

Terms on this page

snapshot fact: State captured daily: one row per worker per day.
event fact: Changes appended as they happen: one row per pay change.
dimension: A shared lookup table: worker, grade, organization, plan, date.
versioned: Kept with validity dates so history joins to its own era.
as-of join: Matching a date’s fact row to the dimension version valid that day.
grain: What one row means; the first question of any model.