SharePoint — Sources — Binom Governance

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

Example

Sites active

How many sites had modifications in the period?

COUNT(DISTINCT site.id) WHERE lastModifiedDateTime IN period OR driveItem.lastModifiedDateTime IN period

Grain: Site with activity
Fields: Site.id, Site.lastModifiedDateTime, DriveItem.lastModifiedDateTime
Dimensions: site, drive, sensitivity

Activity via site or item lastModified — lock definition.

Adapt: Optionally exclude OneDrive personal sites.

Example

Files created

How many files were created in the period?

COUNT(*) FROM drive_item_meta WHERE file IS NOT NULL AND createdDateTime IN period

Grain: DriveItem (file)
Fields: DriveItem.id, DriveItem.createdDateTime, DriveItem.file.mimeType
Dimensions: site, drive, content_type, sensitivity

file facet set = file (exclude folders).

Adapt: Optionally separate uploads vs Office create via createdBy app.

Files modified

How many files were modified in the period?

COUNT(*) FROM drive_item_meta WHERE file IS NOT NULL AND lastModifiedDateTime IN period

Grain: DriveItem (file) modified
Fields: DriveItem.id, DriveItem.lastModifiedDateTime, DriveItem.file.mimeType
Dimensions: site, drive, content_type

lastModifiedDateTime; avoid double-counting create-only when created=modified.

Adapt: Lock distinct files vs modification events.

Sharing links active

How many sharing links are active (not expired)?

COUNT(*) FROM share_link WHERE expirationDateTime IS NULL OR expirationDateTime > now()

Grain: Active share link
Fields: ShareLink.id, ShareLink.link.scope, ShareLink.expirationDateTime
Dimensions: site, drive, sensitivity

Scope anonymous vs organization for risk slices.

Adapt: Variant: only external/anonymous links as risk KPI.

Lists count

How many lists exist (snapshot or new in period)?

COUNT(*) FROM list

Grain: List
Fields: List.id, List.displayName, List.list.template, Site.id
Dimensions: site, content_type

Possibly exclude system lists (e.g. form templates).

Adapt: Only lists with item activity in the period.

Formula overview

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

Label	Formula	Grain	Fields
Sites active	`COUNT(DISTINCT site.id) WHERE lastModifiedDateTime IN period OR driveItem.lastModifiedDateTime IN period`	Site with activity	Site.id, Site.lastModifiedDateTime, DriveItem.lastModifiedDateTime
Files created	`COUNT(*) FROM drive_item_meta WHERE file IS NOT NULL AND createdDateTime IN period`	DriveItem (file)	DriveItem.id, DriveItem.createdDateTime, DriveItem.file.mimeType
Files modified	`COUNT(*) FROM drive_item_meta WHERE file IS NOT NULL AND lastModifiedDateTime IN period`	DriveItem (file) modified	DriveItem.id, DriveItem.lastModifiedDateTime, DriveItem.file.mimeType
Sharing links active	`COUNT(*) FROM share_link WHERE expirationDateTime IS NULL OR expirationDateTime > now()`	Active share link	ShareLink.id, ShareLink.link.scope, ShareLink.expirationDateTime
Lists count	`COUNT(*) FROM list`	List	List.id, List.displayName, List.list.template, Site.id

Id	Label	Grain	Notes
`site`	Site	site.id / webUrl	Primary slice for activity and sharing.
`drive`	Drive	drive.id / name	Library level under site; separate personal vs documentLibrary.
`content_type`	Content type	listItem.contentType / file.mimeType	Do not mix MIME and list content types without mapping.
`sensitivity`	Sensitivity	sensitivityLabel (notes)	MIP/sensitivity labels when available; else proxy via share-link scope.

PII hotspots

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

Entity	Fields	Classification	Stage treatment	Treatment
Site / drive item metadata	`createdBy, lastModifiedBy, webUrl, name, listItem fields`	Workforce	RAW: workforce policy; Curated: user ids only; Mart: aggregates only	Document metadata — author as user id; filenames can contain PII.
File / page content	`driveItem content, file binary, page canvas body, version content`	Quasi	RAW: never store; Curated: never; Mart: never	Never land document content — strictly separate metadata vs content.
Sharing links / permissions	`sharingLink, permission grants, sharedWith identities, anonymous links`	Sensitive	RAW: restrict access; Curated: counts/types only; Mart: no link URLs	Sharing links can contain tokens — never full URLs; aggregate type/scope.
Sensitivity / Purview labels	`sensitivityLabel, retentionLabel, label assignment actor`	Sensitive	RAW: label ids ok; Curated: label name/policy; Mart: aggregates	Sensitivity labels are governance signals — never load underlying content.
List columns with personal data	`Person/Group columns, email columns, custom PII fields`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Inventory site columns — person fields are often a second PII surface.

