Summary The [cdm].[post_insert] stored procedure is responsible for inserting a new metadata row into a specified table using a structured JSON payload. It dynamically builds the insert logic using the column metadata from cont.table_templates, including default values and computed checksum logic. This procedure is a core component of the CDM metadata engine, providing consistency, reusability, … Read more
Summary The [cdm].[patch_upsert] procedure acts as a controller for metadata inserts and updates. It dynamically evaluates JSON payloads, identifies whether an existing active row already exists, and determines whether to call an insert (post_insert) or update (put_update) operation. This ensures consistent versioned metadata with minimal redundancy. It supports JSON-driven payloads with field mapping based on … Read more
Summary The [cdm].[create_table] stored procedure dynamically builds SQL tables using metadata templates from cont.table_templates. It is used to create consistent core, meta or item tables within a specified schema, complete with partitioning, indexes, and insertion triggers for data integrity and metadata logging. This approach ensures repeatability, consistency, and automated setup of foundational table structures across … Read more
Additions Customer Details & Project Details APC Alterations General Ledger Transaction Customer Details (CAD & USD) Business Opportunity Dates (Busopp Dates) Notes The following SQL scripts have been modified and differ from previous versions: There were additional alterations to cdm to support transaction error logging 1. ⚠️ DIFFERENT: create_table_comment.sql2. ⚠️ DIFFERENT: create_table_log.sqlSource ID altered to … Read more
Ensuring Structured, Reusable, and Scalable Data Architecture 1️⃣ Purpose of the Common Data Standard The Common Data Standard (CDS) establishes a structured framework for defining DataMart views in a consistent and scalable manner. This standard: ✅ Promotes reusability across different datasets.✅ Ensures alignment between business logic and reporting needs.✅ Enables efficient data joins and minimises … Read more
Bridging Structured Data with Usability 1️⃣ Understanding the Layers: CDS vs. Utility Views The Common Data Standard (CDS) defines a structured, specialist-oriented view of data, ensuring consistency and modularity in reporting. However, for non-specialists, the Utility (Commodity) View simplifies data consumption by cross-combining relevant details into a single, user-friendly dataset. View Type Purpose Audience Example … Read more
1️⃣Overview The Data Dictionary serves as the backbone for structured data definitions across financials, operations, projects, compliance, and workforce domains. It ensures standardised terminology, clear metadata definitions, and structured governance for all key data entities. Key Functions: ✅ Standardised Definitions – Ensures clarity and uniformity across datasets.✅ Metadata-Driven Organisation – Supports schema validation and query … Read more
Proposal for a white paper on Common Data Model (Datamarts) Proposed Structure for the White Paper 1. Executive Summary 2. Introduction 3. Objectives 4. Proposed Standardised Format 5. Naming Conventions 6. Standardised Structure for Views 7. Metadata Format 8. Implementation Roadmap 9. Benefits 10. Conclusion Naming Suggestions for Views Here are some options for naming … Read more
In its most basic definition, sensitive data is a specific set of “special categories”: Article 9 of the GDPR prohibits special category data from being processed unless you can rely on an exemption.
Core Views and their Standard Structures Each DataMart will be built on six primary views: 1. Details (or Core) View Purpose: Schema: Column Name Data Type Description company VARCHAR Primary entity identifier object_id VARCHAR Unique object identifier (project_id) seq_json JSON Structured metadata reference for linking name VARCHAR Name of the project/entity description TEXT Brief descriptive … Read more