Microsoft Dynamics 365 — Sources

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

Won actual value

What is actualvalue of opportunities with statecode = Won in the period?

SUM(opportunity.actualvalue) WHERE opportunity.statecode = 1 AND opportunity.actualclosedate IN period

Grain: opportunity
Fields: opportunity.actualvalue, opportunity.statecode, opportunity.actualclosedate, opportunity.transactioncurrencyid
Dimensions: owner, region, stage

Dataverse: statecode 0=Open, 1=Won, 2=Lost. Currency via transactioncurrencyid.

Adapt: Use statuscode for firm-specific won reasons; lock multi-currency reporting.

Example

Open estimated value

How much estimatedvalue sits in open opportunities?

SUM(opportunity.estimatedvalue) WHERE opportunity.statecode = 0

Grain: opportunity
Fields: opportunity.estimatedvalue, opportunity.statecode, opportunity.stepname, opportunity.closeprobability
Dimensions: owner, stage, source

Optionally × closeprobability for weighted pipeline.

Adapt: Clarify estimatedvalue vs actualvalue policy by stage.

Win rate (statecode)

What share of closed opportunities is Won (statecode=1)?

COUNT(statecode=1) / COUNT(statecode IN (1,2))

Grain: opportunity
Fields: opportunity.statecode, opportunity.statuscode, opportunity.actualclosedate
Dimensions: owner, source

Denominator closed only (won+lost), not open.

Adapt: Exclude statuscode values that mean cancelled from the denominator.

Avg won actual value

What is the typical actualvalue of won opportunities?

SUM(actualvalue WHERE statecode=1) / COUNT(statecode=1)

Grain: opportunity
Fields: opportunity.actualvalue, opportunity.statecode
Dimensions: owner, region

statecode=1 only; normalize currency.

Adapt: Lock median vs mean and base currency.

Line extended amount

How does extendedamount distribute across products on won opportunities?

SUM(opportunityproduct.extendedamount) WHERE parent opportunity.statecode = 1

Grain: opportunityproduct
Fields: opportunityproduct.extendedamount, opportunityproduct.quantity, opportunityproduct.productid, opportunity.statecode
Dimensions: owner, region

Only when opportunityproduct is loaded.

Adapt: Align product catalog / price list grain with the business.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
Won actual value	`SUM(opportunity.actualvalue) WHERE opportunity.statecode = 1 AND opportunity.actualclosedate IN period`	opportunity	opportunity.actualvalue, opportunity.statecode, opportunity.actualclosedate, opportunity.transactioncurrencyid
Open estimated value	`SUM(opportunity.estimatedvalue) WHERE opportunity.statecode = 0`	opportunity	opportunity.estimatedvalue, opportunity.statecode, opportunity.stepname, opportunity.closeprobability
Win rate (statecode)	`COUNT(statecode=1) / COUNT(statecode IN (1,2))`	opportunity	opportunity.statecode, opportunity.statuscode, opportunity.actualclosedate
Avg won actual value	`SUM(actualvalue WHERE statecode=1) / COUNT(statecode=1)`	opportunity	opportunity.actualvalue, opportunity.statecode
Line extended amount	`SUM(opportunityproduct.extendedamount) WHERE parent opportunity.statecode = 1`	opportunityproduct	opportunityproduct.extendedamount, opportunityproduct.quantity, opportunityproduct.productid, opportunity.statecode

