Employee Meta Dates

by

Overview

The Employee Meta Dates view provides a structured temporal layer for employee objects within the Common Data Model (CDM).

It captures key lifecycle dates associated with employees and presents them in a standardised attribute format, aligned to the CDM meta pattern.

This view complements:

  • employee.core_details → identity
  • employee.meta_codes → classification
  • employee.meta_dates → time context

Purpose

  • Provide date-based context for employee lifecycle events
  • Enable consistent temporal filtering and reporting
  • Standardise date attributes using the CDM meta structure
  • Support alignment of workforce timelines across domains

Key Features

1. Structured Date Model

Each date is represented as a row using:

  • META_TYPE → Context grouping (e.g. EMPLOYMENT)
  • ATTRIBUTE → Date type (e.g. EMPLOYMENT_DATE, LEAVING_DATE)
  • CODE_VALUE → ISO-formatted date (YYYY-MM-DD)
  • VALUE_METADATA → JSON payload containing the date value

This ensures consistency with the wider meta_ framework*.

2. Employment Lifecycle Coverage

The view currently captures:

  • EMPLOYMENT_DATE → Start of employment
  • LEAVING_DATE → End of employment

These form the core lifecycle boundaries for an employee.

3. Current-State Filtering

  • Records are constrained to active employment periods: GETDATE() BETWEEN DATE_OF_EMPLOYMENT AND DATE_OF_LEAVING
  • Ensures:
    • Only currently valid employment records are returned
    • Alignment with other “current-state” CDM views

4. Standardised Date Formatting

  • Dates are normalised to: YYYY-MM-DD
  • Ensures compatibility across:
    • Reporting tools (Power BI)
    • APIs and JSON payloads
    • Cross-region datasets

5. Lightweight, Focused Design

  • Intentionally minimal:
    • Only true lifecycle dates are included
  • Avoids duplication of:
    • Derived dates
    • Reporting-specific time logic

This keeps the view clean, reliable, and extensible.

Design Principles

  • Separation of concerns
    • Dates are isolated from codes and values
    • Prevents mixing classification with time context
  • Object alignment
    • All records link via OBJECT_SEQ
    • Fully dependent on employee.core_details
  • Temporal consistency
    • Dates reflect valid employment state, not historical snapshots
  • Extensibility
    • Additional date groups (e.g. ASSIGNMENT, CONTRACT) can be introduced via new META_TYPEs

Usage Guidance

  • Use this view when:
    • Analysing employee lifecycle timelines
    • Filtering active workforce populations
    • Aligning employee dates with:
      • Projects
      • Absence
      • Financial or utilisation data
  • Recommended pattern:
    • Join via OBJECT_SEQ
    • Pivot using META_TYPE + ATTRIBUTE if required
  • Do not:
    • Derive complex time logic here
    • Expect historical tracking (this is current-state only)

What This View Is Not

  • Not a full employment history view
  • Not a transactional or event timeline
  • Not a reporting-ready wide date table

It is a controlled temporal reference layer.

Summary

employee.meta_dates provides a clean and standardised representation of employee lifecycle dates, enabling consistent temporal analysis across the Data Warehouse.

It ensures that time-based context is applied consistently, without compromising the stability of identity (core_details) or classification (meta_codes).

Leave a Comment