DIM_EMPLOYEETS Employee Dimension
Type-2 SCD employee dimension in DATASTORE.MART that standardizes employee history, hierarchy context, and downstream reporting joins across enterprise domains.
Overview
Enterprise employee master dimension that tracks historical changes and provides stable join context for analytics, payroll, and workforce reporting.
Business Purpose
Ensure cross-domain reporting uses a consistent employee truth set with governed history, hierarchy alignment, and repeatable daily maintenance controls.
Technology Stack
Business Impact
Defects in this dimension can cascade into incorrect workforce metrics, broken historical joins, and downstream reporting inaccuracies across multiple domains.
Grain
One row per employee SCD version (DOMAINID plus RECORDVERSION).
Daily Orchestration
Two-task chain starts at 05:00 ET via TASK_CHECK_EMPLOYEEDIMENSION.
Primary Procedures
SP_CHECK_EMPLOYEE_DIMENSION validates integrity before SP_UPDATE_EMPLOYEE_DIMENSION writes.
SCD Behavior
ROW_HASH-driven Type-2 history with current row enforcement and version chaining.
Pipeline sequence at 05:00 ET:
- TASK_CHECK_EMPLOYEEDIMENSION calls SP_CHECK_EMPLOYEE_DIMENSION.
- Integrity checks validate current-record uniqueness, contiguous versions, and date validity.
- On success, TASK_UPDATE_EMPLOYEEDIMENSION triggers SP_UPDATE_EMPLOYEE_DIMENSION.
- Update procedure performs hash-based change detection, expiry, insert, collision handling, and logging.
Error routing uses SF_REPORTING_TASKERROR_NOTIFICATION_INT on the root task, and child-task failures are covered through task-graph inheritance.
- STARTDATE and ENDDATE bound each version; ENDDATE 9999-12-31 marks the current open row.
- CURRENTRECORD = TRUE identifies the active version and is enforced as one non-deleted current row per DOMAINID.
- RECORDVERSION increments for each changed version within a DOMAINID chain.
- ROW_HASH (SHA2-256) across normalized business attributes triggers version rollover.
- Same-day collisions are updated in place when DATE(UPDATEDDT) equals current STARTDATE.
- CHANGE_NOTES stores field-level change summaries for inserted versions.
ENTRYID is now generated using table-column AUTOINCREMENT; SEQ_DIMEMPLOYEETS remains only for historical reference.
| Source | Usage |
|---|---|
| ENGAGEHR.ENGAGEHR.GETEMPLOYEES | Primary HR attributes for status, org, role, manager, location, and dates. |
| ENGAGEHR.ENGAGEHR.GETWORKLOCATIONS | Country name lookup from work location code. |
| PROQOR.DBO.VWEMPLOYEESUBUNITEXTENDED_OPTIMIZED | Latest-record ISTESTEMPLOYEE filtering and EMPLOYEEID retention in staging only (v19+). |
| DATASTORE.MART.DIM_EMPLOYEETS (current) | Carry-forward for ProQor org/title attributes, WAH/SMS fields, and terminated hierarchy paths. |
Recent behavior updates:
- v18 removed RESOURCEMONITORING.SMS join and now carries SMS-related fields from current dimension row.
- v19 uses ProQor only for test-employee filtering and latest EMPLOYEEID staging, while org/title fields are carried forward.
Consumer Footprint
DIM_EMPLOYEETS is referenced by production procedures, tables, and views across these domains:
Join patterns include current-record joins and Type-2 date-range matching depending on reporting use case.
Business outcomes: consistent employee-history joins across reporting domains, fewer downstream reconciliation issues, and improved confidence in workforce, payroll, and operations metrics.