Release 61

by

Additions

Common Data Model

New Common Data Model (CDM) views have been introduced, conforming to the latest standard:

Employee Domain

  • employee_core
  • employee_codes
  • employee_dates
  • employee_item_dates_absence
  • employee_item_codes_absence
  • employee_item_values_absence

Customer & Supplier Domains

  • customer_core
  • supplier_core

Project Domain

  • project_core
  • project_codes
  • project_dates

These views establish a consistent and scalable approach to data modelling across core business domains, streamlining downstream reporting and integration.

CDM Functions

Key functions have been revised to support the new Common Data Model table templates:

  • upsert_meta
    • upsert_meta_insert
    • upsert_meta_update
  • create_table_meta
    • func_partition_function
      • pf_ExpiryDateRange
      • ps_ExpiryDateScheme

The updated functions support automated table generation and metadata management, ensuring new dataviews adhere to CDM standards with minimal manual intervention.

Note:

The addition of func_partition_function introduces a major structural enhancement to the create_table_meta process. This enables automatic partitioning of tables by financial year, allowing them to grow efficiently while archiving previous years’ data seamlessly — all without manual intervention. This lays a foundation for scalable, performant data marts that handle historical data more effectively.

upsert_meta acts as a controller function that manages both insert and update logic, typically used during incremental data loads. It ensures that previous records are expired and their active flags set to 0, while the current value is inserted as the new active row. This dual-purpose design enables a single table to serve both as the current state and a historic archive, eliminating the need for separate audit tables.

pf_ExpiryDateRange defines the partitioning logic based on date ranges, usually aligned to financial years, while ps_ExpiryDateScheme maps those partitions to the appropriate segments. Together, they support the expiry-based table partitioning framework introduced with func_partition_function.

Alterations

Employee Item Tables

Field structures have been aligned to reflect the latest CDM table templates:

  • employee_item_codes_expense
  • employee_item_values_expense

Structural adjustments ensure alignment with CDM templates, reducing variability and improving reuse across similar item-based views (e.g. expense, absence).

Global Organisation

To ensure dataview continuity prior to the decommissioning of versions 202404 and 202406, the following views have been versioned and updated:

  • global_company_202404
  • global_organisation_202404
  • global_company_202409
  • global_organisation_202409
  • customer_details_202409

This ensures seamless transition and continuity for dependent reporting solutions.

Notes

🔍 Found 26 unique file changes in the given period:
1. ⚠️ DIFFERENT: employee_item_dates_absence.sql
2. ⚠️ DIFFERENT: upsert_meta.sql
3. ⚠️ DIFFERENT: upsert_meta_insert.sql
4. ⚠️ DIFFERENT: upsert_meta_update.sql
5. ⚠️ DIFFERENT: customer_core.sql
6. ⚠️ DIFFERENT: employee_codes.sql
7. ⚠️ DIFFERENT: employee_core.sql
8. ⚠️ DIFFERENT: employee_dates.sql
9. ⚠️ DIFFERENT: employee_item_codes_absence.sql
10. ⚠️ DIFFERENT: employee_item_values_absence.sql
11. ⚠️ DIFFERENT: project_codes.sql
12. ⚠️ DIFFERENT: project_core.sql
13. ⚠️ DIFFERENT: project_dates.sql
14. ⚠️ DIFFERENT: supplier_core.sql
15. ⚠️ DIFFERENT: create_table_meta.sql
16. ⚠️ DIFFERENT: func_partition_function.sql
17. ⚠️ DIFFERENT: pf_ExpiryDateRange.sql
18. ⚠️ DIFFERENT: ps_ExpiryDateScheme.sql
19. ⚠️ DIFFERENT: myview.sql
20. ⚠️ DIFFERENT: global_company_202404.sql
21. ⚠️ DIFFERENT: global_organisation_202404.sql
22. ⚠️ DIFFERENT: global_company_202409.sql
23. ⚠️ DIFFERENT: global_organisation_202409.sql
24. ⚠️ DIFFERENT: employee_item_codes_expense.sql
25. ⚠️ DIFFERENT: employee_item_values_expense.sql
26. ⚠️ DIFFERENT: customer_details_202409.sql

Leave a Comment