Microsoft Exchange — Sources

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

Example

Mailboxes active

How many user mailboxes are active (snapshot)?

COUNT(*) FROM mailbox WHERE recipientTypeDetails = 'UserMailbox'

Grain: Active user mailbox
Fields: Mailbox.id, Mailbox.recipientTypeDetails
Dimensions: mailbox_type, department

Filter UserMailbox; count shared/room separately.

Adapt: Optional: only mailboxes with mail activity in the period.

Example

Emails sent (meta count)

How many emails were sent in the period (meta count)?

COUNT(*) FROM mail_item_meta WHERE folder = 'SentItems' AND sentDateTime IN period

Grain: Sent message (meta)
Fields: MailItemMeta.id, MailItemMeta.receivedDateTime, MailItemMeta.size
Dimensions: mailbox_type, department

Meta count only; no body read needed for volume KPI.

Adapt: Separate internal vs external mail via domain comparison.

Meetings scheduled

How many calendar events were created in the period?

COUNT(*) FROM calendar_event WHERE isCancelled = false AND createdDateTime IN period

Grain: Calendar event
Fields: CalendarEvent.id, CalendarEvent.start.dateTime, CalendarEvent.isCancelled
Dimensions: event_type, department

Clarify recurring master vs instances in the extract.

Adapt: Only meetings with >1 attendee as “meeting” vs focus time.

Mail flow rule hits

How often did transport rules match in the period?

SUM(match_count) FROM mail_flow_rule_stats WHERE period_date IN period

Grain: Rule match (agg)
Fields: MailFlowRule.id, MailFlowRule.name, MailFlowRule.priority
Dimensions: department

Rule stats from Exchange reporting, not derived from message bodies.

Adapt: Track DLP rules separately for compliance dashboards.

Distribution groups count

How many distribution groups exist (snapshot)?

COUNT(*) FROM distribution_group

Grain: Distribution group
Fields: DistributionGroup.id, DistributionGroup.displayName, DistributionGroup.memberCount
Dimensions: department

Mark dynamic vs static groups.

Adapt: Only groups with memberCount > 0 as “active groups”.

Formula overview

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

Label	Formula	Grain	Fields
Mailboxes active	`COUNT(*) FROM mailbox WHERE recipientTypeDetails = 'UserMailbox'`	Active user mailbox	Mailbox.id, Mailbox.recipientTypeDetails
Emails sent (meta count)	`COUNT(*) FROM mail_item_meta WHERE folder = 'SentItems' AND sentDateTime IN period`	Sent message (meta)	MailItemMeta.id, MailItemMeta.receivedDateTime, MailItemMeta.size
Meetings scheduled	`COUNT(*) FROM calendar_event WHERE isCancelled = false AND createdDateTime IN period`	Calendar event	CalendarEvent.id, CalendarEvent.start.dateTime, CalendarEvent.isCancelled
Mail flow rule hits	`SUM(match_count) FROM mail_flow_rule_stats WHERE period_date IN period`	Rule match (agg)	MailFlowRule.id, MailFlowRule.name, MailFlowRule.priority
Distribution groups count	`COUNT(*) FROM distribution_group`	Distribution group	DistributionGroup.id, DistributionGroup.displayName, DistributionGroup.memberCount

Id	Label	Grain	Notes
`mailbox_type`	Mailbox type	mailbox.recipientTypeDetails	Evaluate user vs shared vs room mailboxes separately.
`department`	Department	Directory join (Entra department)	Department comes from a directory join, not from Exchange itself.
`event_type`	Event type	calendar_event.isCancelled / recurrence	Distinguish single vs recurring vs cancelled events.
`device_type`	Device type	mobile_device.deviceType	For compliance reports on mobile device coverage.

PII hotspots

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

Entity	Fields	Classification	Stage treatment	Treatment
Mailbox	`id, primarySmtpAddress, displayName, aliasAddresses`	Workforce	RAW: workforce policy; Curated: id only; Mart: aggregates only	Mailbox — primarySmtpAddress/displayName only with legal basis; keep mailbox id as key.
Mail item headers	`from.emailAddress, toRecipients, ccRecipients, subject`	Workforce	RAW: workforce policy; Curated: hashed / counts only; Mart: aggregates only	From/to/subject are PII — hash for reach analytics; never load body.
Calendar event attendees	`organizer.emailAddress, attendees, location`	Workforce	RAW: workforce policy; Curated: counts only; Mart: aggregates only	Treat attendees/organizer as ids; location/subject may be sensitive.
Mail content / attachments	`body, bodyPreview, attachments`	Sensitive	RAW: never store; Curated: never; Mart: never	Never land mail bodies/attachments in the warehouse — meta only for all stages.
Mobile device identifiers	`deviceId, deviceOS, deviceUserAgent, deviceAccessState`	Quasi	RAW: restrict access; Curated: aggregated; Mart: aggregates only	Device meta can re-identify the mailbox owner — treat restrictively.