DSDR discovery

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

Focus	Notes
Match keys	site id, drive id, item id, listItem id, user id, sensitivity label id.
Primary objects	Site, drive, driveItem metadata, list, permission, sensitivity label.
Graph / Search / Export copies	Graph sites/drives and Search exports duplicate author and sharing PII.
Tenant / geo copies	Keep multi-geo and archive site retention separate from content KPIs.

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 DriveItem / ListItem without site

Problem: Items without site_id break collaboration joins and site activity KPIs.
Prevent: Graph extract: always persist parentReference.siteId.
Staff must know: Data: Item without site is an extract error.
Checks: item.site_id IS NULL OR site_id NOT IN dim_site
Fix in source: Re-extract with site context; repair deleted site tombstones.
Warehouse safety net: Quarantine; Unknown site dim.

High Anonymous sharing links without expiry

Problem: Anon links without expiry raise leak risk; full URLs must never land.
Prevent: Sharing policy: anonymous links require expiry; prefer specific people.
Staff must know: Admins: Restrict anon sharing and enforce expiry.
Checks: link_scope = 'anonymous' AND (expiry_at IS NULL OR expiry_at < CURRENT_TIMESTAMP)
Fix in source: Tenant sharing settings; revoke stale links.
Warehouse safety net: Flag anon_sharing_risk; store type/scope only — no URL.

Medium Item without sensitivity label when policy requires

Problem: Missing labels despite policy weaken Purview coverage.
Prevent: Auto-labeling; default label on libraries; required for classified sites.
Staff must know: Owners: Set labels on libraries with required policy.
Checks: requires_sensitivity_label = TRUE AND sensitivity_label_id IS NULL
Fix in source: Apply label / auto-label policy.
Warehouse safety net: Flag unlabeled; exclude from compliant coverage numerator.

High Drive without site

Problem: Drives without site_id break storage and activity rollups.
Prevent: Fully extract Graph sites/drives hierarchy.
Staff must know: Data: Always attach drive to site.
Checks: drive.site_id IS NULL
Fix in source: Re-sync site → drives.
Warehouse safety net: Quarantine; Unknown site.

Medium Permission grant without item/site

Problem: Orphan permissions distort sharing coverage and access reviews.
Prevent: Permission extract with parent item/site keys; soft-delete handling.
Staff must know: Governance: Review broken inheritance and orphans.
Checks: permission.item_id IS NULL AND permission.site_id IS NULL
Fix in source: Re-extract permissions with parents; revoke orphans.
Warehouse safety net: Flag orphan_permission; exclude from coverage.

Medium Duplicate item ids in extract

Problem: Duplicate item_id distort activity counts and storage facts.
Prevent: Idempotent load; check delta token overlap.
Staff must know: Data: Keep item_id unique per drive.
Checks: COUNT(*) GROUP BY drive_id, item_id HAVING COUNT > 1
Fix in source: Re-extract; investigate Graph delta pagination.
Warehouse safety net: Dedup by drive_id+item_id; keep latest _loaded_at.

MDM / duplicates 2

Site Site duplicates / URL rename

Match keys: site id, webUrl, siteCollection hostname+path
Prevent in source: Stable site id; capture URL changes via Graph.
Resolve in warehouse: Golden site id; webUrl SCD2.
Survivorship: Active site with latest activity wins.

Sensitivity label Sensitivity label taxonomy drift

Match keys: label id, name, parent policy
Prevent in source: Purview label governance; controlled rename.
Resolve in warehouse: Canonical label id; SCD2 label name.
Survivorship: Current Purview label wins over legacy names.

Product settings to improve 3

SharePoint Admin → Sharing External/anonymous sharing limits and link expiry

Why: Sharing risk KPIs become controllable without storing link URLs.
How: Tenant sharing settings; default link type; expiry required.

Microsoft Purview → Sensitivity labels Label publishing and auto-labeling for sites/libraries

