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').
- A. Suffix the view name – e.g.
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).