Microsoft Teams — Sources — Binom Governance

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

Example

Messages sent

How many chat/channel messages were sent?

COUNT(*) FROM chat_message_meta WHERE createdDateTime IN period

Grain: Chat message meta
Fields: Chat Message.id, Chat Message.createdDateTime, Chat Message.from.user
Dimensions: team, channel, workload

Graph channel messages / chats — do not load body by default.

Adapt: Clarify system/app messages and importance filters.

Example

Active teams

How many teams had activity in the period?

COUNT(DISTINCT team_id) FROM activity WHERE activity_date IN period

Grain: Team
Fields: Team.id, Chat Message.createdDateTime, Online Meeting.startDateTime
Dimensions: team, workload

Microsoft 365 usage / Graph reports as alternative to message bodies.

Adapt: Activity = messages and/or meetings — lock the definition.

Meetings held

How many online meetings were held?

COUNT(*) FROM online_meeting WHERE startDateTime IN period

Grain: Online meeting
Fields: Online Meeting.id, Online Meeting.startDateTime
Dimensions: team, meeting_type, workload

Graph onlineMeetings / calendar — treat no-shows separately.

Adapt: Filter scheduled vs held and channel meetings.

Meeting minutes

How many meeting minutes were held?

SUM(DATEDIFF(minute, startDateTime, endDateTime)) FROM online_meeting WHERE startDateTime IN period

Grain: Online meeting
Fields: Online Meeting.startDateTime, Online Meeting.endDateTime
Dimensions: team, meeting_type, workload

Distinguish attendee-minutes (attendance) vs meeting duration.

Adapt: Set caps for extreme duration and overlapping meetings.

Calls count

How many calls occurred in the period?

COUNT(*) FROM call WHERE startDateTime IN period

Grain: Call
Fields: Call.id, Call.startDateTime, Call.modalities
Dimensions: team, workload, meeting_type

Call Records API — skip recordings and transcripts.

Adapt: Report 1:1 vs group calls and PSTN separately.

Formula overview

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

Label	Formula	Grain	Fields
Messages sent	`COUNT(*) FROM chat_message_meta WHERE createdDateTime IN period`	Chat message meta	Chat Message.id, Chat Message.createdDateTime, Chat Message.from.user
Active teams	`COUNT(DISTINCT team_id) FROM activity WHERE activity_date IN period`	Team	Team.id, Chat Message.createdDateTime, Online Meeting.startDateTime
Meetings held	`COUNT(*) FROM online_meeting WHERE startDateTime IN period`	Online meeting	Online Meeting.id, Online Meeting.startDateTime
Meeting minutes	`SUM(DATEDIFF(minute, startDateTime, endDateTime)) FROM online_meeting WHERE startDateTime IN period`	Online meeting	Online Meeting.startDateTime, Online Meeting.endDateTime
Calls count	`COUNT(*) FROM call WHERE startDateTime IN period`	Call	Call.id, Call.startDateTime, Call.modalities

Id	Label	Grain	Notes
`team`	Team	team id	Watch archived teams and guests in membership.
`channel`	Channel	channel id	Use membershipType for private/shared splits.
`meeting_type`	Meeting type	scheduled / ad-hoc / channel	Derive from Graph onlineMeeting / calendar event.
`workload`	Workload	chat / meetings / calling / apps	Combine usage reports and tab/app signals.

PII hotspots

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

Entity	Fields	Classification	Stage treatment	Treatment
User / Entra ID	`id, userPrincipalName, mail, displayName, mobilePhone`	Workforce	RAW: workforce policy; Curated: user_id only; Mart: aggregates only	Teams/Entra users — UPN/mail only with legal basis; id as key.
Chat / channel message body	`body.content, attachments, mentions`	Quasi	RAW: rarely load; Curated: never cleartext; Mart: never	Do not store message bodies by default — activity facts without text.
1:1 / group chat	`chat members, chat message body, chatTopic`	Direct	RAW: restrict access; Curated: drop; Mart: never	Private chats — default out of scope for analytics.
Team membership / owners	`members, owners, guest users`	Workforce	RAW: workforce policy; Curated: aggregates / hashed; Mart: aggregates only	Membership reveals org structure — tight access.
Purview / retention exports	`eDiscovery content, retention labeled items, compliance copies`	Quasi	RAW: rarely load; Curated: never in analytics; Mart: never	Purview exports are compliance — do not mix with activity marts.
Meeting recordings / transcripts	`recording content, transcript text, attendance reports with names`	Quasi	RAW: rarely load; Curated: drop/redact; Mart: never	Recordings/transcripts — default skip; attendance aggregates only.

