Release 96

by

A strong week of structural improvement, model refinement, and practical fixes across the Common Data Model. The focus has been on better relationship modelling, clearer taxonomy, stronger export readiness, and higher-quality project transaction data.

🚀 Major Enhancement: New Bridge Table Type Introduced

The new Bridge table type has now been extended into production-ready use cases, enabling cleaner handling of one-to-many relationships that previously sat awkwardly inside arrays or compressed attributes.

New Views

  • employee_bridge_info_skillsbase_quals
  • employee_bridge_roles_skillsbase

What This Delivers

Multiple Qualifications per Employee

Qualifications from SkillsBase can now be represented properly as separate rows rather than compressed values.

Role Array Breakout

The previous single array field:

  • employee_meta_codes_skillsbase [roles]

has now been normalised into a structured bridge model, allowing:

  • multiple roles per employee
  • better filtering
  • cleaner reporting joins
  • simpler downstream analytics

This is a significant maturity step in employee relationship modelling.

👤 New Person Domain Extended

New View

  • person_meta_codes_UKS

Collateral Enhancement

  • employee_meta_codes_UKS

Why It Matters

The first edition of the Person layer begins the separation of:

  • the human identity (person)
  • the employment relationship (employee)

This is an important strategic direction for identity management, future HR modelling, and multi-assignment scenarios.

💰 Project Value Views – Correctness First

Views Updated

  • project_item_values_cost
  • project_item_values_revenue

Change

Reverted cvw.project_item_values back to mvw due to unexplained omissions in DEV data.

Reasoning

When data completeness is uncertain, correctness takes priority over abstraction. A rebuild of the MDM layer is planned, particularly following the KEYREF logic improvements below.

🏢 PRU Code Reliability Improvement

Views Updated

  • project_meta_codes_UKS
  • project_core_details_UKS

New Logic

PRU_CODE = COALESCE(src.CODE_E, CATEGORY1_ID)

Outcome

Where CODE_E was missing, fallback logic now preserves PRU classification using CATEGORY1_ID.

Improved resilience with no reporting disruption.

📦 Export View Naming Standard Introduced

Several export-oriented views have moved away from the misleading core_* naming convention.

Renamed Views

  • busopp_core_dates_export → busopp_export_dates
  • busopp_core_meta_export → busopp_export_meta
  • feedback_core_meta_export → feedback_export_meta
  • global_core_details_export_company → global_export_details_company
  • employee_core_meta_export → employee_export_meta
  • project_core_dates_export → project_export_dates
  • project_core_meta_export → project_export_meta
  • customer_core_details_export → customer_export_details

Why This Matters

These are not traditional Core tables.

They are:

  • serverless-friendly
  • document-style outputs
  • ideal for Cosmos DB
  • suitable for AI / ML pipelines
  • useful for Graph / API consumption

This naming now reflects purpose rather than legacy structure.

👥 Roles Reclassified as First-Class Metadata

Renamed Views

  • project_meta_codes_roles → project_meta_roles
  • project_meta_codes_roles_APC → project_meta_roles_APC
  • project_meta_codes_roles_CAD → project_meta_roles_CAD
  • project_meta_codes_roles_UKS → project_meta_roles_UKS
  • project_meta_codes_roles_USD → project_meta_roles_USD

What Changed

Roles are no longer treated as ordinary codes.

They now have their own metadata class supporting:

  • role name
  • linked person
  • email references
  • source assignment references
  • multiple associations

This enhances Roles as relationships, not static codes.

📅 Events Promoted to Dedicated Structure

Renamed Views

  • invoice_item_dates_stage → invoice_event_stage
  • order_item_dates_stage → order_event_stage

Why

Events contain richer lifecycle meaning than simple dates.

Examples:

  • raised
  • approved
  • dispatched
  • invoiced
  • closed

Creating an explicit Event class makes lifecycle data easier to understand and consume.

🪪 Identity Bridge Improvements

Renamed Views

  • identity_bridge_entra → identity_bridge_info_entra
  • identity_bridge_payroll → identity_bridge_info_payroll_UKS
  • identity_bridge_personid → identity_bridge_info_personid_UKS

Additional Enhancement

Bridge tables now use structured row sequencing:

SEQ_NO = RIGHT('0000' + CONVERT(VARCHAR(4),
ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY attribute.value)),4)

Benefit

Allows checksum logic to continue working while supporting multiple identities per person.

🔧 KEYREF Duplicate Prevention Upgrade

Views Updated

  • project_item_values_revenue_UKS
  • project_item_values_time
  • project_item_values_cost_UKS
  • project_item_codes_UKS
  • project_item_values_contingency

Enhancement

Additional key condition added:

CONCAT_WS('|', src.KEYREF4, src.KEYREF3)

Why

Duplicates were being created where currency values occupied the KEYREF3 position.

This change strengthens uniqueness and improves transactional integrity.

📈 Strategic Summary

This week’s release shows a clear architectural trend:

From Flat Tables → Structured Relationships

Through new:

  • Bridge types
  • Event types
  • Role classes
  • Person identity modelling

From Legacy Naming → Purposeful Taxonomy

Through clearer:

  • export naming
  • role metadata
  • event semantics

From Raw Data → Trusted Data Product

Through:

  • duplicate prevention
  • fallback logic
  • cautious rollback where data confidence was low

💬 Feedback Source Field Change Successfully Absorbed

View Updated

  • feedback.meta_codes_response

Change Summary

Following notification of an upstream source change, the feedback text source column has moved from:

  • Comments
  • to:
  • FeedbackText (tsl_feedbacktext)

Reporting Impact

No downstream impact.
The reporting attribute remains unchanged as:

  • COMMENT

This means reports, models, dashboards, and dependent logic continue to function without amendment.

Leave a Comment