Headcount Analytics as a Slowly-Changing Dimension Problem: Why the Org Chart Needs Effective-Dated History

Ask three teams in the same organisation for last March’s headcount by department and you will usually get three numbers. None of the teams made an arithmetic error. Each pulled the current state of the HRIS and filtered it backwards, which means each answered a slightly different question: how many of today’s employees, in today’s departments, were employed last March. That is not last March’s headcount. This piece argues that the org chart is a slowly-changing dimension problem, sets out why point-in-time snapshots beat current-state pulls, and catalogues what breaks when reports read the current row.

The org chart is a Type 2 dimension

In warehouse terms, a slowly-changing dimension is an attribute set that changes irregularly and whose history matters. The Type 2 treatment (SCD2) keeps every version of a record as its own row, bounded by effective_from and effective_to, with a flag or open-ended date marking the current version. A Type 1 treatment overwrites the old value.

Almost every attribute people analytics cares about is Type 2 by nature: department_id, manager_id, job_level, location, cost_centre, fte, employment_status. Most HRIS reporting exports, however, are Type 1 by default. They return one row per person, carrying current values.

The consequence is that a current-state pull cannot reconstruct the past. It can only project the present onto it.

What breaks when reports read the current row

The failure modes recur across the large-firm extracts we have reviewed:

• Restructure back-propagation. A department merged in Q3 appears to have existed, at its merged size, for the full year. Any growth trend for either predecessor unit is erased; the successor’s trend is inflated.
• Leaver disappearance. Terminated employees are often excluded from active-population exports, or retained with their last-known attributes only. Historical headcount for a unit with high attrition is understated, and attrition rates computed from that headcount are overstated, because the denominator was quietly shrunk.
• Manager attribution drift. An employee’s whole history is credited to their current manager. Span-of-control series, manager-level attrition, and team engagement trends all inherit the error.
• Level inflation. Today’s level is applied to past periods, so historical level mix looks more senior than it was, and promotion rates computed as level changes between pulls are distorted.
• Non-reproducibility. Rerun the same report next quarter and last March’s number changes, because the current state changed. A metric whose history moves is not a history.

Snapshots versus effective-dated history

There are two defensible architectures, and they are not mutually exclusive.

Point-in-time snapshots freeze the full population at a fixed cadence (month-end is typical) into an append-only table keyed by snapshot_date. Their strength is reproducibility: last March’s number is whatever was frozen last March, permanently. Their weakness is granularity. Anything that happened and reversed between snapshots is invisible, and mid-month events are rounded to the nearest boundary.

Effective-dated history stores every change as a dated row, so any date can be reconstructed with an as-of join: effective_from <= d AND (effective_to > d OR effective_to IS NULL). Its strength is completeness. Its weakness is that it depends on the source system recording changes when they took effect, and many do not. Retroactive changes, entered weeks after the event with a back-dated effective date, mean the history itself is revised after the fact.

That last point is the reason we recommend running both. Snapshots record what the organisation believed on a given date. Effective-dated history records what the organisation now believes was true on that date. The gap between the two is a measurable quantity (the retroactive-correction rate), and it deserves tracking.

Minimum specification

For any headcount series that will appear in a planning or board document:

1. State the as-of convention. Snapshot-based, or reconstructed from effective-dated history, and as of which reporting date.
2. Hold leavers in the dimension. Terminated employees keep their full attribute history; exclusion is a filter applied at query time, never at load time.
3. Key every fact to a dimension version, not a person. A promotion event joins to the row that was valid on the event date.
4. Version the org hierarchy separately. Department parent-child relationships change on their own schedule, independently of employee records, and need their own SCD2 table.
5. Publish the restatement log. When a historical figure changes, record the old value, the new value, and the cause.

To illustrate the scale of the problem, with numbers constructed for demonstration, not measured: a 4,000-person unit with 15% annual attrition and one mid-year restructure can show a prior-year headcount that differs by several hundred people depending on whether leavers and the restructure are handled in the dimension or flattened by a current-state pull.

What we cannot claim

Effective-dated history is only as good as the dates the source system records. If transactions are keyed late, or back-dated inconsistently across modules, an SCD2 model will faithfully reproduce the inconsistency. SCD2 makes history possible; it does not make it correct. The defensible claim is narrower: without versioned dimensions, historical headcount cannot be reproduced at all, and every trend built on it is a projection of the present. The data always wins over the narrative, but only if the data remembers what it used to say.