Id	Label	Grain	Notes
`owner`	Owner / team	User → role → team	Align owner mapping across CRMs when both systems run.
`stage`	Stage / pipeline	Stage name + pipeline	Stage labels are firm-specific — lock won/lost/open meanings.
`region`	Region / country	Country / region	Consciously choose billing vs shipping country.
`source`	Lead source / channel	Source value	Harmonize picklist values before the mart.

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
contact	`emailaddress1, telephone1, mobilephone, firstname, lastname, address1_line1, address1_city, address1_postalcode, birthdate`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Dataverse contact — tag logical names.
lead	`emailaddress1, telephone1, firstname, lastname, address1_composite, companyname`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Like contact; watch qualify path to contact.
account	`address1_line1, address1_city, address1_postalcode, telephone1, emailaddress1`	Quasi	RAW: tag; Curated: generalize (region vs postal); Mart: aggregated	Account addresses quasi-ID; email may be personal.
systemuser	`internalemailaddress, fullname, mobilephone, domainname`	Workforce	RAW: workforce policy; Curated: separate from customer PII; Mart: aggregates only	Workforce separate from customer contact.
incident	`title, description, emailaddress, customerid`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Case free text and customer join.
annotation	`subject, notetext, filename`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Notes/attachments — skip bodies by default.
opportunity	`description, name`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Free text and names can identify customers.
activitypointer / email	`subject, description, regardingobjectid`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Activity bodies and regarding joins.
Custom columns	`new_* email/phone, alternate keys`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Inventory publisher-prefix custom columns.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	emailaddress1, telephone1, contactid, alternate keys, Azure AD oid.
Primary objects	contact, lead, account, incident + activity parties.
Warehouse copies	Data Lake/Synapselink, export to data lake, Fabric mirrors.
Audit & plugin logs	audittable, plugintracelog — old PII values.
Annotations & files	annotation notetext and file attachments.
Polymorphic customerid	account vs contact in one lookup column.
Solutions / managed fields	Managed attributes with hidden sensitivity.
Integration users	Application users bypass UI field security.
Environments	Do not mix dev/test/prod environments.
Virtual tables / external	External sources double DSDR surface.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 8

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High Won without actualvalue

Problem: Won (statecode=1) without actualvalue makes revenue unreliable.
Prevent: Business rule: on Won require actualvalue+actualclosedate.
Staff must know: Sales: Close only after actualvalue is set.
Checks: statecode = 1 AND (actualvalue IS NULL OR actualvalue = 0)
Fix in source: Business rule / required on won status reasons.
Warehouse safety net: Exclude from won measures; DQ flag.

High statecode/statuscode drift

Problem: Invalid statecode/statuscode combos → win/loss differs by report.
Prevent: Clean status reasons; document the map.
Staff must know: Sales: Only approved status reasons.
Checks: Invalid statuscode for statecode combinations
Fix in source: Maintain status reason map; deprecate unused.
Warehouse safety net: Canonical win/loss map in Curated.

Medium actualclosedate missing when won

Problem: Won without actualclosedate breaks periods and cycle time.
Prevent: Require/auto-set actualclosedate on close.
Staff must know: Sales: Close date = real close day.
Checks: statecode=1 AND actualclosedate IS NULL
Fix in source: Required actualclosedate on close.
Warehouse safety net: Fallback modifiedon with flag.

Medium Opportunity without customerid

Problem: Opportunity without customerid → no customer/account view.
Prevent: Business rule: customer required.
Staff must know: Sales: Always set Account or Contact as customer.
Checks: customerid IS NULL on open/won opps
Fix in source: Business rule require customer.
Warehouse safety net: Unknown customer dim.

Medium Owner disabled

Problem: Owner disabled → team KPIs inconsistent.
Prevent: Disable-user process including reassign.
Staff must know: Ops: Reassign records before disabling users.
Checks: ownerid → systemuser.isdisabled = true
Fix in source: Reassign on disable; queues.
Warehouse safety net: Fallback team owner dim.

Low transactioncurrencyid missing

Problem: Money without transactioncurrencyid distorts sums.
Prevent: Org default currency; require currency when amount set.
Staff must know: Sales: Check currency on the record.
Checks: actualvalue NOT NULL AND transactioncurrencyid NULL
Fix in source: Org currency defaults; required currency.
Warehouse safety net: Assume base currency + flag.

Medium Incident without customer

Problem: Cases without customer leave service KPIs personless.
Prevent: Require customer on case form.
Staff must know: Service: Do not save a case without customer.
Checks: incident.customerid IS NULL
Fix in source: Required customer on case form.
Warehouse safety net: Unknown caller bucket.

High Same emailaddress1 multiple times