Why: Label coverage stays measurable — without document bodies.
How: Publish labels; default library label; extract label ids only.

SharePoint Admin → Sites / Lifecycle Site lifecycle / inactive site policies

Why: Orphan sites and storage growth stay manageable.
How: Inactive site detection; archive/delete cadence.

Metadata: what exists & how to get it 4

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

Graph sites / drives

Where in product: Microsoft Graph → sites, drives, driveItems (metadata select)
How to access: Site/drive/item metadata allowlist; avoid content select.
Use for: Activity facts, storage dims, join keys.
Watchouts: Never load /content or version streams.

Site columns / content types

Where in product: Site Settings → Site columns / Content types; Graph list columns
How to access: Inventory column types; tag person/email columns as PII.
Use for: Extract allowlist, PII scan, content type dims.
Watchouts: Custom person columns — default drop in marts.

Sharing / Purview labels

Where in product: Sharing Admin Center; Purview sensitivity labels; Graph permissions
How to access: Document link types, scopes, label ids — no URLs.
Use for: Sharing risk KPIs, label coverage, permission counts.
Watchouts: Never store sharing link tokens.

Search schema

Where in product: SharePoint Admin → More features → Search → Manage search schema
How to access: Managed/crawled properties as field reference — do not export bodies.
Use for: Field discovery, classification signals, metadata coverage.
Watchouts: Search ≠ RAW landing — no indexed document bodies.

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

Table	Description	Grain	Load
`site` Site	SharePoint site — id, webUrl, name; fact anchor for activity.	One site (id)	Load
`drive` Drive	Document library drive — site join; do not load binaries.	One drive (id)	Load
`drive_item_meta` Drive item meta	File/folder meta — name, mime, lastModified; skip content stream.	One driveItem (id)	Load
`list` List	SharePoint list — schema/counts; no free-text field dumps.	One list (id)	Load
`list_item_meta` List item meta	List item meta — id, contentType, modified; free-text fields selective.	One list item (id)	Optional
`user` User	Site/drive user — mail is PII; Graph user join.	One user (id)	Load
`permission` Permission	Item/site permission — roles and principals for sharing analytics.	One permission (id)	Optional
`share_link` Share link	Sharing link — scope, type, expiry; targets may be PII.	One share link (id)	Load

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

Entity	Field	Role	Why
Site	`Site.id`	Key	Site join (Graph id)
Site	`Site.webUrl`	Dimension	Site URL
Site	`Site.displayName`	Dimension	Site name
Site	`Site.createdDateTime`	Measure	Site created
Site	`Site.lastModifiedDateTime`	Measure	Last site modification
Drive	`Drive.id`	Key	Drive join
Drive	`Drive.name`	Dimension	Library name
Drive	`Drive.driveType`	Dimension	documentLibrary / personal …
DriveItem	`DriveItem.id`	Key	DriveItem join
DriveItem	`DriveItem.name`	Dimension	File name (meta)
DriveItem	`DriveItem.file.mimeType`	Dimension	MIME / content type
DriveItem	`DriveItem.size`	Measure	Size in bytes (meta)
DriveItem	`DriveItem.createdDateTime`	Measure	File created
DriveItem	`DriveItem.lastModifiedDateTime`	Measure	File modified
DriveItem	`DriveItem.createdBy.user.id`	Dimension	Creator join
List	`List.id`	Key	List join
List	`List.displayName`	Dimension	List name
List	`List.list.template`	Dimension	List template / type
ListItem	`ListItem.id`	Key	List item join
ListItem	`ListItem.contentType.name`	Dimension	Content type
ListItem	`ListItem.lastModifiedDateTime`	Measure	Item modified
User	`User.id`	Key	User join
User	`User.mail`	PII	Email / PII
User	`User.displayName`	PII	Display name / PII
Permission	`Permission.id`	Key	Permission join
Permission	`Permission.roles`	Dimension	read / write / owner
Permission	`Permission.grantedToIdentities`	PII	Sharing targets may be PII
ShareLink	`ShareLink.id`	Key	Share link join
ShareLink	`ShareLink.link.scope`	Dimension	anonymous / organization / users
ShareLink	`ShareLink.link.type`	Dimension	view / edit
ShareLink	`ShareLink.expirationDateTime`	Measure	Link expiry
DriveItem	`DriveItem.sensitivityLabel`	Dimension	Sensitivity label (if present)

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

Skip tables / objects

