Template.DataMart

by

<Domain.Object_ViewName>

Template note: Replace all <placeholders> with your content. Keep sections, even if marked “N/A”, so views stay consistent across the Common Data Standard (CDS).

Purpose

A concise paragraph stating why this view exists and how it aligns to the CDS item & values table pattern. Mention the primary decision/use it supports.

Example prompt: “This view provides for , enabling . It follows CDS conventions for consistent modelling across finance and operations.”

Use Case

  • Owner: <Full name>
  • Typical Use Cases: <bullets of key analytics / operational needs>
  • Style: Each row represents <item grain> with values captured as <value grain>. Reference CDS item/value structure.

Suggested dashboard insights:

  • <KPI 1>
  • <KPI 2>
  • <KPI 3>

Source

  • Primary Source(s): <schema.table or shortcode>
    Example WP shortcode:
  • Refresh cadence: <e.g., hourly, daily, ad-hoc>
  • Load path: <Bronze→Silver→Gold/CDM>

Integration with Dimension / Reference Tables

List explicit joins used (and typical joins anticipated):

Joins used:

  • <schema.table> — purpose
  • <schema.table> — purpose

Common but optional joins:

  • <Employee core/details>
  • <Finance / Accounting>

Note: keep this list in sync with actual SQL.

Sample Exec

Provide a runnable example (view-limited or top-N) for quick validation.

  • WP shortcode (if applicable):
  • Notes: <filters or parameters>

Response (Output Schema)

Describe the output columns and JSON structures. Use exact names and include data types where helpful.

Identifiers

  • COMPANY: <type> — Company associated with the record.
  • OBJECT_CLASS / OBJECT_CODE / OBJECT_SEQ:<type> — (If CDS object pattern is used)
    • OBJECT_CLASS e.g., Company
    • OBJECT_CODE e.g., Emp_No
    • OBJECT_SEQ = concat of class & code (canonical key)
  • EMP_NO / EMPLOYEE_ID: <type> — Employee identifier.

Item Sequence (JSON)

  • ITEM_SEQ (JSON):
    • EXPENSE_ID — Unique ID for the item.
    • SEQ_NO — Sequence number for the item line.
    • ITEM_TYPE — Category (e.g., “Expense”).

Item Details

  • ATTRIBUTE: <type> — Item classification (e.g., travel, meals).
  • ITEM_AMOUNT: <decimal> — Gross amount attributed to the item.

Financial Metadata (JSON)

  • VALUE_META (JSON):
    • CURRENCY — GBP|EUR|USD|...
    • QUANTITY — <decimal>
    • PRICE — <decimal> per unit
    • VAT — <decimal> (tax component)
    • ACCOUNT_DATE — <date> posting/transaction date
    • (Optionally) BASE, UNIFIED, EXCHANGE_RATE, LOCAL_VALUE, CURRENCY_CODE — if using nested currency structure as per CDS value spec.

Status (JSON)

  • STATUS (JSON):
    • ACTIVE_FLAG — 0|1 (1 = Active)
    • (Optionally) CLOSED_FLAG, CANCELLED_FLAG, WORK_AT_RISK, SOURCE_STATE

Audit & Tracking

  • CREATE_DATE: <datetime> — Record creation timestamp.
  • SOURCE_SYSTEM: <varchar> — Originating table/system name.
  • LOAD_BATCH / RUN_ID: <guid/int> — Optional ingestion lineage.

Keys & Uniqueness

  • Primary (logical) key: <COMPANY, EMPLOYEE_ID, EXPENSE_ID, SEQ_NO> (or OBJECT_SEQ + ITEM_SEQ)
  • Grain statement: “One row per <employee item> at <value grain> on <date or sequence>“.
  • De-duplication rule(s): <e.g., prefer latest CREATE_DATE>

History & Presence (RPV)

  • History model: <Type> — e.g., soft-delete, status history, or snapshot.
  • Row Presence Verification (RPV): Track last-seen presence per key to detect stale/missing values when using incremental loads.
    • Presence key: <COMPANY, EMPLOYEE_ID, EXPENSE_ID, SEQ_NO>
    • Last-seen date field: <LAST_SEEN_DATE>
    • Soft delete flag/date: <IS_ACTIVE, DELETED_DATE>

Data Quality & Rules

  • Mandatory fields: <list>
  • Validations: <e.g., VAT ≥ 0, ITEM_AMOUNT ≥ 0, ACCOUNT_DATE not future>
  • Business rules applied: <e.g., mileage capped at policy rate>
  • Assumptions/limitations: <bullets>

Security & Privacy

  • PII/Commercial sensitivity: <PII elements, handling>
  • Access control: <roles / containers / schemas>
  • Retention: <policy or period>

Performance & Partitions

  • Partitioning / clustering: <e.g., by ACCOUNT_DATE, COMPANY>
  • Indexes / statistics: <notes>
  • Expected volume: <rows/day, total>

Dependencies

  • Upstream: <Bronze/Silver tables, external APIs>
  • Downstream: <reports, data products>

SQL Code & Artefacts

  • SQL location: <repo path or DevOps link>
  • WP shortcode for SQL:
  • View name: <schema.view_name>
  • Create/alter script reference: <stored procedure or pipeline>

Change Log

DateVersionAuthorChange
<YYYY-MM-DD>v1.0<name>Initial creation

Quick-Start Checklist (tick off when publishing)

  • Purpose and Use Case completed
  • Source + Joins reflect current SQL
  • Output schema verified in staging
  • Keys & Grain declared
  • RPV / history behaviour documented
  • Security reviewed (PII)
  • Performance notes added
  • SQL & shortcode IDs linked
  • Change log updated

Leave a Comment