Problem: Same emailaddress1 many times → duplicate people, false 360° view.
Prevent: Duplicate detection + alternate key on email; block create.
Staff must know: Search before create; on match use existing contact.
Checks: COUNT(*) GROUP BY emailaddress1 HAVING COUNT>1
Fix in source: Duplicate detection rules + alternate key on email.
Warehouse safety net: Golden contact map.

MDM / duplicates 4

contact Contact duplicates

Match keys: emailaddress1, mobilephone, alternate key
Prevent in source: Duplicate detection rules + alternate key unique email.
Resolve in warehouse: Merge jobs; master_contact_id in Curated.
Survivorship: Active + most recent modifiedon wins.

account Account duplicates

Match keys: name + address1_postalcode, websiteurl, alternate key
Prevent in source: Duplicate detection on account create/update.
Resolve in warehouse: Fuzzy company match; golden_account_id.
Survivorship: Alternate key / highest open revenue wins.

lead Lead vs contact

Match keys: emailaddress1, qualify path
Prevent in source: Qualify checks existing contact; duplicate detection.
Resolve in warehouse: Block double qualify; link to surviving contact.
Survivorship: Qualified contact is master.

customerid Polymorphic customerid

Match keys: customerid type account|contact
Prevent in source: UI guidance: prefer account for B2B.
Resolve in warehouse: Split dims by type; never mix counts blindly.
Survivorship: Account grain for B2B KPIs.

Product settings to improve 6

Settings → Data Management → Duplicate Detection Rules + jobs for contact/account/lead

Why: Stop duplicates at source.
How: Enable publish rules; schedule detect jobs.

Tables → Keys Alternate keys (email, account number)

Why: Stable integration/MDM key.
How: Create unique alternate keys; use in upserts.

Business Rules Won → actualvalue/actualclosedate required

Why: Enforce KPI fields.
How: Scope to forms + server side where possible.

Forms / Columns Required columns on main forms

Why: UI completeness.
How: Mark customerid, budget fields required on opp form.

Status Reasons Clean status reasons

Why: state/status consistency.
How: Deprecate unused reasons; document win/loss set.

Users & Security Reassign on disable

Why: Avoid stale owners.
How: Disable user checklist includes record reassignment.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

Dataverse tables & columns

Where in product: make.powerapps.com → Tables; Solution Explorer
How to access: UI; Web API `$metadata`; EntityDefinitions / AttributeMetadata.
Use for: Logical names for load, PII, business rules.
Watchouts: Publisher-prefix custom columns; managed vs unmanaged solutions.

Choice / status reason

Where in product: Table → Columns → Choice; Status/Status Reason
How to access: Option set metadata via API; document status maps (statecode/statuscode).
Use for: Won/lost DQ, incident states.
Watchouts: Status reason depends on statecode — invalid combos possible without rules.

Alternate keys

Where in product: Table → Keys
How to access: UI keys; API EntityKeyMetadata.
Use for: Upserts, MDM anchors (email, account number).
Watchouts: Keys must be unique & populated — otherwise upserts fail.

Duplicate detection rules