DSDR discovery

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

Focus	Notes
Match keys	mailbox id, primarySmtpAddress (hashed), internetMessageId, calendar event id, deviceId.
Primary objects	Mailbox, mail item meta, calendar event meta, distribution group, mobile device.
Graph / Compliance Center / journaling copies	Graph mail API, Purview eDiscovery and journaling duplicate mail PII into several stages.
Archive / litigation hold copies	Keep litigation-hold mailboxes separate from active analytics marts.

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 5

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

High Mail item meta without mailbox

Problem: Message meta without mailbox_id breaks mail-volume joins and reach KPIs.
Prevent: Graph extract: always persist mailbox context.
Staff must know: Data: Message without mailbox is an extract error.
Checks: mail_item.mailbox_id IS NULL OR mailbox_id NOT IN dim_mailbox
Fix in source: Re-extract with mailbox context; verify delegate/shared mailbox scope.
Warehouse safety net: Quarantine; Unknown mailbox dim.

Medium Calendar event without organizer

Problem: Events without organizer.emailAddress distort meeting-load analysis.
Prevent: Graph events endpoint: organizer field required in select.
Staff must know: Data: Never drop organizer field from the select.
Checks: calendar_event.organizer_id IS NULL
Fix in source: Re-fetch event; check delegated calendar permissions.
Warehouse safety net: Flag missing_organizer; exclude from per-organizer KPIs.

High Mailbox without directory match

Problem: Mailboxes without an Entra user match break department/org dimension joins.
Prevent: Directory sync before mailbox extract; map shared/room mailboxes separately.
Staff must know: Admins: Keep mailbox provisioning and directory sync in step.
Checks: mailbox.recipientTypeDetails = 'UserMailbox' AND mailbox.id NOT IN dim_entra_user
Fix in source: Re-sync directory; investigate provisioning delay.
Warehouse safety net: Unknown-department bucket; exclude from org rollups.

Medium Mail flow rule without match statistics

Problem: Rules without stats make compliance coverage KPIs impossible.
Prevent: Extract message trace / rule reporting regularly.
Staff must know: Compliance: Do not disable rule reporting feed.
Checks: mail_flow_rule.id NOT IN mail_flow_rule_stats
Fix in source: Verify rule reporting export schedule.
Warehouse safety net: Flag stats_missing; exclude from hit-rate KPIs.

Medium Mobile device without recent sync

Problem: Devices without a sync for a long time are a compliance risk and distort device coverage.
Prevent: Conditional access + Intune compliance policies for mobile devices.
Staff must know: Security: Review stale devices and wipe/block if needed.
Checks: lastSyncAttemptTime < CURRENT_DATE - 90 AND deviceAccessState = 'Allowed'
Fix in source: Block/quarantine stale device; contact owner.
Warehouse safety net: Flag stale_device; exclude from active device KPIs.

MDM / duplicates 2

Mailbox Mailbox duplicates / alias drift

Match keys: id, primarySmtpAddress, aliasAddresses
Prevent in source: Directory as source of truth; controlled alias changes.
Resolve in warehouse: Golden mailbox id; SCD2 primarySmtpAddress.
Survivorship: Active mailbox with latest mail activity wins.

Mailbox / Group Shared mailbox owner mapping drift

Match keys: mailbox id, delegate user id, distribution group id
Prevent in source: Maintain owner register for shared mailboxes; track delegation changes.
Resolve in warehouse: Canonical mailbox id → owner SCD2 map.
Survivorship: Most recent delegation entry wins.

Product settings to improve 3

Compliance → Retention policies Apply retention tags consistently across mailboxes

Why: Retention coverage becomes measurable without storing mail bodies.
How: Default retention policy assignment; audit unassigned mailboxes.

Mail flow → Rules Regular review of transport/DLP rules

Why: Prevents dead rules and keeps compliance coverage KPIs reliable.
How: Quarterly rule audit; disable zero-hit rules after review.

Mobile → Device access policies Enforce ActiveSync/Intune device compliance policies

Why: Stale/non-compliant devices become visible and blockable.
How: Conditional access + quarantine rules; periodic stale-device sweep.

Metadata: what exists & how to get it 4

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

Graph mail / calendar $metadata

