Common Data Model (CDM): Structure & Query Guide

How to query a CDM view (Power Query / M)

Use the datamart domain value as @dataMart and the view name as @viewName.

= Sql.Database(
    DataMart_Host,
    DataMart_Endpoint,
    [
      Query = 
        "EXEC get.myView " &
        "@dataMart = 'project', " &
        "@viewName = 'details', " &
        "@token = '" & DataMart_Token & "', " &
        "@version = 'beta'"
    ]
)

Naming conventions

Base data views: details, core, meta_values, meta_dates, item_values.
Level 3 collections (two options):
- A. Suffix the view name – e.g. project.item_values_forecast, project.meta_values_revenue.
- B. Pass the collection via @version – if supported by your proc (e.g. @version='item_values_forecast').

Sales Pipeline

Covers all stages of customer acquisition and order-to-cash pipeline.

Marketing (`marketing`)

Activities and campaigns to generate awareness and leads.

Collection	Definition	Views
Base views	Campaigns, channels, lead gen performance.	`marketing.core_details` / `marketing.meta_values`

Sales Lead (`lead`)

Initial prospect or lead record in the sales funnel

Collection	Definition	Views
Base views	Prospect/lead records in the funnel.	`lead.core_details`

Business Opportunity (`busopp`)

Qualified opportunities with estimated value and probability.

Collection	Definition	Views
Base views	Qualified opps with value & probability.	`busopp.core_details`

Order (`order`)

Customer sales orders committed for fulfilment.

Collection	Definition	Views
Base views	Committed customer orders.	`order.core_details`

Invoice (`invoice`)

Issued sales invoices sent to customers for payment.

Collection	Definition	Views
Base views	Sales invoices issued to customers.	`invoice.core_details`

Global

Organisation-wide reference data used across all domains.

Customer (`customer`)

Master data for customers and their attributes.

Collection	Definition	Views
Base views	Customer master data.	`customer.core_details`, `customer.meta_values`

Supplier (`supplier`)

Master data for suppliers and vendors.

Collection	Definition	Views
Base views	Supplier/vendor master data.	`supplier.core_details`, `supplier.meta_values`

Dates (`dates`)

Canonical calendar structures and period definitions.

Collection	Definition	Views
Base views	Canonical calendars/periods.

Company (`company`)

Registered companies or legal entities in the group.

Collection	Definition	Views
Base views	Legal entities in the group.	`company.core_details`

Programme Reporting Units (`pru`)

Programme Reporting Units, used to group and report projects at programme level.

Collection	Definition	Views
Base views	PRU catalogue (reference list).	`pru.core_details`

Workforce

Human resources data covering people, employment, and roles.

Employee (`employee`)

Employment details tied to people in the workforce.

Collection	Definition	Views
Base views	Employment details tied to people.	`employee.core_details`
Expenses	Employee-submitted expense claims for reimbursement.	`employee.item_values_expenses`, `employee.meta_values_expenses`

Person (`person`)

Personal details and identity attributes for individuals.

Collection	Definition	Views
Base views	Personal identity attributes.	`person.core_details`
Absence	Absence and leave records for people.	`person.item_values_absence`, `person.meta_values_absence`

Job Family (`job`)

Classification of roles into job families and categories.

Collection	Definition	Views
Base views	Job taxonomy..	`job.core_details`

Product/Service

Delivery domains covering projects, resources, and programmes.

Project (`project`)

Data relating to project delivery and control.

Collection	Definition	Views
Base views	Project delivery/control.	`project.core_details`
Commercials	Contracted or priced value, order amounts, and variations.	`project.item_values_commercials`, `project.meta_values_commercials`
Cost	All expenses and charges attributed to projects.	`project.item_values_cost`, `project.meta_values_cost`
Forecast	Projected costs, revenues, and outcomes for projects.	`project.item_values_forecast`, `project.meta_values_forecast`
Revenue	Recognised income earned by projects.	`project.item_values_revenue`, `project.meta_values_revenue`
Time	Effort and hours recorded against projects.	`project.item_values_time`, `project.meta_values_time`
Contingency	Risk allowances and contingency budgets within projects.	`project.item_values_contingency`, `project.meta_values_contingency`
Billing	Invoices raised or pending for project work delivered.	`project.item_values_billing`, `project.meta_values_billing`

Resource (`resource`)

Assignment of people or assets to project activities.

Collection	Definition	Views
Base views	Assignments to activities.	`resource.core_details`
Activity	Planned or actual project tasks and activities.	`resource.item_values_activity`, `resource.meta_values_activity`

Portfolio Programme (`programme`)

Groupings of projects into programmes and portfolios.

Collection	Definition	Views
Base views	Programmes/portfolios catalogue.	`programme.core_details`, `programme.meta_values`

Finance

Financial control and accounting structures.

Finance Organisation (`organisation`)

Reference structures for finance and accounting.

Collection	Definition	Views
Base views	Finance reference structures.
Chart of Accounts	Master list of financial accounts for postings	`organisation.core_details_chartofaccounts`
Cost Centres	Organisational cost centre structures.	`organisation.core_details_costcentres`
Currencies	List of supported currencies	`organisation.core_details_currencies`
Foreign Exchange	Exchange rates used for currency conversion.	`organisation.core_details_forex`

