Release 67

by

🗂️ Summary by Change Type

Many of the changes in this release were introduced to support the dynamic generation of Common Table Expressions (CTEs) as part of the myBMT auto-creation logic. As such, the majority were structural or naming updates to improve integration and metadata alignment.

There were limited functional changes, namely:

  • employee_meta_codes – reordering of PAYROLL to support UNION logic
  • project_details_USD and project_details_plus_USD – improved logic for CUSTOMER_ID using fallback hashing
  • employee_hours_202409 – version increment to 202410 with added alias to support OPENROWSET identification
Change TypeExample Views
Renamed _core_detailemployee_core_detail, busopp_core_detail
Prefixed _metaemployee_codes, project_codes
New Views Introducedemployee_item_values_expense, supplier_item_values_invoice
Legacy View for Stabilitycustomer_thermometer_202409
Increment/Version Revemployee_hours_202409 → _202410
Technical Fix / Cleanupcustomer_orderline_202404 alias fix, supplier_item_dates_invoice field removal

🔄 Cross-Cutting Theme: Integration and AI Readiness

Many views have been prefixed or renamed to support integration with AI models and automated metadata-driven pipelines:

  • Prefixed _meta to standardise naming across domains (e.g. busopp_codes → busopp_meta_codes)
  • Renamed _core_detail views to improve clarity and consistency (e.g. busopp_core_detail, employee_core_detail, supplier_core_detail)

👤 Employee Domain Updates

Enhanced Metadata Structure & View Expansion:

  • employee_meta_codes: Reordered 'PAYROLL' AS META_TYPE to support cleaner UNION logic.
  • employee_meta_codes, employee_meta_dates, employee_meta_values: Prefixed _meta for standardisation.
  • employee_core_detail: Renamed for consistency.
  • New Views for Expenses and Absence:
    • employee_item_dates_expense: New view for expense-specific date tracking.
    • employee_item_codes_expense, employee_item_values_expense: Added for granular expense item tracking.
    • employee_item_codes_absence, employee_item_dates_absence, employee_item_values_absence: Introduced with removal of Object Source field to prep for CTE automation.
  • employee_hours_202409: Incremented to 202410 with alias added to support OPENROWSET.

💼 BusOpp Domain Enhancements

Full Meta Layer Adoption:

  • All major views updated: busopp_core, busopp_core_detail (renamed), busopp_dates, and dated variants.
  • busopp_codes, busopp_dates, busopp_values: Prefixed _meta for AI/ETL readiness.
  • Consistent implementation of meta_codes, meta_dates, meta_values.

🏗️ Project Domain Expansion

Significant Development in Value Tracking:

  • project_meta_values_ series:
    • Includes views for cost, revenue, hours, expenses, forecast, contingency, and time.
    • These are precursors to unified item_values logic, built on internal CTEs (e.g. Project_Totals).
  • project_values_ series:
    • Mirrors the above meta series but outputs value summaries.
    • Will evolve as item_values model matures.

Additional Updates:

  • project_codes, project_meta_codes, project_meta_dates: Aligned for meta-prefix consistency.
  • project_core_detail: Renamed.
  • project_meta_template: Created to scaffold auto-CTE logic (supporting future automation).
  • project_details_USD, project_details_plus_USD: Aligned CUSTOMER_ID logic using COALESCE and hashed fallbacks.

👥 Customer Domain Refinements

  • customer_core_detail: Renamed to match integration naming pattern.
  • customer_orderline_202404: Fixed inconsistent field aliasing (src.DIM_PROJECT_ID AS PROJECT_ID).
  • customer_thermometer_202409: Introduced as a legacy stabilisation view.

🚚 Supplier Domain Build-out

New Item Views Introduced:

  • supplier_item_codes_invoice, supplier_item_codes_purchaseorder
  • supplier_item_dates_invoice, supplier_item_dates_purchaseorder
  • supplier_item_values_invoice, supplier_item_values_purchaseorder

Other Changes:

  • supplier_core_detail: Renamed to align with other domains.
  • supplier_item_dates_invoice: Removed [ACC_PERIOD_TO] which wasn’t present in source.

🧾 Notes

