Conformed Dimensions for Workforce Data
Conformed dimensions for workforce data are the shared keys that every process in a workforce planning model reads the same way: one record per person, one per queue, one per forecast group, one per site, one date and interval calendar, one activity-code list, and one partner flag. A dimension is conformed when every fact that uses it uses the same key, the same attributes and the same history, so that a headcount from the human-resources system, a paid hour from the time record, a scheduled hour from the workforce-management system and a handled contact from the telephony platform can be joined on the person and the day without a lookup table someone maintains by hand.[1] The page is part of the Workforce Data Definitions Framework; it describes the Matrix, People and Queues tabs of the Source of Truth Design Workbook.
Without conformed keys, each unit of a function joins its own data with its own identifiers, and the estate's numbers cannot be added. The symptom is familiar: the human-resources system, the roster and the finance plan each report a different headcount, and nobody can say which is wrong because no record carries all three identifiers for the same person.
The process-by-key matrix
The matrix lists every process of the planning model as a row and every shared key as a column, and marks which keys each process needs on every one of its rows.[2] It is filled first, before any metric is defined, because it tells the function which dimensions it must conform before any fact can be built. An X means the process needs the key on every row; a B means the join is many-to-many and goes through a bridge table (a contact that opened several cases, a queue that serves several clients); a blank means the key is not needed.
| Process | Person | Date | Interval | Queue | Forecast group | Pool | Channel | Unit | Client | Site | Partner flag | Activity code | Forecast version |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Demand | X | X | X | X | X | X | B | X | |||||
| Workload | X | X | X | X | X | X | X | B | X | X | |||
| Requirement | X | X | X | X | X | X | X | X | X | ||||
| Paid supply | X | X | X | X | X | ||||||||
| Productive supply | X | X | X | X | X | X | X | X | |||||
| Plan and budget | X | B | X | X | X | X | X | X | X | ||||
| Schedule | X | X | X | B | X | X | X | X | X | X | |||
| Intraday | X | X | X | X | X | X | X | X | X | X | X | ||
| Actuals | X | X | X | X | X | X | X | X | B | X | X | X | |
| Learn | X | X | X | X | X | X | X | X |
The rows are the ten stages of the planning chain as the hub describes them; a function adds rows for processes of its own (queue management, partner reporting). The filled matrix is read down the columns: a key with an X in most rows is a dimension that must be conformed first, and the person and queue columns are always the busiest.
The person record
The person record, sometimes called the identity map, carries every system's identifier for one person, with the attributes that planning joins on, and keeps history. It is the dimension the function usually lacks: each system holds its own identifier, and the mapping between them lives in a spreadsheet, or in nobody's head.
| Attribute | Holds | Rule |
|---|---|---|
| Person key | One surrogate key per person across every system | Generated; never reused |
| System identifiers | The human-resources identifier; the workforce-management login, one per tenant; the telephony login; the case-tool user; the chat-platform user; the partner's own identifier for a partner worker | Business keys; a person can hold two logins in two tenants, and the record carries both |
| Partner flag | Whether the person is the function's own employee or a partner seat | Partner seats sit on the same record from the first day, so headcount, paid hours and attrition are counted on one basis |
| Unit, client portfolio, cost centre, country, site, time zone | The planning context of the person | Country drives standard hours and consultation rules; the cost centre is kept both as original and as current |
| Supervisor chain | Supervisor, manager, director, function head | Hierarchy as data, not as a spreadsheet that drifts |
| Role, employee type, FTE, status | Agent, team lead, analyst; permanent, temporary, contractor; the FTE fraction; active, on leave, left | FTE is contract hours over the country's standard full-time hours |
| Dates | Hire, class start, nesting start and end, first day on live work, leaving date and reason | The ramp curve and attrition read these; the leaving reason splits voluntary from involuntary |
| Skills and languages | What the person is eligible to handle | Feeds the pool and the placement rules |
| Validity | Effective start, effective end, current flag, change reason | Type 2 history: a change of site or supervisor closes one row and opens another |
The queue record
The queue (contact type) record is the dimension that volume, staffing and service level are read against. Its business key is whatever the routing holds: a contact-type number in the workforce-management system, a telephony routing point, a mailbox, a chat queue. A queue created in the routing without a row here is invisible to planning until someone notices the volume; a queue retired in the routing without closing its row here still carries a forecast.
| Attribute | Holds | Rule |
|---|---|---|
| Queue key, queue id, name | Surrogate key per queue per validity period; the routing's business key; the planning name | The id never changes; the name may |
| Status | Active, dormant, retired, candidate to drop | A dormant queue that still takes volume is a finding, not a status |
| Unit, client, service type | The segment, the client where the queue is client-specific, the product or service | Many clients on one queue is a bridge, not a second row |
| Pool type and pool | Dedicated, designated or shared, and the eligibility pool the queue is served from | See Designated, Shared and Dedicated: What the Words Implement and Pooling Architecture in Service Workforces |
| Channel, routing points | Voice, email, chat, transaction; every routing point that feeds the queue | Many-to-one |
| Forecast group | The forecast group or contact-type set the queue rolls up to | Some queues stand alone; some roll up through a set, which is a bridge |
| Service threshold, service target, short-abandon threshold | The seconds or hours, the percentage, and the abandon exclusion | Per queue, because clients differ; the value is SET LOCALLY and lives on the targets tab |
| Hours of operation and time zone | When the queue is open | Effective-dated |
| Owner seat | The seat that answers for the queue | A queue with no owner is a finding |
| Validity | Effective start, effective end, current flag, change reason | Type 2: a queue consolidation closes rows and opens one, so history is not restated |
The smaller dimensions
Forecast group carries the name, the channel and the set membership; a group that contains several contact types is joined through a bridge. Site carries address, time zone, hours, whether the site is the function's own or a partner's, and the site leader seat, effective-dated. Date carries the calendar and the fiscal calendar, work-day and holiday flags, and the "is current period" flags that reports read. Interval carries the quarter-hour of the day, with the ledger kept in coordinated universal time and local time derived per site. Activity code carries, per code per tenant, the flags the workforce-management system already holds (paid, available, personal, overtime, uses a seat, code type) plus the function's own mapping to a ledger tier and to whether the code counts as workload; see Agent State Taxonomy and Activity Codes. Channel and direction are small, stable lists.
Design rules that hold everywhere
- Every dimension has an unknown member. A fact row whose lookup fails is kept, pointed at the unknown member and counted by a test; it is never dropped silently.
- History is kept with type 2 rows. Each row carries an effective start, an effective end (an open-ended date while current), a current flag and a change reason, so that a person's site on a given day or a queue's threshold in a given month can be read as it was.[3]
- A many-to-many join is a bridge, not a duplicated row. A call that opened three cases is one call; a queue serving twelve clients is one queue.
- A business key is never a surrogate key. The surrogate is generated; the business key is what the source system holds; both are stored.
- Seats, not names. The owner of a dimension is a seat; the steward of each attribute is a seat.
- Products are not named in anything published. A source is a category and an instance label.[4]
Maturity Model Position
Four scales on this wiki use the word level; the launch page states which is which. On the data definitions maturity scale, level 2 has the matrix filled and the person and queue records specified; level 3 has both records built and loaded with history, with every fact joining through them; level 4 runs the unknown-member and orphan tests on every load.
See Also
- Workforce Data Definitions Framework — the hub: the ledger, the definitions and the sources every model computes on
- Source of Truth Design Workbook — the workbook whose Matrix, People and Queues tabs this page specifies
- Source of Truth Register — the ST row each dimension's attributes map to
- Agent State Taxonomy and Activity Codes — the activity-code dimension
- Reconciliation Tests for Time Accounting — the tests that catch failed lookups and orphan time
- Pooling Architecture in Service Workforces — what the pool attribute implements
- Designated, Shared and Dedicated: What the Words Implement — the pool-type attribute
- Human Gates and Number Grades — the grades carried on every row
References
- ↑ Kimball, R. and Ross, M. (2013). The Data Warehouse Toolkit, 3rd ed. Wiley. Chapter 4 (conformed dimensions and the enterprise bus matrix).
- ↑ Kimball, R. and Ross, M. (2013), chapter 4; the matrix is Kimball's "enterprise data warehouse bus matrix".
- ↑ Kimball, R. and Ross, M. (2013), chapter 5 (slowly changing dimension techniques, type 2).
- ↑ West, M. (2011). Developing High Quality Data Models. Morgan Kaufmann. Chapter 2 (entity types, identity and the separation of a thing from the systems that record it).