DSDR discovery

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

Focus	Notes
Match keys	user id, userPrincipalName, teamId, channelId, chatId, tenantId.
Primary objects	Team, channel, membership, message metadata (no body), meeting metadata.
Microsoft Graph exports	Graph messages/chats and change notifications can deliver bodies — allowlist.
Purview / eDiscovery copies	Strictly separate compliance copies from analytics stages.

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 6

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

High Chat without team/channel context

Problem: Activity without teamId/channelId cannot slice collaboration by team.
Prevent: Prefer channel messages; skip chat exports for analytics.
Staff must know: Data: Channel activity ≠ private chat — context keys required.
Checks: teamId IS NULL AND channelId IS NULL on channel_activity grain
Fix in source: Extract only team/channel scoped Graph calls.
Warehouse safety net: Quarantine orphan activity; exclude from team KPIs.

High Team without owner

Problem: Teams without owner break lifecycle and governance KPIs.
Prevent: Team create policy: at least one owner; guest ≠ owner.
Staff must know: Admins: Every team needs an owner.
Checks: owner_count = 0 on team WHERE isArchived = false
Fix in source: Teams admin center policy; Graph ownership fix.
Warehouse safety net: Unknown-owner dim; flag orphan teams.

High Message body in warehouse

Problem: Cleartext bodies in the warehouse create DSDR/retention risk.
Prevent: Graph extract: counts/metadata only; skip body.content by default.
Staff must know: Data: No message bodies — message_count facts.
Checks: EXISTS body.content in curated OR raw without legal flag
Fix in source: App permission review; remove ChannelMessage.Read for analytics if unused.
Warehouse safety net: Drop body columns; rebuild activity aggregates.

Medium Archived team in active KPIs

Problem: Archived teams in active counts distort adoption.
Prevent: isArchived in extract and KPI definition.
Staff must know: Analysts: Filter archived teams from active inventory.
Checks: isArchived = true AND included_in_active_inventory = true
Fix in source: Archive playbook in Teams admin center.
Warehouse safety net: Exclude archived from active team count.

High Purview retention mismatch

Problem: Warehouse keeps activity longer than Purview retention — compliance gap.
Prevent: Warehouse TTL ≤ Purview labels; document exceptions.
Staff must know: Legal/data: Retention aligned with Purview.
Checks: activity fact age > purview_retention_days
Fix in source: Purview retention label review.
Warehouse safety net: TTL purge jobs on activity grain.

Medium Channel without team

Problem: Channels without teamId are orphaned — hierarchy reports break.
Prevent: Graph create channel always under team; validate import.
Staff must know: Admins: Channel always belongs to a team.
Checks: teamId IS NULL on channel
Fix in source: Repair via Graph; delete orphans.
Warehouse safety net: Quarantine; exclude from hierarchy KPIs.

MDM / duplicates 2

User / Entra ID User duplicates / guest

Match keys: id, userPrincipalName, mail
Prevent in source: Entra ID as source of truth; guest lifecycle policy.
Resolve in warehouse: Golden user id; UPN-hash merge map.
Survivorship: Member account wins over guest on same mail hash.

Team Team rename / archive

Match keys: teamId, displayName, mailNickname
Prevent in source: Stable teamId as key; naming convention for renames.
Resolve in warehouse: SCD2 team name; teamId canonical.
Survivorship: teamId wins over displayName.

Product settings to improve 3

Teams admin center Team/channel policies and archive rules

