Health Workforce Planning Data Requirements

A health workforce plan needs five kinds of data: who is employed (positions and people, by role, status, location, and cohort), how they flow (hires, exits, leaves, and internal moves over time), how much of their paid time reaches the point of care (productive hours), what work they are delivering (activity and census), and what the population will need (demographics and, where the question is access, health status). Most health systems hold all five somewhere. Almost none hold them in a form that can be joined, refreshed monthly, and trusted by the people who have to act on the result.

That gap, not the modeling method, is what usually stalls workforce planning. The finding from workforce audits is consistent: definitions differ between HR, payroll, and scheduling; extracts are manual and undocumented; and nobody owns the number. Fixing the data foundation is the first and highest-value phase of any planning build, and it is the reason a workforce audit starts with data and governance rather than with a model.

Every planning model is a join across HR, payroll, scheduling, and clinical systems. If the join is wrong, the model is wrong, however elegant the equations.

The five data domains

Domain	Minimum fields	Usual source	Usual problem
Positions and people	Position ID, funded FTE, filled FTE, classification, unit, site, employment status, hire date, birth year or tenure band	HRIS	Position and person conflated; unit codes differ from clinical systems; casual pool untracked
Flows	Hire, exit, exit reason, leave start and end, transfers, by month, three to five years	HRIS history tables	History overwritten; exit reasons unreliable; internal transfers counted as exits
Productive hours	Paid hours by pay code (worked, overtime, sick, vacation, education, orientation) by unit and month	Payroll, time and attendance	Pay codes not mapped to categories; hours charged to home unit, not worked unit
Activity and census	Census by unit by shift or hour; visits, cases, referrals by service; ADT events	ADT, EHR, registration	Unit definitions differ from HR; midnight census only; virtual and overflow beds unlabelled
Population	Population by age and sex by geography, projected; prevalence for priority conditions	Statistics agencies, public health	Geography does not match catchment; projections stale

Definitions before pipelines

The most expensive mistake is automating an extract before agreeing what it measures. Four definitions cause most of the disputes and should be written down, signed by HR, finance, and operations, and versioned:

Vacancy: funded minus filled, but filled by whom? Include or exclude positions held by someone on leave, positions with an accepted offer, positions backfilled by casual staff.
FTE: contracted, paid, or worked. Each is a different number and each is right for a different question.
Turnover: exits over average headcount, but which exits? Retirements, internal transfers, and casual-to-permanent conversions all distort the rate if not classified.
Unit: the single crosswalk between HR department codes, payroll cost centres, and clinical unit identifiers. Without it nothing joins.

Building the foundation

Inventory and score

List every source, its owner, refresh frequency, and the fields it holds against the five domains. Score availability, quality, and definitional consistency. This is the data section of a workforce audit.

Agree the definitions

Vacancy, FTE, turnover, unit crosswalk, productive-hours categories. One page, signed, versioned.

Build the unit crosswalk

The table that maps HR department, payroll cost centre, scheduling unit, and clinical unit to one planning unit. Maintain it as a governed asset, because reorganizations break it constantly.

Automate the monthly extract

Positions, flows, hours, and census by planning unit, produced on a schedule without manual steps, into one place. A modest data warehouse or even a governed set of tables is enough to begin.

Publish the baseline reports

Headcount, vacancy, turnover, overtime, and productive hours by unit, monthly, with definitions on the page. Leaders start trusting numbers when the same number appears in every meeting.

Then model

Forecasting, ratio feasibility, and capacity planning all sit on top of this. Built in this order, the models are maintainable. Built first, they are one-off reports.

How long this takes

For a regional health authority with a functioning HRIS and payroll system, steps one to five are typically four to six months of focused work, most of it on definitions and the crosswalk rather than on technology. Organizations mid-restructuring, or integrating several legacy systems, should expect longer and should treat the crosswalk as the first deliverable of the integration.

Sources

Canadian Institute for Health Information. Health Workforce data and standards. cihi.ca
Health Workforce Canada. State of Health Workforce Modelling and Forecasting in Canada. 2024. healthworkforce.ca
Someplum Consulting. Healthcare Workforce Audit: what we assess. someplumconsulting.com

Questions leaders ask about workforce data

Do we need a data warehouse before we can plan?

No. You need governed definitions, a unit crosswalk, and a repeatable monthly extract. That can start as a set of managed tables. A warehouse becomes worthwhile once the extracts are stable and several models depend on them.

Our HR and clinical systems use different unit codes. Is that fixable?

Yes, and it is the first thing to fix. The crosswalk is a maintained table, not a one-time mapping. Assign an owner in workforce planning and update it as part of every reorganization.

How much history do we need?

Three years of monthly flows is enough to estimate attrition and retirement rates by cohort. Five is better for seasonality. If history has been overwritten, start capturing monthly snapshots now; the model improves each year.

What if our payroll pay codes are a mess?

They usually are. Mapping pay codes to productive, non-productive, and premium categories is a one-time exercise of a few days that unlocks productive-hours reporting, which is the single most useful number in capacity planning.

What Data a Health Workforce Plan Actually Needs