Release 89

by

This release focuses on improving the reliability, regional consistency, and future-proofing of project and business opportunity metadata.

Key improvements include a redesigned approach to project role handling, introducing dedicated role views and a new email-based identifier aligned with the emerging Person domain. Several legacy defects were corrected, including misclassified company identifiers, invalid JSON payloads affecting API exports, and historical inconsistencies in opportunity metadata.

We also resolved a critical naming collision in the Feedback domain by clarifying customer versus owner email fields, ensuring stable checksums and safer cross-domain joins. Change-date handling has been standardised across SharePoint and file-based sources to strengthen auditability and historical tracking.

Finally, a set of technical view renames was applied to support regionalisation and improve clarity of data origin.
Overall, this release strengthens data integrity, supports multi-region reporting, and reduces risk to downstream consumers.

1. Project Roles Review

Views affected

  • project_meta_codes_UKS
  • project_meta_codes_UKS_archive
  • project_meta_codes_APC
  • project_meta_codes_roles_APC
  • project_meta_codes_roles
  • project_meta_codes
  • project_meta_codes_roles_UKS
  • project_meta_codes_USD
  • project_meta_codes_roles_CAD
  • project_meta_codes_roles_USD

Change

  • Role attributes have been removed from the main project_meta_codes view and rebuilt in dedicated *_roles views.
  • Role data is now constructed bottom-up from the original project.roles assignment logic.

Key design decision

  • EMAIL is now the primary identifier for project roles.
  • This is derived as: LEFT(8, HASH(normalised_email))

Rationale

  • Removes dependency on PERSON_ID, enabling support for regional project managers.
  • Aligns with the future Person domain design.
  • Provides consistent cross-regional role identity.

Known limitation

  • CAD and USD role data has been inferred from name-only sources.
  • Where a full name is not available:
    • EMAIL will be synthetic/false ⚠️
    • The original name is preserved in NAME

2. Company Identifier Correction (BusOpp_APC)

Views affected

  • busopp_item_values_APC
  • busopp_meta_codes_APC
  • busopp_meta_dates_APC

Change

  • Corrected legacy bug where:
    • OBJECT_CLASS = COMPANY
    • instead of OBJECT_CLASS = COMPANY_ID

Impact

  • Restores consistency with the Common Data Model.
  • Prevents misclassification of company identifiers.

3. Export View JSON Encoding Fix

Views affected

  • busopp_core_meta_export
  • project_core_meta_export

Change

  • Fixed JSON encoding bug causing API failures.
  • Date and HASHBYTES values are now encoded as stable VARCHAR.

Impact

  • API payloads now validate correctly.
  • Export endpoints are stable again.

4. Historical BusOpp Metadata Fixes

busopp_meta_dates_UKS_archive

Issue

  • OPPORTUNITY had been altered to PROPOSAL in the main view, affecting history.
  • Dates were split between PROPOSAL and DELIVERY.

Fix

  • Restored consistent attribute handling for historical records.

busopp_meta_codes_UKS_archive

Issue

  • ROUTE_TO_MARKET JSON payload was missing a structural comma.
  • Resulted in invalid JSON.

Fix

  • Corrected JSON structure.
  • Payload now passes encoding tests.

5. Feedback Domain – EMAIL Naming Collision

Views affected

  • feedback_meta_codes_customerthermometer
  • feedback_meta_codes_customervoice
  • feedback_meta_codes_legacyexternal
  • feedback_meta_codes_legacyinternal
  • feedback_meta_codes_requests
  • feedback_meta_codes_response

Issue

  • Naming collision between:
    • CUSTOMER.EMAIL
    • OWNER.EMAIL

Because:

  • META_TYPE is not part of the checksum logic.
  • Both collapsed to the same attribute identity.

Decision / Fix

  • OWNER.EMAIL remains unchanged.
  • CUSTOMER.EMAIL renamed to:
    CUSTOMER.EMAIL_ADDRESS

Rationale

  • OWNER.EMAIL aligns with EMPLOYEE.EMAIL in other domains.
  • CUSTOMER.EMAIL_ADDRESS is treated as a reference/contact field.

Impact

  • Breaking change for consumers using CUSTOMER.EMAIL.
  • Semantics unchanged; only the attribute name has changed.

6. CREATE_DATE Standardisation

APC (SharePoint List)

Views affected

  • customer_core_details_APC
  • project_meta_dates_APC
  • project_meta_dates_APC_archive
  • busopp_core_details_APC

Change

  • CREATE_DATE now sourced from SharePoint list [createdon].

Benefit

  • Improves change history traceability.
  • Provides reliable lineage for SharePoint-derived data.

NA & EUR (Source File-based MP8/9)

Views affected

  • busopp_core_details_EUR
  • busopp_core_details_NA
  • busopp_item_values_EUR
  • busopp_item_values_NA
  • busopp_meta_codes_EUR
  • busopp_meta_codes_NA
  • busopp_meta_dates_EUR
  • busopp_meta_dates_NA

Change

  • CREATE_DATE now derived from file name using:
    • FY_PERIOD_START_DATE

Benefit

  • Provides consistent and auditable historical tracking for file-based ingestion.

7. Project Baseline Metadata Enhancement

View affected

  • project_meta_dates_UKS

Change

  • Added structured BASELINE metadata, including:
    • Revision date
    • Revision number
    • Planned values update history
    • User comment

Impact

  • Baseline changes are now explicitly traceable.
  • Supports audit and comparison use cases.

8. BusOpp Technical Renaming (Regionalisation)

Views affected

  • busopp_core_details_UKS_archive
  • busopp_core_details_archive
  • busopp_item_values_lines
  • busopp_item_values_lines_UKS
  • busopp_meta_codes
  • busopp_meta_codes_UKS
  • busopp_meta_codes_archive
  • busopp_meta_codes_UKS_archive
  • busopp_meta_dates
  • busopp_meta_dates_UKS
  • busopp_meta_dates_archive
  • busopp_meta_dates_UKS_archive

Change

  • Technical rename of views to follow regionalised naming conventions.

Purpose

  • Improves clarity of data origin.
  • Aligns with multi-region architecture.
  • Reduces ambiguity between global and regional datasets.

Leave a Comment