Project.Meta_Codes_PMN

by

Project Managers Narrative

Purpose

This view supports a range of portfolio and project management insights, helping decision-makers understand delivery health and emerging risks across all active projects. It transforms Project Manager Narratives (PMNs) into a structured, analysable format, making it easier to identify recurring risks, schedule pressures, and delivery challenges. By standardising how commentary is stored and linked to project data, it enables consistent reporting and trend analysis — while ensuring sensitive or personal information is handled safely and responsibly.

Use Case

  • Owner: Martyn Coles
  • Typical Use Cases:
    • Identify trends in project commentary – Spot recurring themes such as delays, resourcing pressures, or risk causes from PM updates.
    • Strengthen governance and assurance – Track whether project narratives have been reviewed, excluded, or remain eligible for inclusion in reporting, with visibility of last-updated dates.
    • Monitor schedule and status confidence – Align qualitative commentary (e.g. “on track” or “at risk”) with project delivery metrics to highlight potential slippage or capacity issues early.
    • Link narrative to portfolio performance – Combine narrative insights with project master data to provide a richer understanding of delivery challenges and customer impact.
  • Style: Each row represents one attribute of a single project’s PMN entry, typed by META_TYPE. CODE_VALUE holds a canonical code (hashed for long text), with VALUE_METADATA preserving readable payload.

Suggested dashboard insights:

  • Heat-maps showing the volume and focus of project commentary over time.
  • Top recurring factors behind profit erosion, cost growth, or delivery delays.
  • Review coverage and commentary freshness across the portfolio.
  • Alignment between reported schedule health and recent PM narrative tone.

Source

pmo.pmo_PMN
  • Refresh cadence: Weekly (TBC) or aligned to PMN dataflow
  • Load path: Bronze → Silver (aligned PMN) → Gold/CDM (project.meta_codes_pmn)

Integration with Dimension / Reference Tables

Joins used (per SQL):

  • pmo.tsl_bmtprojects — enrich PROJECT_ID with project name for IDENTITY attributes

Common optional joins:

  • cdm.project_core (canonical keys, lifecycle)
  • cdm.employee_core (to map PM to employee, if added later)

Sample Exec

For sample data execution, use the code:

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

Response (Output Schema)

Columns emitted by final SELECT:

Identifiers

  • OBJECT_SEQ: json (varchar) — canonical project key JSON: { "COMPANY_ID": "<code>", "PROJECT_ID": "<id>" } (direct from source columns Company_ID, Project_ID).
  • META_TYPE: varchar — one of: IDENTITY | PMN | COMMENTS | PRINCIPLE | QUALITY | REVIEW | RISK | SCHEDULE | STATUS | CAUSE.

Meta Codes (attributes by META_TYPE)

  • IDENTITY
    • PROJECT_ID — code = project ID; DESC = project name
  • PMN (tables or multi‑field composites hashed)
    • RadarScores, Progress — MD5(…) short hex in CODE_VALUE; DESC = original text
  • COMMENTS
    • Review, CustomerRelationship, Resourcing, Schedule, ProfitOnTrack, PM, CostIncrease, RevenueReduction — hashed from free‑text fields (see SQL), preserved in VALUE_METADATA.DESC
  • PRINCIPLE
    • KeyRisks, KeyOpportunities, KeyVunerabilities — hashed from free‑text
  • QUALITY
    • Modified — last modified date (YYYY‑MM‑DD)
  • REVIEW
    • Excluded, Eligible, Reviewed — boolean flags emitted as 'True'|'False'
  • RISK
    • RiskRegisterUpToDate, HasRiskRegister (DESC carries link), RiskRegisterRequired
  • SCHEDULE
    • ProjectOnSchedule, WillFinishOnSchedule
  • STATUS
    • BelieveData, ProfitOnTrack, Report, Customer, Resource, Reviewed — hashed for long text where applicable; booleans or categorical otherwise
  • CAUSE
    • ProfitReduction, CostIncrease, RevenueReduction, SupplierDriven, InternallyDriven, CustomerDelay — hashed from free‑text causes

Common fields

  • CODE_VALUE: varchar — canonical value (short MD5 hex for long text; raw for booleans/dates)
  • VALUE_METADATA: json (varchar) — compact JSON with at least CODE and optional DESC (sanitised; single quotes replaced by ^).

Audit & Status

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

Keys & Uniqueness

  • Primary (logical) key: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE
  • Grain statement: One row per project narrative attribute (META_TYPE + ATTRIBUTE).
  • De‑duplication rule(s): Prefer most recent CREATE_DATE; use CHECKSUM for change detection.

Data Quality & Rules

  • Mandatory fields: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE
  • Validations:
    • Dates castable to YYYY‑MM‑DD (e.g., Modified)
    • Hashes present when source narrative non‑blank
    • Boolean/text flags only 'True'|'False'
  • Business rules applied:
    • Free‑text narrative hashed → short MD5; original preserved in DESC
    • Sanitise quotes in narrative (' → ^)
  • Assumptions/limitations: Some attributes carry long narrative; consider truncation limits in downstream tools when displaying DESC.

Security & Privacy

  • PII/Commercial sensitivity: Narrative may include sensitive commercial or client references. Treat as Internal/Confidential. Display of VALUE_METADATA.DESC should respect project access controls.
  • Access control: Restrict by project/PRU; consider row‑level filtering on COMPANY_ID and project ownership.
  • Retention: Align to PMO record retention (TBC).

Dependencies

  • Upstream: pmo.pmo_PMN, optional pmo.tsl_bmtprojects
  • Downstream: PMO dashboards (narrative trends, risk/issue theming), programme reviews, portfolio status packs

SQL Code & Artefacts

BMT-DWH-DEV/Project_Meta_Codes_PMN.sql

Leave a Comment