Where in product: Settings → Data Management → Duplicate Detection
How to access: Published rules + jobs; export XML/definition where possible.
Use for: Contact/account/lead dedup at source.
Watchouts: Jobs run async — do not expect realtime block for everything.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`account` account	Dataverse company (account) — customer master and hierarchy via parentaccountid.	One account record	Load
`contact` contact	Dataverse person (contact) — PII via emailaddress1/telephone1, join via parentcustomerid.	One contact	Load
`lead` lead	Dataverse lead before qualification — statuscode/statecode for funnel.	One lead	Optional
`opportunity` opportunity	Dataverse opportunity — actualvalue/estimatedvalue, won via statecode=1.	One opportunity	Load
`opportunityproduct` opportunityproduct	Product lines (opportunityproduct) — extendedamount/quantity for product mix.	One opportunityproduct row	Optional
`systemuser` systemuser	Dataverse user (systemuser) for ownerid → fullname / businessunitid.	One systemuser	Load
`campaign` campaign	Marketing campaign in Dataverse — attribution optional.	One campaign	Optional
`incident` incident	Case/incident in Customer Service — only for service analytics.	One incident	Optional
`task` task	Activity (task) — often noisy; load only with a clear consumer.	One task	Optional

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
Opportunity	`Opportunity.opportunityid`	Key	Join / lineage
Opportunity	`Opportunity.actualvalue`	Measure	Revenue / pipeline
Opportunity	`Opportunity.estimatedvalue`	Measure	Pipeline estimate
Opportunity	`Opportunity.statecode`	Measure	Open / won / lost
Opportunity	`Opportunity.statuscode`	Dimension	Finer stage logic
Opportunity	`Opportunity.actualclosedate`	Measure	Period grain
Opportunity	`Opportunity.closeprobability`	Measure	Weighted pipeline
Opportunity	`Opportunity.stepname`	Dimension	Stage dimension
Opportunity	`Opportunity.ownerid`	Dimension	Owner
Opportunity	`Opportunity.customerid`	Dimension	Account join
Opportunity	`Opportunity.createdon`	Key	Cycle time / CDC
Opportunity	`Opportunity.modifiedon`	Key	Incremental sync
Opportunity	`Opportunity.transactioncurrencyid`	Measure	Multi-currency
Opportunity	`Opportunity.leadsourcecode`	Dimension	Channel
OpportunityProduct	`OpportunityProduct.opportunityid`	Key	Parent join
OpportunityProduct	`OpportunityProduct.quantity`	Measure	Quantity
OpportunityProduct	`OpportunityProduct.extendedamount`	Measure	Line revenue
OpportunityProduct	`OpportunityProduct.productid`	Dimension	Product
Account	`Account.accountid`	Key	Join
Account	`Account.name`	Dimension	Account label
Account	`Account.industrycode`	Dimension	Industry
Account	`Account.address1_country`	Dimension	Region
Account	`Account.parentaccountid`	Dimension	Hierarchy
Account	`Account.ownerid`	Dimension	Account owner
Contact	`Contact.contactid`	Key	Join
Contact	`Contact.parentcustomerid`	Dimension	Account join
Contact	`Contact.emailaddress1`	PII	Person key / PII
Contact	`Contact.telephone1`	PII	PII
Contact	`Contact.mobilephone`	PII	PII
Contact	`Contact.firstname`	PII	PII
Contact	`Contact.lastname`	PII	PII
Contact	`Contact.jobtitle`	Dimension	Job title
Lead	`Lead.emailaddress1`	PII	Person key / PII
Lead	`Lead.statuscode`	Dimension	Lead status
Lead	`Lead.statecode`	Measure	Conversion
SystemUser	`SystemUser.systemuserid`	Key	Owner join
SystemUser	`SystemUser.fullname`	Dimension	Owner label
SystemUser	`SystemUser.isdisabled`	Dimension	Active owners
SystemUser	`SystemUser.businessunitid`	Dimension	Team / BU
Campaign	`Campaign.campaignid`	Key	Attribution join
Campaign	`Campaign.name`	Dimension	Campaign label
Campaign	`Campaign.typecode`	Dimension	Campaign type
Incident	`Incident.incidentid`	Key	Join
Incident	`Incident.statuscode`	Dimension	Case status
Incident	`Incident.caseorigincode`	Dimension	Channel

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`annotation (bodies) / attachments`	System	Free text and files — expensive and sensitive.
`plugintracelog / trace log`	System	Runtime noise, may contain PII.
`audit (vor Bedarf)`	System	Load audit only with a retention policy.
`systemform / ui metadata`	System	Not analytical.
`asyncoperation bulk`	System	Job noise.
`post / postcomment (Yammer-like)`	System	Collaboration PII.
`mailbox / email server sync dumps`	System	Mail bodies.
`Unused managed solution entities`	System	Schema ballast.

Skip fields / content

notetext / description free-text (bulk) — PII — selectively or redact.
Unused custom columns (bulk sync) — DSDR surface.
UI / form XML — Not analytical.
Binary file columns — Cost.
Plugin debug payloads — Secrets/PII.
Calculated fields without consumer — Derive again in Curated.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW opportunity landing