🔍 Found 65 unique file changes for datamart view in the given period:

  1. ⚠️ DIFFERENT: busopp_codes.sql
  2. ⚠️ DIFFERENT: busopp_codes_202404.sql
  3. ⚠️ DIFFERENT: busopp_meta_codes.sql
  4. ⚠️ DIFFERENT: busopp_meta_codes_202404.sql
  5. ⚠️ DIFFERENT: busopp_core.sql
  6. ⚠️ DIFFERENT: busopp_core_detail.sql
  7. ⚠️ DIFFERENT: busopp_dates.sql
  8. ⚠️ DIFFERENT: busopp_dates_202404.sql
  9. ⚠️ DIFFERENT: busopp_meta_dates.sql
  10. ⚠️ DIFFERENT: busopp_meta_dates_202404.sql
  11. ⚠️ DIFFERENT: busopp_values.sql
  12. ⚠️ DIFFERENT: busopp_meta_values.sql
  13. ⚠️ DIFFERENT: customer_core.sql
  14. ⚠️ DIFFERENT: customer_core_detail.sql
  15. ⚠️ DIFFERENT: employee_codes.sql
  16. ⚠️ DIFFERENT: employee_codes_202404.sql
  17. ⚠️ DIFFERENT: employee_meta_codes.sql
  18. ⚠️ DIFFERENT: employee_meta_codes_202404.sql
  19. ⚠️ DIFFERENT: employee_core.sql
  20. ⚠️ DIFFERENT: employee_core_detail.sql
  21. ⚠️ DIFFERENT: employee_dates.sql
  22. ⚠️ DIFFERENT: employee_dates_202404.sql
  23. ⚠️ DIFFERENT: employee_meta_dates.sql
  24. ⚠️ DIFFERENT: employee_meta_dates_202404.sql
  25. ⚠️ DIFFERENT: employee_item_dates_expense.sql
  26. ⚠️ DIFFERENT: project_codes.sql
  27. ⚠️ DIFFERENT: project_meta_codes.sql
  28. ⚠️ DIFFERENT: project_core.sql
  29. ⚠️ DIFFERENT: project_core_detail.sql
  30. ⚠️ DIFFERENT: project_dates.sql
  31. ⚠️ DIFFERENT: project_meta_dates.sql
  32. ⚠️ DIFFERENT: project_values_contingency.sql
  33. ⚠️ DIFFERENT: project_values_cost.sql
  34. ⚠️ DIFFERENT: project_values_expenses.sql
  35. ⚠️ DIFFERENT: project_values_forecast.sql
  36. ⚠️ DIFFERENT: project_values_hours.sql
  37. ⚠️ DIFFERENT: project_values_revenue.sql
  38. ⚠️ DIFFERENT: project_values_time.sql
  39. ⚠️ DIFFERENT: project_meta_values_contingency.sql
  40. ⚠️ DIFFERENT: project_meta_values_cost.sql
  41. ⚠️ DIFFERENT: project_meta_values_expenses.sql
  42. ⚠️ DIFFERENT: project_meta_values_forecast.sql
  43. ⚠️ DIFFERENT: project_meta_values_hours.sql
  44. ⚠️ DIFFERENT: project_meta_values_revenue.sql
  45. ⚠️ DIFFERENT: project_meta_values_time.sql
  46. ⚠️ DIFFERENT: supplier_core.sql
  47. ⚠️ DIFFERENT: supplier_core_detail.sql
  48. ⚠️ DIFFERENT: employee_item_codes_expense.sql
  49. ⚠️ DIFFERENT: employee_item_values_expense.sql
  50. ⚠️ DIFFERENT: employee_item_codes_absence.sql
  51. ⚠️ DIFFERENT: employee_item_dates_absence.sql
  52. ⚠️ DIFFERENT: employee_item_values_absence.sql
  53. ⚠️ DIFFERENT: customer_thermometer_202409.sql
  54. ⚠️ DIFFERENT: customer_orderline_202404.sql
  55. ⚠️ DIFFERENT: employee_hours_202409.sql
  56. ⚠️ DIFFERENT: employee_hours_202410.sql
  57. ⚠️ DIFFERENT: supplier_item_dates_invoice.sql
  58. ⚠️ DIFFERENT: supplier_item_codes_invoice.sql
  59. ⚠️ DIFFERENT: supplier_item_codes_purchaseorder.sql
  60. ⚠️ DIFFERENT: supplier_item_dates_purchaseorder.sql
  61. ⚠️ DIFFERENT: supplier_item_values_invoice.sql
  62. ⚠️ DIFFERENT: supplier_item_values_purchaseorder.sql
  63. ⚠️ DIFFERENT: project_meta_template.sql
  64. ⚠️ DIFFERENT: project_details_USD.sql
  65. ⚠️ DIFFERENT: project_details_plus_USD.sql

Leave a Comment