Table	Category	Why
`DriveItem / page content binaries`	System	Never land content — metadata enough for activity KPIs.
`Sharing link full URLs / tokens`	System	Tokenized links — leak risk; type/count at most.
`File version content streams`	System	Version binaries — expensive and sensitive.
`Search indexed document bodies`	System	Search bodies duplicate content PII — schema/counts only.

Skip fields / content

Document / page file content — Metadata vs content — never content in the warehouse.
Sharing link URLs / tokens — Never store — aggregate sharing type/scope.
Author email / displayName cleartext in marts — user id is enough for collaboration KPIs.
Person column cleartext (bulk) — PII — allowlist columns or drop.
Version content streams — Cost and sensitivity without KPI value.

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

Landing (RAW)

RAW site + drive landing

Graph sites/drives — metadata only; no document content.

-- Warehouse-neutral RAW from Microsoft Graph SharePoint extract
CREATE TABLE raw_spo_site (
  site_id              VARCHAR,
  site_name            VARCHAR,
  web_url              VARCHAR,
  created_at           TIMESTAMP,
  _loaded_at           TIMESTAMP
);

CREATE TABLE raw_spo_drive (
  drive_id             VARCHAR,
  site_id              VARCHAR,
  drive_name           VARCHAR,
  drive_type           VARCHAR,
  _loaded_at           TIMESTAMP
);

RAW item metadata + sharing landing

Item metadata vs content; sharing as type/scope — no link URLs/tokens.

CREATE TABLE raw_sharepoint_item (
  item_id              VARCHAR,
  drive_id             VARCHAR,
  site_id              VARCHAR,
  item_name            VARCHAR,
  created_by_user_id   VARCHAR, -- workforce key
  last_modified_at     TIMESTAMP,
  sensitivity_label_id VARCHAR,
  size_bytes           BIGINT,
  _loaded_at           TIMESTAMP
  -- no file/page content binary
);

CREATE TABLE raw_sharepoint_sharing (
  permission_id        VARCHAR,
  item_id              VARCHAR,
  site_id              VARCHAR,
  link_scope           VARCHAR, -- anonymous | organization | users
  link_type            VARCHAR, -- view | edit
  expiry_at            TIMESTAMP,
  _loaded_at           TIMESTAMP
  -- never store sharing link URL / token
);

Curated

Curated fact sharepoint item activity

Activity/metadata grain — site required; no content bodies.

CREATE TABLE curated_fct_sharepoint_item_activity AS
SELECT
  i.item_id,
  i.drive_id,
  i.site_id,
  i.created_by_user_id,
  i.last_modified_at,
  i.sensitivity_label_id,
  i.size_bytes,
  CASE WHEN i.sensitivity_label_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_sensitivity_label
FROM raw_sharepoint_item i
WHERE i.site_id IS NOT NULL;

Curated dim site / sharing fact

Site dim; sharing risk flags without link URLs.

CREATE TABLE curated_dim_sharepoint_site AS
SELECT
  site_id,
  site_name,
  created_at
  -- web_url optional / restricted
FROM raw_spo_site;

CREATE TABLE curated_fct_sharepoint_sharing AS
SELECT
  s.permission_id,
  s.item_id,
  s.site_id,
  s.link_scope,
  s.link_type,
  s.expiry_at,
  CASE
    WHEN s.link_scope = 'anonymous'
     AND (s.expiry_at IS NULL OR s.expiry_at < CURRENT_TIMESTAMP)
    THEN TRUE
    ELSE FALSE
  END AS anon_sharing_risk
FROM raw_sharepoint_sharing s
WHERE s.site_id IS NOT NULL OR s.item_id IS NOT NULL;

Curated measure SELECTs

Active items, label coverage, anon sharing risk — adapt period filters.

-- Items modified in period
SELECT COUNT(*) AS items_modified
FROM curated_fct_sharepoint_item_activity
WHERE last_modified_at BETWEEN :period_start AND :period_end;

-- Sensitivity label coverage
SELECT
  AVG(CASE WHEN has_sensitivity_label THEN 1.0 ELSE 0.0 END) AS label_coverage
FROM curated_fct_sharepoint_item_activity;

-- Anonymous sharing risk count (no URLs stored)
SELECT COUNT(*) AS anon_sharing_risk_count
FROM curated_fct_sharepoint_sharing
WHERE anon_sharing_risk = TRUE;

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 →