Employee Meta Codes

by

Overview

The Employee Meta Codes view provides a structured, extensible attribute layer for employee objects within the Common Data Model (CDM).

It transforms employee-related attributes into a standardised code-based format, enabling consistent classification, filtering, and reporting across all employee-driven use cases.

This view extends employee.core_details by introducing contextual attributes grouped by domain, without compromising the stability of the core identity model.

Purpose

  • Provide a consistent and scalable structure for employee attributes
  • Enable cross-domain reporting and slicing (e.g. job, organisation, assignment)
  • Standardise attribute representation using:
    • META_TYPE
    • ATTRIBUTE
    • CODE_VALUE
    • VALUE_METADATA
  • Support governed, metadata-driven reporting across the DataMart

Key Features

1. Structured Attribute Model

Each attribute is represented as a row using:

  • META_TYPE → Logical grouping (e.g. ASSIGNMENT, JOB, ORGANISATION)
  • ATTRIBUTE → Specific attribute name (e.g. STATUS, GRADE, LOCATION)
  • CODE_VALUE → Standardised system value (often normalised or hashed)
  • VALUE_METADATA → JSON payload with:
    • CODE → Stored value
    • DESC → Human-readable description

This enables a long, flexible schema that avoids column sprawl.

2. Domain-Based Meta Grouping

Attributes are organised into meaningful business domains:

  • ASSIGNMENT
    • Status, work location, assignment pattern, line manager
  • JOB
    • Job family, category, grade, job ID, title (hashed)
  • ORGANISATION
    • Organisation hierarchy, codes, contractor classification
  • EMPLOYMENT
    • Agreement type, contract type, leaving reason
  • EMAIL
    • Email link key (hashed), UPN (where different)
  • KEY_CODE
    • Core identity linkage (e.g. PERSON_UID)

This grouping ensures semantic clarity and reporting alignment.

3. Identity & Relationship Enrichment

  • Introduces line manager relationships using:
    • Hashed email as the linking mechanism
    • Embedded metadata including PERSON_ID and email
  • Separates identity keys (KEY_CODE) from descriptive attributes
  • Aligns with the broader Person / Entra identity strategy

4. Secure Handling of Sensitive Data

  • Email and job title values are hashed where required
  • Prevents exposure of sensitive or inconsistent free-text values
  • Maintains linkability without compromising security

5. Validity-Aware Attribute Selection

  • Uses date-effective filtering:
    • VALID_FROM / VALID_TO
    • PRIMARY flags for assignment and job
  • Ensures only current and relevant attribute states are presented

6. Multi-Source Integration

Combines multiple IFS sources into a unified structure:

  • QDIM_COMPANY_PERSON_ALL_CF_OL
  • QFACT_EMP_JOB_ASSIGN_CFV_OL
  • QDIM_COMPANY_PERS_ASSIGN_C_OL
  • DIM_EMPLOYMENT_OL
  • Supporting lookup tables (organisation, job families, users)

All normalised into a single, consistent output model

7. Metadata-Driven Construction

  • Built using CTEs + CROSS APPLY patterns
  • Enables:
    • Dynamic attribute generation
    • Consistent transformation logic
    • Easy extension without structural redesign

Design Principles

  • Separation from Core
    • No identity logic is defined here
    • Relies entirely on OBJECT_SEQ from core_details
  • Long-form over wide-form
    • Attributes remain row-based
    • Pivoting is deferred to reporting layers
  • Code-first modelling
    • All attributes represented as codes with descriptions
    • Supports consistency across regions and systems
  • Extensibility by design
    • New attributes added via META_TYPE and ATTRIBUTE
    • No schema change required

Usage Guidance

  • Use this view when:
    • Filtering employees by attributes (e.g. grade, location, status)
    • Building organisational or workforce reports
    • Joining attribute context onto employee or resource datasets
  • Recommended pattern:
    • Join via OBJECT_SEQ
    • Use CODE_ATTRIBUTE (META_TYPE + '_' + ATTRIBUTE) for pivoting
  • Do not:
    • Duplicate attributes into core views
    • Assume one-to-one relationships between attributes
    • Expand or reinterpret identity fields

What This View Is Not

  • Not a transactional dataset
  • Not time-series history (see meta_dates for temporal context)
  • Not a flattened reporting structure

It is a classification and context layer, not a fact table.

Summary

employee.meta_codes provides a powerful, flexible, and governed attribute framework for employee data.

It enables consistent classification across domains such as job, assignment, organisation, and employment, while maintaining strict separation from identity and transactional layers.

This view is essential for:

  • Slicing and grouping employee populations
  • Driving dimensional reporting
  • Supporting scalable, metadata-driven analytics

Leave a Comment