Where in product: Microsoft Graph → $metadata; messages, events, mailFolders
How to access: Inventory header fields; exclude body/bodyPreview from the select.
Use for: Extract design, PII tags, join keys (mailbox id, message id).
Watchouts: Never load the attachments field with $expand.

Transport / mail flow rules

Where in product: Exchange Admin Center → Mail flow → Rules; EXO PowerShell Get-TransportRule
How to access: Document rule name, priority, conditions — do not store content matches.
Use for: Compliance dims, hit-rate KPIs, DLP coverage.
Watchouts: Rule conditions may contain keyword lists — treat sensitively.

Retention tags / policies

Where in product: Purview Compliance → Retention; EXO Get-RetentionPolicyTag
How to access: Export tag names, actions and policy assignments.
Use for: Retention coverage dims, compliance reporting.
Watchouts: Retention actions change — SCD2 recommended.

Mobile device / ActiveSync schema

Where in product: EXO Get-MobileDevice / Get-MobileDeviceStatistics
How to access: Inventory device type, sync status and access state.
Use for: Device coverage dims, stale-device DQ.
Watchouts: DeviceUserAgent can carry re-identification risk — keep restricted.

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

Table	Description	Grain	Load
`mailbox` Mailbox	Exchange mailbox — primarySmtpAddress, type; fact anchor for mail activity.	One mailbox (id)	Load
`mail_item_meta` Mail item meta	Message meta — from/to, receivedDateTime, size; never load body/attachments.	One message (id) meta	Load
`calendar_event` Calendar event	Event meta — start/end, organizer, attendees; subject/body may be sensitive.	One calendar event (id)	Load
`distribution_group` Distribution group	Distribution list/M365 group — membership join for mail reach.	One group (id)	Load
`mail_flow_rule` Mail flow rule	Transport rule — name, priority, match count for compliance analytics.	One mail flow rule (id)	Optional
`mobile_device` Mobile device	ActiveSync/managed device — sync status and mailbox join.	One device (deviceId)	Optional
`retention_tag` Retention tag	Retention/compliance tag — policy assignment for mailbox governance.	One retention tag (id)	Optional

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

Entity	Field	Role	Why
Mailbox	`Mailbox.id`	Key	Mailbox join (Graph id)
Mailbox	`Mailbox.primarySmtpAddress`	PII	Primary email / PII
Mailbox	`Mailbox.displayName`	PII	Display name / PII
Mailbox	`Mailbox.recipientTypeDetails`	Dimension	UserMailbox / SharedMailbox / RoomMailbox
Mailbox	`Mailbox.whenCreated`	Measure	Mailbox creation
MailItemMeta	`MailItemMeta.id`	Key	Message join (meta)
MailItemMeta	`MailItemMeta.from.emailAddress`	PII	Sender / PII
MailItemMeta	`MailItemMeta.toRecipients`	PII	Recipients / PII
MailItemMeta	`MailItemMeta.receivedDateTime`	Measure	Received timestamp
MailItemMeta	`MailItemMeta.hasAttachments`	Dimension	Attachment flag (not content)
MailItemMeta	`MailItemMeta.size`	Measure	Message size (bytes)
CalendarEvent	`CalendarEvent.id`	Key	Event join
CalendarEvent	`CalendarEvent.subject`	PII	Subject often sensitive
CalendarEvent	`CalendarEvent.organizer.emailAddress`	PII	Organizer / PII
CalendarEvent	`CalendarEvent.attendees`	PII	Attendee list / PII
CalendarEvent	`CalendarEvent.start.dateTime`	Measure	Event start
CalendarEvent	`CalendarEvent.end.dateTime`	Measure	Event end / duration
CalendarEvent	`CalendarEvent.isCancelled`	Dimension	Cancelled flag
DistributionGroup	`DistributionGroup.id`	Key	Group join
DistributionGroup	`DistributionGroup.displayName`	Dimension	Group display name
DistributionGroup	`DistributionGroup.memberCount`	Measure	Member count
MailFlowRule	`MailFlowRule.id`	Key	Rule join
MailFlowRule	`MailFlowRule.name`	Dimension	Rule name
MailFlowRule	`MailFlowRule.priority`	Dimension	Evaluation order
MobileDevice	`MobileDevice.deviceId`	Key	Device join
MobileDevice	`MobileDevice.deviceType`	Dimension	iOS / Android / Outlook Mobile
MobileDevice	`MobileDevice.lastSyncAttemptTime`	Measure	Last sync
RetentionTag	`RetentionTag.id`	Key	Tag join
RetentionTag	`RetentionTag.retentionAction`	Dimension	Delete / MoveToArchive / Keep

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

