Release 63

by

🆕 What’s New

New Project View (Item 207)

Added project_details_plus_APC to complete the set for regional project reporting.

Improved Date Views (Items 20–21, 27–30, 204–205)

  • Enhanced performance and maintainability of date views using a single CTE-based query structure.
  • Consolidated logic into the main DataMart framework, replacing ad hoc scripts.
  • Standardised naming conventions (e.g. Dates_DIM_DATE → dates_dim_date).
  • Improved period handling and calendar support.

Programme ID Enhancements (Items 206, 208)

Integrated clearer programme identification fields from MIS80 into global organisation views, allowing for cross regional identification.

🔂Development/Improvements for cdm

  • Smarter Timestamps (Items 1–8)
    We’ve updated how we capture creation dates in our SQL to use CAST(GETDATE() AS DATE). This change supports integration with our get.myArchive functionality.
  • Metadata-Driven Engine (Item 9)
    Refactored logic now supports metadata-driven design – making it easier to manage triggers, logging, and indexing automatically.
  • Modern Upserts (Items 10–13, 15, 17–19)
    Legacy “upsert” statements have been replaced or updated to follow our new standard. This includes:
    • Support for CODE_VALUE formatting
    • New patch, put, and post versions
    • More consistent handling of data payloads
  • Standard Templates (Item 14)
    Introduced source table definitions for core, meta, and item views – part of standardising our design approach.
  • New Shortcode Controller (Item 16)
    Created a new controller pattern for shortcodes, now organised within a dedicated schema: [uspert].
  • Utility Functions (Items 22–23)
    Added helpful utilities such as countrow_view and whos_active to support monitoring and debugging.

Notes

The following files were updated in this release:

  1. ⚠️ DIFFERENT: customer_core.sql
  2. ⚠️ DIFFERENT: employee_codes.sql
  3. ⚠️ DIFFERENT: employee_core.sql
  4. ⚠️ DIFFERENT: project_codes.sql
  5. ⚠️ DIFFERENT: project_core.sql
  6. ⚠️ DIFFERENT: project_dates.sql
  7. ⚠️ DIFFERENT: supplier_core.sql
  8. ⚠️ DIFFERENT: employee_dates.sql
  9. ⚠️ DIFFERENT: create_table.sql
  10. ⚠️ DIFFERENT: create_table_meta.sql
  11. ⚠️ DIFFERENT: patch_upsert.sql
  12. ⚠️ DIFFERENT: post_insert.sql
  13. ⚠️ DIFFERENT: put_update.sql
  14. ⚠️ DIFFERENT: table_template.sql
  15. ⚠️ DIFFERENT: upsert_entity.sql
  16. ⚠️ DIFFERENT: upsert_log_table_list.sql
  17. ⚠️ DIFFERENT: upsert_meta.sql
  18. ⚠️ DIFFERENT: upsert_meta_insert.sql
  19. ⚠️ DIFFERENT: upsert_meta_update.sql
  20. ⚠️ DIFFERENT: Dates_DIM_DATE.sql
  21. ⚠️ DIFFERENT: dates_DIM_DATE.sql
  22. ⚠️ DIFFERENT: countrow_view.sql
  23. ⚠️ DIFFERENT: whos_active.sql
  24. ⚠️ DIFFERENT: patch_myview.sql
  25. ⚠️ DIFFERENT: myarchive.sql
  26. ⚠️ DIFFERENT: myview.sql
  27. ⚠️ DIFFERENT: dates_holidays_uk.sql
  28. ⚠️ DIFFERENT: dates_mydate.sql
  29. ⚠️ DIFFERENT: dates_period.sql
  30. ⚠️ DIFFERENT: dates_DIM_DATE_o.sql
  31. ⚠️ 204. DIFFERENT: dates_DIM_DATE_202409.sql
  32. ⚠️ 205. DIFFERENT: Dates_DIM_DATE 202409.sql
  33. ⚠️ 206. DIFFERENT: global_organisation_ifs.sql
  34. ⚠️ 207. DIFFERENT: project_details_plus_APC.sql
  35. ⚠️ 208. DIFFERENT: global_organisation_202409.sql

Retired Code & Views (31–203)

A wide set of legacy code and views have been reviewed and retired. This includes:

  • External Tables in Operational Layer (OL) Views: e.g. DIM_PROJECT_OL, FACT_CUSTOMER_INVOICE_OL, FACT_EMPLOYEE_ANALYSIS_OL
  • Redundant Domain Structures: Project, Customer, Employee, and Date summary/detail/label views no longer in use.
  • Obsolete Upsert & Table Generator Scripts: Older versions of table creation and insert/update scripts.
  • Stale metadata/snippet logic and compiled artefacts: Superseded by metadata-driven framework and removed to reduce complexity.

🟢 Note: All retired views and scripts were non-essential to the stability and operation of the 202409 Apps10 solution, which remains the current and target stable deployment.

Leave a Comment