Dataverse opportunity — actualvalue, estimatedvalue, statecode/statuscode, transactioncurrencyid.

-- Warehouse-neutral RAW from Dataverse export (not FetchXML)
CREATE TABLE raw_d365_opportunity (
  opportunityid           VARCHAR,
  name                    VARCHAR,
  parentaccountid         VARCHAR,
  owninguser              VARCHAR,
  actualvalue             DECIMAL(18,2),
  estimatedvalue          DECIMAL(18,2),
  closeprobability        INT,
  statecode               INT,  -- 0 Open, 1 Won, 2 Lost
  statuscode              INT,
  actualclosedate         DATE,
  estimatedclosedate      DATE,
  transactioncurrencyid   VARCHAR,
  createdon               TIMESTAMP,
  modifiedon              TIMESTAMP,
  statecode_name          VARCHAR,
  _loaded_at              TIMESTAMP
);

RAW account + systemuser

emailaddress1 and address fields are PII — keep RAW restricted.

CREATE TABLE raw_d365_account (
  accountid VARCHAR,
  name VARCHAR,
  address1_country VARCHAR,
  address1_postalcode VARCHAR, -- PII/quasi
  ownerid VARCHAR,
  createdon TIMESTAMP,
  modifiedon TIMESTAMP,
  _loaded_at TIMESTAMP
);

CREATE TABLE raw_d365_systemuser (
  systemuserid VARCHAR,
  fullname VARCHAR,
  internalemailaddress VARCHAR, -- workforce PII
  isdisabled BOOLEAN,
  _loaded_at TIMESTAMP
);

Curated

Curated fact opportunity

Won = statecode = 1; open pipeline via estimatedvalue and statecode = 0.

CREATE TABLE curated_fct_d365_opportunity AS
SELECT
  opportunityid,
  parentaccountid AS account_id,
  owninguser AS owner_id,
  actualvalue,
  estimatedvalue,
  closeprobability,
  statecode,
  statuscode,
  actualclosedate,
  estimatedclosedate,
  transactioncurrencyid AS currency_id,
  createdon,
  (estimatedvalue * closeprobability / 100.0) AS weighted_estimatedvalue,
  CASE WHEN statecode = 1 THEN TRUE ELSE FALSE END AS is_won,
  CASE WHEN statecode = 0 THEN TRUE ELSE FALSE END AS is_open
FROM raw_d365_opportunity;

Curated dim account / owner

No cleartext emails in default dims; region instead of postal code.

CREATE TABLE curated_dim_d365_account AS
SELECT
  accountid AS account_id,
  name,
  address1_country AS region,
  ownerid AS owner_id
FROM raw_d365_account;

CREATE TABLE curated_dim_d365_owner AS
SELECT
  systemuserid AS owner_id,
  fullname AS owner_name
  -- omit internalemailaddress from default marts
FROM raw_d365_systemuser
WHERE COALESCE(isdisabled, FALSE) = FALSE;

Curated measure SELECTs

actualvalue for won; estimatedvalue for open; win rate via statecode.

-- Won actualvalue in period
SELECT SUM(actualvalue) AS won_actualvalue
FROM curated_fct_d365_opportunity
WHERE statecode = 1
  AND actualclosedate BETWEEN :period_start AND :period_end;

-- Open estimatedvalue
SELECT SUM(estimatedvalue) AS open_estimatedvalue
FROM curated_fct_d365_opportunity
WHERE statecode = 0;

-- Win rate (closed opps: won vs won+lost)
SELECT
  SUM(CASE WHEN statecode = 1 THEN 1 ELSE 0 END) * 1.0
    / NULLIF(SUM(CASE WHEN statecode IN (1, 2) THEN 1 ELSE 0 END), 0) AS win_rate
FROM curated_fct_d365_opportunity
WHERE statecode IN (1, 2)
  AND actualclosedate BETWEEN :period_start AND :period_end;

Tools

Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.

Playbooks

Related governance stories for modelling, PII and measure design around this source.

Vendor help on Resources Official docs, governance and learning paths for this source. Open Resources →