Person.Meta_Codes_SkillsBase

by

Purpose

This view supports workforce capability management and planning by providing consistent, up-to-date insight into employee skills, certifications, and team structures. It enables HR, project managers, and business leaders to make better decisions about deployment, development, and compliance.

Use Case

  • Owner: Simon Mathieson
  • Typical Use Cases:
    • Understand workforce capability – View skill coverage across teams, locations, and domains to identify strengths and development areas.
    • Monitor compliance and certification validity – Track certification status and identify employees due for renewal or reassessment.
    • Support resourcing and project planning – Match people to projects based on skill, role, or location for optimal team composition.
    • Ensure data accuracy – Compare SkillsBase and IFS records to maintain a reliable and complete picture of workforce data.
  • Style: Each row is an employee coded attribute (meta code) with supporting VALUE_METADATA JSON. CDS object/meta_codes structure.

Suggested dashboard insights:

  • Skills coverage and gaps by PRU, team, or location.
  • Certification currency and upcoming expiries.
  • Skills adoption and completeness by business unit.
  • Workforce capability trend over time (self-assessment vs supervisor assessment).

Source

Primary Source(s):

skl.people
  • IFS identity & employee lookups:
    • ifs.QDIM_FND_USER_PROPERTY_OL (SMTP_MAIL_ADDRESS)
    • ifs.QDIM_FND_USER_OL (user, active)
    • ifs.QDIM_PERSON_INFO_OL (PERSON_ID ↔ USER_ID)
    • ifs.DIM_EMPLOYEE_OL (COMPANY, CODE as EMP_NO)
  • Reference tables:
    • skl.locations
    • skl.teams

Load Path

SkillsBase API (2FactAuth) → (ADF Pipeline) → Bronze (skl.*) → Silver (employee.meta_codes_skillsbase)

  • Refresh cadence: Daily (TBC)

Integration with Dimension / Reference Tables

Joins used (per SQL):

  • ifs.QDIM_FND_USER_PROPERTY_OL + ifs.QDIM_FND_USER_OL — resolve email address → identity (only SMTP_MAIL_ADDRESS, active users)
  • ifs.QDIM_PERSON_INFO_OL — identity → PERSON_ID
  • ifs.DIM_EMPLOYEE_OL (aggregated by PERSON_ID) — derive COMPANY, EMP_NO
  • skl.locations — enrich LOCATION_ID → location.name
  • skl.teams — enrich TEAM_ID → team.name

Common optional joins:

  • cdm.employee_core (object canonicalisation, employment status)
  • ref.skills_taxonomy (friendly skill/cert labels) — TBC

Sample Exec

For sample data execution, use the code:

EXEC get.myView @dataMart = 'Person', @viewName = 'Meta_Codes_SkillsBase', @token = '<myToken>', @version = '<202404>'

Response (Output Schema)

Columns emitted by final SELECT:

Identifiers

  • OBJECT_SEQ:json (varchar) — canonical employee key JSON: { "COMPANY_ID": "<int>", "PERSON_ID": "<code>" }
    • COMPANY_ID resolved from ifs.DIM_EMPLOYEE_OL.COMPANY when available; otherwise mapped from email domain using the following mapping in SQL:
      • apac.bmt.org → 4009; bmt.org → 1062; bmtglobal.com → 1062; ca.bmt.org → 5013; uk.bmt.org → 1072; us.bmt.org → 5088; bmtfleet.onmicrosoft.com → 1062; apac-sec.bmt.org → 3037; else 9900.
    • EMP_NO from ifs.DIM_EMPLOYEE_OL.CODE; fallback 9900000 + skl.people.id.
  • META_TYPE: varchar — constant 'SKILLSBASE'.

Meta Codes (attributes)

  • ATTRIBUTE:varchar — one of:
    • DOMAIN
    • EMAIL
    • ID
    • last_self_assessment
    • last_supervisor_assessment
    • LOCATION_ID
    • ROLE_ID (array string stripped of brackets/whitespace; NULL when empty)
    • TEAM_ID
  • CODE_VALUE: varchar — attribute value (normalised/cast as in SQL).
  • VALUE_METADATA: json (varchar) — JSON-ish payload with keys typically CODE and DESC, e.g. for EMAIL: { "CODE": "<PERSON_ID>", "DESC": "<email>" }. Single quotes from source content are replaced with ^.

Audit & Status

  • CREATE_DATE: date — GETDATE() cast to date at load time.
  • EXPIRY_DATE: date — constant '2099-12-31'.
  • SOURCE_SYSTEM: varchar — constant {"Source":"skl.people"}.
  • ACTIVE_FLAG: int — constant 1 (active).
  • CHECKSUM: nvarchar(32) — MD5 of (OBJECT_SEQ, ATTRIBUTE, CODE_VALUE) (hex string).

Keys & Uniqueness

  • Primary (logical) key: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE (plus EXPIRY_DATE if required for bitemporal patterns)
  • Grain statement: One row per employee coded attribute (per ATTRIBUTE value).
  • De‑duplication rule(s): Prefer highest CREATE_DATE; checksum can be used to detect duplicates/incremental changes.

Data Quality & Rules

  • Mandatory fields: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE, VALUE_METADATA
  • Validations:
    • Email domain → company mapping resolves to a known code or 9900 (Other)
    • ROLE_ID: treat [], empty, or whitespace as NULL
    • last_*_assessment values cast to DATE (YYYY‑MM‑DD)
    • LOCATION_ID/TEAM_ID numeric cast succeeds
  • Business rules applied:
    • Normalise string content; replace ' with ^ inside VALUE_METADATA
    • Derive EMP_NO fallback as 9900000 + skl.people.id
  • Assumptions/limitations: No explicit OBJECT_CLASS/OBJECT_CODE columns — carried inside OBJECT_SEQ JSON for this view.

Security & Privacy

  • PII/Commercial sensitivity: Contains emails and employee identifiers → Confidential.
  • Access control: Restrict to authorised HR/reporting roles; consider row‑level filtering by derived COMPANY_ID.
  • Retention: Align with HR data retention policy (TBC).

Dependencies

  • Upstream:
    • skl.people,
    • ifs.QDIM_FND_USER_PROPERTY_OL,
    • ifs.QDIM_FND_USER_OL,
    • ifs.QDIM_PERSON_INFO_OL,
    • ifs.DIM_EMPLOYEE_OL,
    • skl.locations,
    • skl.teams
  • Downstream: Capability dashboards, HR compliance, project resourcing, employee master views

SQL Code & Artefacts

BMT-DWH-DEV/Person_Meta_Codes_SkillsBase.sql

Leave a Comment