The payroll cost star

Every report in this module is one table, aggregated five different ways. This page is that table: what one row holds, what joins to it, and why three of its five dimensions are borrowed rather than built.

RuleOne fact at person, pay component, and period. Every report in the module is an aggregation of this table and nothing else.

Neverbuild a second payroll table for a new report. Two tables drift, and then the reports argue with each other instead of with the ledger.

◆ The shapeone table of amounts, five tables of context.

A fact is the table of amounts. A dimension is a table of context: who, which department, which kind of pay, which company, which date. The fact sits in the middle and the dimensions join to it, which is why the shape is called a star.

The fact carries amounts. The dimensions answer who, what, where, and when.

Three of the five are not payroll tables at all. Department, company, and date are the same tables the rest of the catalog already uses for headcount and for the ledger. That sharing is the entire trick: because payroll cost and ledger balances sit on the same department and date tables, they can be laid side by side without translation. That is what made the ledger tie a query instead of a project.

◆ The fact, column by columnwhat one row holds and why.

Column	What it holds
worker_key	Which person. Joins to the worker table shared with headcount.
pay_component_key	Which kind of pay: base, overtime, a tax, a benefit. Carries whether the amount is earning, deduction, or employer cost, and whether it reaches the books.
cost_center_key	Which department is charged. Missing on unassigned lines, and kept missing rather than papered over, so the unassigned money stays visible.
company_key	Which legal entity paid.
accounting_date_key	The date the amount is recognised in the books. This, not the pay period end, is what the ledger tie filters on.
pay_period, run_type	Which run produced the row, and whether it was a regular run or one of the four off cycle kinds.
amount, hours	The money, and the hours behind it where the pay is hourly.

Notice what is absent. No department name, no account name, no month column. Names live in the dimensions and are joined in when needed. A fact that stores names goes stale the first time a department renames itself; a fact that stores keys never does.

Use case

Problem

Payroll questions multiply: by department, by pay type, by month, by entity. Each new question tends to get its own extract and its own table, and the tables drift apart.

What we build

One payroll fact at person, pay component, and period, joined to the department, company, and date tables the catalog already shares with headcount and the ledger.

What you get

Every payroll report aggregates one proven table. New questions are new queries, not new pipelines, and every answer ties back to the same total.

Want one payroll table instead of five extracts?

We build the fact, share the dimensions, and every report after that is a query.

Talk to us

Terms on this page

fact: A table of measured amounts. Here, pay.
dimension: A table of context joined to the fact: who, which department, which date.
star: The shape of a fact in the middle with dimensions joined around it.
key: A number linking a fact row to its dimension row. Survives renames; names do not.
pay component: One kind of pay or deduction: base salary, overtime, a tax, a benefit.
cost center: The department or team a cost is charged to.
accounting date: The date an amount is recognised in the books.
run type: Whether a row came from a regular run or an off cycle payment.
off cycle: A payment made outside the regular payroll run.
unassigned: A pay line with no department. Kept visible, never defaulted away.