Unit of analysis and people data model
12 min read
Lesson objectives
- Define person, position, assignment, and time grain; join HRIS and operations without double counting.
Please sign in to use all the tools
Once you sign in, the reading tools are yours: larger or smaller text, typeface, line spacing, focus mode, highlighting and bold, and notes saved to your account and gathered in My study.
'We have 420 service-centre employees' sounds precise, yet an HR system might contain 420 people, 450 assignments, and 390 occupied positions. A person is a unique individual, a position is an authorized seat, and an assignment links a person to a position for a period of time. One person may split time across two positions; two people may share a role; a vacant position has no occupant. If the manager asks how much capacity is available to answer customers, none of the three counts settles the question. We must choose the unit of analysis: people, full-time equivalents (FTE), productive shifts, or service requests. This is not merely a database formatting choice. It establishes the denominator behind a staffing recommendation. CIPD's people analytics guidance recognizes that people data from HR and other systems may be brought together to address business problems. A defensible analysis first specifies how those systems can be joined without creating records that never represented real work.
Use a four-layer model. A person table has a stable internal ID, rather than a name or email address as its join key; names repeat and addresses change. A position table has a seat ID, team, and organizational location. An assignment table holds person ID, position ID, work fraction, start date, and end date. It represents internal moves and changes in hours over time. An operations table holds shifts, requests, or service hours with their own IDs and timestamps. The safe method is not to join every row and then count the result. Numerous tickets will replicate a worker's record, while multiple assignments may replicate tickets if attribution is undefined. Fix a snapshot date, select assignments valid on that date, aggregate operating hours at the intended level, such as team-week, and only then join operating measures to the workforce stock. If a work fraction is missing, do not silently assume 1.0. Quarantine the record for correction or show a lower and upper bound with the number of affected records.
Time matters as much as identity. A June report must not use the team shown in an employee's October record to explain June productivity; that leaks future information into an earlier period. Apply effective dates: an assignment is active from its start and before its end, with an explicit convention for the end day. If someone transfers from support to sales mid-month, a month-end snapshot places them in sales, while a daily capacity estimate must attribute their days to the team in which they worked at the time. Also distinguish the event date from the date the event reached the system. A departure may be entered a week late. Either wait for late data to stabilize before comparing months, or label the newest month provisional. These small definition choices can reverse the trend, so keep them beside the metric definition rather than burying them in a query.
Test the model before using it. Verify uniqueness of person IDs in the person table, check for overlapping assignments to the same person-position pair, and examine the sum of a person's assignment fractions by date. A sum above 1.0 can represent real overtime or a data error; without a stated policy it should prompt review, not an automatic rejection. Confirm that operational records have attributable team and time values, and that a DISTINCT person_id count after the join matches the pre-join person population for the defined slice. Inspect a small sample of transfers, leave, and split assignments by hand. A correct grand total can still conceal capacity moved between teams. Where governance does not permit managers to inspect individual records, perform aggregation in a restricted environment and expose only the minimum team measure needed. A managerial dashboard should not quietly become a surveillance log. The design involves access decisions, not just column names.
One employee in two records: how should her capacity be allocated?
A project manager sends you a list of six names. Operations sends another containing the same six people. Adding the lists would suggest twelve workers even though the same people split their working day. The defect lies in the meaning of a row, rather than addition. Ask whether each row represents a person, position, assignment, or work period. Give the person a stable identity and connect dated assignments to it. Each assignment needs its destination, work share, and task type. This lets the organization count the person once while each team sees its separate allocation. Also establish whether the list describes current assignments or next month's plan.
Inspect assignment start and end dates. One assignment may end on Thursday and another begin on Sunday, while the archived row remains in the system. Summing all rows would combine assignment history rather than today's capacity. Choose the measurement date, then retain only assignments active on that date. For a monthly measure, count actual service days within each assignment using an explicit working calendar. A mid-month transfer contributes part of a month to each team. The month-end allocation hides capacity used before the move, so a stock snapshot serves a different purpose from measuring work delivered during the month.
Check the direction and multiplicity of the relationship before joining person and task records. A person may have three assignments and an assignment may cover a hundred transactions. Joining every person transaction to every assignment without dates or destination could repeat each transaction three times. Compare distinct transaction counts before and after the join. Counts should remain stable when the join merely adds assignment attributes. Sum each transaction's hours once, then inspect totals by person and date. An implausibly large daily total suggests duplication or inconsistent units and needs investigation; it does not automatically establish that the worker submitted inaccurate time.
Another problem appears when one system records scheduled days and another records actual processing minutes. At eight hours per day, five days mean forty scheduled hours, potentially including meetings, partial leave, and access waiting. Processing minutes describe the activity captured by the operational tool. Keep a source-unit column, a conversion column, and the original value for audit. Test conversions against three familiar records. When systems disagree, ask whether they measure the same activity before diagnosing a technical error. Both numbers can be correct while their comparison is wrong because their meanings differ.
After checking, hand over a short definition for each field: person key, assignment key, date precision, and time unit. Add one valid example and one needing correction so another analyst can reproduce the calculation. Preserve the join rules and execution date because later register changes may alter results. At tomorrow's meeting, show people, contractual allocation, and productive hours together. If the manager asks which number is correct, explain the question each measure answers. Data can then support an intelligible decision rather than a dispute between apparently inconsistent lists.
Lesson summary
'We have 420 service-centre employees' sounds precise, yet an HR system might contain 420 people, 450 assignments, and 390 occupied positions.