Common Data Model

Summary The [upsert].[entity] procedure registers or updates an entity object in the Common Data Model. It constructs a standardised payload using object_code and object_class, along with derived metadata like domain, entity label, and schema. This payload is then passed to [cdm].[patch_upsert]. It is used for adding or updating entries in core tables (e.g. project_core, employee_core) … Read more

Summary The [upsert].[meta] procedure is a lightweight controller for inserting or updating metadata attributes related to a specific object. It constructs a compact JSON payload and delegates the logic to [cdm].[patch_upsert]. It is ideal for managing metadata flags, status indicators, or classifications for objects like projects, employees, customers, and more. 🧭 Key Steps 🧪 Example … Read more

Here’s your KnowHow page for the upsert.table_list controller—a great example of a reusable shortcode that leverages the metadata engine elegantly. Summary The [upsert].[table_list] procedure registers or updates a metadata record in the log.table_list using a structured payload. It acts as a shortcode controller that builds the required JSON and delegates the actual upsert logic to … Read more

Summary The [cdm].[put_update] procedure enables safe, version-controlled updates to metadata tables by: This procedure ensures data immutability, supporting clean audit trails and history tracking. 🧭 Key Steps 🧪 Example Usage 📦 Parameters Parameter Description @tableName Fully qualified or short name of the target table @payload JSON payload containing the metadata to update @tableType Template type … Read more

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

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