Why: Ownership and active inventory stay controllable.
How: Policies review; owner required; archive playbook.

Microsoft Purview retention Align retention labels with warehouse TTL

Why: No analytics copies longer than compliance allows.
How: Document Purview labels; configure TTL jobs.

Microsoft Graph app permissions Least privilege — no message-read without use case

Why: Prevents cleartext bodies in extract.
How: App registration review; ChannelMessage.Read only if needed.

Metadata: what exists & how to get it 4

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

Microsoft Graph $metadata

Where in product: https://graph.microsoft.com/v1.0/$metadata (Teams entities)
How to access: Parse $metadata; tag team/channel/chat entities and properties.
Use for: Extract design, allowlist, join keys.
Watchouts: Message body properties easily available — exclude by default.

Teams admin center

Where in product: Microsoft Teams admin center → Teams / Users / Policies
How to access: Export policies and team inventory.
Use for: Governance baseline, archive status, owner inventory.
Watchouts: Admin center ≠ full Graph schema.

Purview retention

Where in product: Microsoft Purview → Data lifecycle management / Retention
How to access: Document retention labels and locations for Teams.
Use for: Warehouse TTL, compliance alignment.
Watchouts: Do not use eDiscovery exports as analytics source.

Graph change notifications

Where in product: Microsoft Graph subscriptions / change notifications
How to access: Subscriptions on metadata events; do not store message content.
Use for: Near-real-time activity signals without bodies.
Watchouts: Notifications can include bodies — payload allowlist.

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

Table	Description	Grain	Load
`team` Team	Microsoft 365 group / team — root dimension for active teams.	One team	Load
`channel` Channel	Team channel — standard / private; message and meeting context.	One channel	Load
`chat_message` Chat message	Channel/chat message — meta for sent KPIs; body bulk often skip.	One message	Optional
`call` Call	Calls record — calls count and duration; skip recordings.	One call	Optional
`online_meeting` Online meeting	Online meeting — meetings held and meeting minutes.	One meeting	Load
`user` User	Entra/Graph user — UPN/mail are PII; from.user join.	One user (id)	Load
`membership` Membership	Team/channel membership — reach and active-user context.	User in team/channel	Optional
`tab` Tab	Channel tab — optional adoption signal (apps/workload).	One tab	Optional

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

Entity	Field	Role	Why
Team	`Team.id`	Key	Graph team id
Team	`Team.displayName`	Dimension	Team label
Team	`Team.visibility`	Dimension	public / private
Channel	`Channel.id`	Key	Graph channel id
Channel	`Channel.displayName`	Dimension	Channel name
Channel	`Channel.membershipType`	Dimension	standard / private / shared
Chat Message	`Chat Message.id`	Key	Message id
Chat Message	`Chat Message.createdDateTime`	Measure	Created time / period grain
Chat Message	`Chat Message.from.user`	Dimension	Author (Graph from.user)
Chat Message	`Chat Message.importance`	Dimension	normal / high / urgent
Chat Message	`Chat Message.body.content`	PII	Chat content / PII — bulk often skip
Chat Message	`Chat Message.channelIdentity`	Dimension	Channel context
Call	`Call.id`	Key	Call join
Call	`Call.startDateTime`	Measure	Call start
Call	`Call.endDateTime`	Measure	Call end
Call	`Call.modalities`	Dimension	audio / video / …
Online Meeting	`Online Meeting.id`	Key	Meeting join
Online Meeting	`Online Meeting.startDateTime`	Measure	Meeting start
Online Meeting	`Online Meeting.endDateTime`	Measure	Meeting end / minutes
Online Meeting	`Online Meeting.joinWebUrl`	Dimension	Join URL (sensitive)
User	`User.id`	Key	Graph user id
User	`User.userPrincipalName`	PII	UPN / PII
User	`User.mail`	PII	Email / PII
User	`User.displayName`	PII	Display name / quasi-PII
Membership	`Membership.userId`	Key	Member join
Membership	`Membership.roles`	Dimension	owner / member / guest
Tab	`Tab.id`	Key	Tab join
Tab	`Tab.teamsAppId`	Dimension	Workload / app

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