Skip tables / objects

Table	Category	Why
`Mail body / bodyPreview / attachments`	System	Mail content — never land; header meta only.
`Calendar event body / attachments`	System	Event content — often confidential; time/organizer meta only.
`Journaling / eDiscovery export payloads`	System	Legal-hold exports — separate from analytics RAW.
`Message trace IP / routing detail (bulk)`	System	Bulk routing metadata — security-sensitive, short retention.

Skip fields / content

Mail body/attachments in any stage — Never into the warehouse — meta is enough for all KPIs.
From/To/Subject cleartext in marts — mailbox id / hashed values are enough for reach KPIs.
Calendar attendee/organizer cleartext (bulk) — Workforce PII — counts instead of cleartext.
eDiscovery / litigation hold exports — Legal — not in default analytics stages.
Mobile device identifiers in public marts — Re-identification risk — keep restricted.

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

Landing (RAW)

RAW mailbox + mail item landing

Graph mail shape — from/to = workforce PII; no bodies/attachments.

-- Warehouse-neutral RAW from Microsoft Graph mail extract
CREATE TABLE raw_exchange_mailbox (
  mailbox_id           VARCHAR,
  primary_smtp_address VARCHAR, -- workforce PII
  display_name         VARCHAR, -- workforce PII
  recipient_type       VARCHAR, -- UserMailbox | SharedMailbox | RoomMailbox
  when_created         TIMESTAMP,
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_exchange_mail_item_meta (
  message_id           VARCHAR,
  mailbox_id           VARCHAR,
  from_email           VARCHAR, -- PII
  received_at          TIMESTAMP,
  has_attachments      BOOLEAN,
  size_bytes           INT,
  folder               VARCHAR,
  _loaded_at           TIMESTAMP
  -- no body, no bodyPreview, no attachment content
);

RAW calendar event + mobile device landing

Event meta — subject/attendees sensitive; device meta without content.

CREATE TABLE raw_exchange_calendar_event (
  event_id             VARCHAR,
  mailbox_id           VARCHAR,
  organizer_email      VARCHAR, -- PII
  subject              VARCHAR, -- PII, often sensitive
  start_at             TIMESTAMP,
  end_at               TIMESTAMP,
  is_cancelled         BOOLEAN,
  attendee_count       INT,
  _loaded_at           TIMESTAMP
  -- no event body/description
);

CREATE TABLE raw_exchange_mobile_device (
  device_id            VARCHAR,
  mailbox_id           VARCHAR,
  device_type          VARCHAR,
  last_sync_at         TIMESTAMP,
  access_state         VARCHAR,
  _loaded_at           TIMESTAMP
);

Curated

Curated fact exchange mail item

Volume grain — no from/to cleartext; meta flags only.

CREATE TABLE curated_fct_exchange_mail_item AS
SELECT
  m.message_id,
  m.mailbox_id,
  m.received_at,
  m.has_attachments,
  m.size_bytes,
  m.folder
  -- omit from_email from default analytics facts
FROM raw_exchange_mail_item_meta m
WHERE m.mailbox_id IS NOT NULL;

Curated dim mailbox / calendar fact

Mailbox without primarySmtpAddress; calendar event without subject.

CREATE TABLE curated_dim_exchange_mailbox AS
SELECT
  mailbox_id,
  recipient_type,
  when_created
  -- omit primary_smtp_address, display_name from default analytics dims
FROM raw_exchange_mailbox;

CREATE TABLE curated_fct_exchange_calendar_event AS
SELECT
  e.event_id,
  e.mailbox_id,
  e.start_at,
  e.end_at,
  e.is_cancelled,
  e.attendee_count
  -- omit organizer_email, subject from default analytics facts
FROM raw_exchange_calendar_event e
WHERE e.mailbox_id IS NOT NULL;

Curated measure SELECTs

Active mailboxes, emails sent, meetings scheduled — adapt period filters.

-- Active user mailboxes (snapshot)
SELECT COUNT(*) AS mailboxes_active
FROM curated_dim_exchange_mailbox
WHERE recipient_type = 'UserMailbox';

-- Emails sent in period (meta count)
SELECT COUNT(*) AS emails_sent
FROM curated_fct_exchange_mail_item
WHERE folder = 'SentItems'
  AND received_at BETWEEN :period_start AND :period_end;

-- Meetings scheduled in period
SELECT COUNT(*) AS meetings_scheduled
FROM curated_fct_exchange_calendar_event
WHERE is_cancelled = FALSE
  AND start_at 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 →