Accounts Payable (`accountspayable`)

Liabilities and payments to suppliers.

Collection	Definition	Views
Base views	Payables domain base.	`accountspayable.core_details`
Purchase Order	Orders placed with suppliers for goods or services.	`accountspayable.item_values_purchaseorder`
Purchase Invoice	Invoices received from suppliers for payment	`accountspayable.item_values_purchaseinvoice`

Accounts Receivable (`accountsreceivable`)

Amounts owed by customers.

Collection	Definition	Views
Base views	Receivables domain base.	`accountsreceivable.core_details`
Sales Order	Confirmed orders from customers.	`accountsreceivable.item_values_salesorder`
Sales Invoice	Invoices issued to customers for settlement.	`accountsreceivable.item_values_salesinvoice`

General Ledger (`generalledger`)

Core ledger of financial postings and balances.

Collection	Definition	Views
Base views	Ledger backbone.	`generalledger.core_details`
Transactions	Detailed journal and posting entries in the ledger.	`generalledger.item_values_gltransaction`
Budget	Approved financial plans for accounts and cost centres.	`generalledger.item_values_glbudget`
Balances	Period-end aggregated balances by account and cost centre.	`generalledger.item_values_glbalance`
Forecast	Projected financial performance in the ledger.	`generalledger.item_values_glforecast`
Journals	Manual or system-generated journal entries.	`generalledger.item_values_gljournal`
Consolidation	Group-level consolidations and eliminations across entities.	`generalledger.item_values_glconsolidation`

Fixed Assets (`fixedassets`)

Records of company-owned fixed assets.

Collection	Definition	Views
Base views	Asset register & attributes.	`fixedassets.core_details`

Cash Management (`cashmanagement`)

Cash flow, treasury, and banking activities.

Collection	Definition	Views
Base views	Cash flow, treasury, banking.	`cashmanagement.core_details`

Enterprise

Cross-cutting domains covering infrastructure, risk, and governance.

Infrastructure (`infrastructure`)

Physical and digital backbone supporting the enterprise.

Collection	Definition	Views
Base views	Enterprise backbone.	`infrastructure.core_details`
Assets	Enterprise infrastructure assets such as servers, equipment.	`infrastructure.core_details_assets`
Systems	Core business and IT systems in operation.	`infrastructure.core_details_systems`
Facilities	Buildings, sites, and facilities management data.	`infrastructure.core_details_facilities`
Networks	Networking and connectivity infrastructure.	`infrastructure.core_details_networks`

Customer Feedback (`feedback`)

Mechanisms for capturing customer sentiment and input.

Collection	Definition	Views
Base views	Feedback & sentiment.
Requests	Customer or stakeholder requests for new features or data.	`feedback.item_values_requests`
Complaints	Formal complaints raised by customers.	`feedback.item_values_complaints`
Satisfaction	Survey or sentiment measures of satisfaction.	`feedback.item_values_satisfaction`
Engagement	Usage, adoption, and customer engagement measures.	`feedback.item_values_engagement`

Risk (`risk`)

Enterprise risks, registers, and mitigations.

Collection	Definition	Views
Base views	Risk registers/controls.	`risk.core_details`

Compliance (`compliance`)

Regulatory and policy compliance data.

Collection	Definition	Views
Base views	Regulatory/policy compliance.	`compliance.core_details`

Sustainability (`sustainability`)

Environmental and sustainability reporting data.

Collection	Definition	Views
Base views	Environmental reporting.	`sustainability.core_details`

Strategy (`strategy`)

Enterprise strategies and objectives.

Collection	Definition	Views
Base views	Strategies & objectives.	`strategy.core_details`

KPIs / Performance (`performance`)

Key performance indicators and scorecards.

Collection	Definition	Views
Base views	KPIs and scorecards.	`performance.core_details`

Tips

If your proc supports it, you may pass Level-3 as @version (Option B). Otherwise, use the suffix pattern (Option A).
Join Global reference lists to applied domains via keys (e.g., PRU, Cost Centre).

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"Regional View"}

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"Stable 09 View"}

Expected Condition The DataMart View BusOpp.Dates is in the style of 202409 As Found Condition The unversioned (beta) edition of…

Double check your WITH READ ONLY — it’s typically only used on views, not raw SELECT queries unless you're defining…

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"Cross Apply version"}

{"Current stable version":"202409","exec":"@version = ‘202409’","Development" : "@version = ‘beta’","Change":"CTE Version"}

How to query a CDM view (Power Query / M)

Sales Pipeline

Marketing (marketing)

Sales Lead (lead)

Business Opportunity (busopp)

Order (order)

Invoice (invoice)

Global

Customer (customer)

Supplier (supplier)

Dates (dates)

Company (company)

Programme Reporting Units (pru)

Workforce

Employee (employee)

Person (person)

Job Family (job)

Product/Service

Project (project)

Resource (resource)

Portfolio Programme (programme)

Finance

Finance Organisation (organisation)

Accounts Payable (accountspayable)

Accounts Receivable (accountsreceivable)

General Ledger (generalledger)

Fixed Assets (fixedassets)

Cash Management (cashmanagement)