Skip tables / objects

Table	Category	Why
`Chat / channel message bodies (bulk)`	System	Cleartext — default never in warehouse marts.
`1:1 and group chat history`	System	Private chats — out of scope for analytics.
`Meeting recordings / transcript blobs`	System	Media and free text — expensive and sensitive.
`Purview eDiscovery export payloads`	System	Compliance-only — not business KPI.

Skip fields / content

Message body cleartext in curated marts — Only message_count / activity aggregates.
Private chat content — Never without legal/compliance approval.
UPN / mail cleartext in marts — user id is enough for activity KPIs.
Meeting transcript / recording content — Default skip.
Test teams / guest spam channels — Keep prod activity marts clean.

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

Landing (RAW)

RAW team + channel landing

Graph metadata — teamId/channelId keys; no message bodies.

-- Warehouse-neutral RAW from Microsoft Graph extract (metadata only)
CREATE TABLE raw_teams_team (
  team_id          VARCHAR,
  display_name     VARCHAR,
  description      VARCHAR,
  is_archived      BOOLEAN,
  visibility       VARCHAR,
  created_at       TIMESTAMP,
  _loaded_at       TIMESTAMP
);

CREATE TABLE raw_teams_channel (
  channel_id       VARCHAR,
  team_id          VARCHAR,
  display_name     VARCHAR,
  membership_type  VARCHAR, -- standard, private, shared
  created_at       TIMESTAMP,
  _loaded_at       TIMESTAMP
);

RAW user + channel activity landing

Activity without body.content — UPN/mail = PII in RAW; curated aggregates only.

CREATE TABLE raw_teams_user (
  user_id          VARCHAR,
  display_name     VARCHAR,
  user_principal_name VARCHAR, -- workforce PII
  mail             VARCHAR, -- workforce PII
  account_enabled  BOOLEAN,
  _loaded_at       TIMESTAMP
);

-- Prefer aggregates over chat/channel message text
CREATE TABLE raw_teams_channel_activity_daily (
  activity_date    DATE,
  team_id          VARCHAR,
  channel_id       VARCHAR,
  message_count    INT, -- aggregate only — no message text
  active_user_count INT,
  _loaded_at       TIMESTAMP
);

Curated

Curated fact teams channel activity

message_count aggregates with team/channel context — no cleartext.

CREATE TABLE curated_fct_teams_channel_activity AS
SELECT
  a.activity_date,
  a.team_id,
  a.channel_id,
  a.message_count,
  a.active_user_count,
  t.display_name AS team_name,
  t.is_archived,
  c.display_name AS channel_name,
  c.membership_type
FROM raw_teams_channel_activity_daily a
JOIN raw_teams_team t ON t.team_id = a.team_id
JOIN raw_teams_channel c ON c.channel_id = a.channel_id AND c.team_id = a.team_id
WHERE a.team_id IS NOT NULL
  AND a.channel_id IS NOT NULL;

Curated dim team / user

Team inventory; user without UPN/mail in default marts.

CREATE TABLE curated_dim_teams_team AS
SELECT
  team_id,
  display_name,
  is_archived,
  visibility,
  created_at
FROM raw_teams_team;

CREATE TABLE curated_dim_teams_user AS
SELECT
  user_id,
  display_name,
  account_enabled
  -- omit user_principal_name, mail from default analytics dims
FROM raw_teams_user;

Curated measure SELECTs

Active teams, message volume, active users — respect Purview retention.

-- Active (non-archived) teams
SELECT COUNT(*) AS active_teams
FROM curated_dim_teams_team
WHERE is_archived = FALSE;

-- Channel message volume in period (aggregates only)
SELECT SUM(message_count) AS message_volume
FROM curated_fct_teams_channel_activity
WHERE activity_date BETWEEN :period_start AND :period_end;

-- Active user events in period
SELECT SUM(active_user_count) AS active_user_events
FROM curated_fct_teams_channel_activity
WHERE activity_date 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 →