OpenText (Content Server) — Sources

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

Example

Documents created

How many documents were created in the period?

COUNT(*) FROM document WHERE CreateDate IN period

Grain: Created document
Fields: Document.DataID, Document.CreateDate, Document.CategoryID
Dimensions: mime_type, category, department

CreateDate from DTree; exclude system/temp folder content.

Adapt: Separate by category for retention-relevant vs transient documents.

Example

Checked-out documents

How many documents are currently checked out?

COUNT(*) FROM document WHERE ReserveUserID IS NOT NULL

Grain: Checked-out document (snapshot)
Fields: Document.DataID, Document.OwnerID
Dimensions: mime_type, department

Use the ReserveUserID field from DTree for checkout status.

Adapt: Separately flag long checkouts (> 7 days) as a risk.

Versions count

How many new versions were created in the period?

COUNT(*) FROM document_version WHERE CreateDate IN period

Grain: Document version
Fields: DocumentVersion.DataID, DocumentVersion.VersionNum, DocumentVersion.CreateDate
Dimensions: mime_type

Use daily aggregates per category at high volume.

Adapt: Exclude auto-generated rendition versions from counts.

Storage volume by category

How much storage volume sits in each category?

SUM(FileSize) FROM document_version GROUP BY CategoryID

Grain: Document version (latest per document)
Fields: DocumentVersion.FileSize, Document.CategoryID
Dimensions: category

Count only the latest version per document, not the sum of all historical versions.

Adapt: Report large media/video categories separately.

Permission grants per document

How many active permission grants does the average document have?

AVG(grant_count) FROM permission GROUP BY NodeID

Grain: Document (permission snapshot)
Fields: Permission.NodeID, Permission.UserID, Permission.PermType
Dimensions: permission_type, department

Distinguish group vs user grants — group expansion distorts counts.

Adapt: Evaluate public/everyone grants separately as a risk flag.

Formula overview

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

Label	Formula	Grain	Fields
Documents created	`COUNT(*) FROM document WHERE CreateDate IN period`	Created document	Document.DataID, Document.CreateDate, Document.CategoryID
Checked-out documents	`COUNT(*) FROM document WHERE ReserveUserID IS NOT NULL`	Checked-out document (snapshot)	Document.DataID, Document.OwnerID
Versions count	`COUNT(*) FROM document_version WHERE CreateDate IN period`	Document version	DocumentVersion.DataID, DocumentVersion.VersionNum, DocumentVersion.CreateDate
Storage volume by category	`SUM(FileSize) FROM document_version GROUP BY CategoryID`	Document version (latest per document)	DocumentVersion.FileSize, Document.CategoryID
Permission grants per document	`AVG(grant_count) FROM permission GROUP BY NodeID`	Document (permission snapshot)	Permission.NodeID, Permission.UserID, Permission.PermType

Id	Label	Grain	Notes
`mime_type`	MIME type	document.MimeType	Normalize MIME type to a small taxonomy (PDF/office/image/other).
`category`	Category	category.Name	RM classification drives retention KPIs — do not confuse with folder.
`department`	Department	user.DeptID	Resolve department via HR reference table, do not denormalize onto document.
`workflow_status`	Workflow status	workflow.Status	Distinguish in-flight vs overdue vs completed.
`permission_type`	Permission type	permission.PermType	Track read/write/delete grants separately for access-review KPIs.

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 (KUAF)	`Name, Login, EMail, DeptID`	Direct	RAW: restrict access; Curated: id only; Mart: aggregates only	KUAF master data — name/login/email only with legal basis; keep UserID as key.
Document owner reference	`OwnerID display name`	Quasi	RAW: id only; Curated: id only; Mart: aggregates only	Owner display resolves to user PII — join only by UserID, do not denormalize the name.
OCR full-text extraction	`extracted document text, content search index body`	Sensitive	RAW: never store; Curated: never; Mart: never	Full text can contain arbitrary document content including third-party PII — never land it in the warehouse.
Rendition / preview thumbnails	`preview image body, viewer cache`	Sensitive	RAW: never store; Curated: never; Mart: never	Preview binaries are derived content — no warehouse load regardless of classification.
Permission grants (Everyone / Public)	`PermType = Public, group expansion`	Quasi	RAW: workforce policy; Curated: aggregates only; Mart: aggregates only	Public grants can inadvertently expose PII documents — evaluate as an access-risk flag.

DSDR discovery

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

Focus	Notes
Match keys	DataID, UserID, VersionNum, CategoryID, RightID.
Primary objects	Document, folder, category, permission grants, user — no content bodies, no full text.
Batch / archive export copies	RM archive exports and backup copies duplicate document metadata and owner PII.
Test / staging environment copies	Do not mix staging Content Server instances with prod DMS 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 6

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

High OpenText: document without owner

Problem: Documents without owner distort ACL and ownership KPIs.
Prevent: Create policy: owner required; sync orphan cleanup.
Staff must know: Admins: Set owner on create.
Checks: document.owner_id IS NULL
Fix in source: Assign owner or archive.
Warehouse safety net: Quarantine; Unknown-owner dim.

High Version without parent document

Problem: Versions without document join break storage and version counts.
Prevent: Always load parent id in extract.
Staff must know: Data: Version natural key includes parent.
Checks: version.document_id IS NULL OR document_id NOT IN dim_document
Fix in source: Re-extract; tombstones for deleted docs.
Warehouse safety net: Quarantine orphan versions.

High ACL without principal

Problem: Permission grants without user/group are dead security ballast.
Prevent: ACL write: principal required.
Staff must know: Security: No empty ACL rows.
Checks: permission.principal_id IS NULL
Fix in source: Clean ACLs.
Warehouse safety net: Flag orphan_acl; exclude from coverage KPIs.

Medium Document without folder/cabinet path

Problem: Documents without filing path distort taxonomy rollups.
Prevent: Default folder/cabinet on create.
Staff must know: Records: Filing path is a required dim.
Checks: document.folder_id IS NULL
Fix in source: Move into default filing location.
Warehouse safety net: Unknown-folder dim.

Medium Document without MIME/content type

Problem: Missing content type breaks storage-by-type KPIs.
Prevent: Upload: detect MIME; allowlist.
Staff must know: Platform: Persist MIME on ingest.
Checks: document.mime_type IS NULL AND status = 'active'
Fix in source: Backfill MIME.
Warehouse safety net: Unknown mime bucket.

Medium Duplicate document id in extract

Problem: Duplicate keys distort document counts.
Prevent: Idempotent load; check pagination.
Staff must know: Data: Natural key = document_id.
Checks: COUNT(*) GROUP BY document_id HAVING COUNT > 1
Fix in source: Re-extract.
Warehouse safety net: Dedup by latest _loaded_at.

MDM / duplicates 2

Document Document duplicates / rename

Match keys: document_id, external_id, name+folder_path, content_hash_meta
Prevent in source: Stable object id; track renames.
Resolve in warehouse: Golden document_id; name SCD2.
Survivorship: Active document with latest version wins.

User User duplicates

Match keys: user_id, login, email (hashed), external_idp_id
Prevent in source: IdP as source of truth.
Resolve in warehouse: Golden user id; login SCD2.
Survivorship: Active user with latest activity wins.

Product settings to improve 3

Extract / Integration Metadata/ACL/version meta only — no file bodies

Why: Analytics without content leak and volume explosion.
How: Allowlist fields; never land binary endpoints in warehouse.

Security / Permissions ACL export as principal ids

Why: Access KPIs without cleartext mails in marts.
How: Principal id + role; email RAW restricted only.

Records / Retention Track retention labels and legal hold metadata

Why: Compliance KPIs without content bodies.
How: Hold/retention flags + dates; no binary.

Metadata: what exists & how to get it 4

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

Object / document model

Where in product: Content Server / OTDS metadata APIs
How to access: Inventory entities/properties; select allowlist.
Use for: Extract design, PII tags, join keys.
Watchouts: Never select file/OCR bodies.

ACL / permission model

Where in product: OpenText permissions / ACLs
How to access: Document principal, role, object scope.
Use for: Access facts, orphan-ACL DQ.
Watchouts: Inherited ACLs — SCD2 recommended.

Version metadata

Where in product: OpenText version history (meta only)
How to access: Version number, size, author id, timestamps.
Use for: Version counts, storage volume KPIs.
Watchouts: No binary streams.

Folder / category taxonomy

Where in product: OpenText folders / categories / cabinets
How to access: Export hierarchy and codes.
Use for: Taxonomy dims, path rollups.
Watchouts: Renames — SCD2.

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

Table	Description	Grain	Load
`document` Document	Content Server document node (DataID) — name/MIME type/category; file body not loaded.	One document (DataID)	Load
`document_version` Document version	DVersData version record — version number/file size/create date per document; no version binaries.	One version (DataID + VersionNum)	Optional
`folder` Folder	DTree container node — hierarchical filing structure (name/ParentID).	One folder (DataID)	Load
`category` Category	LLAttrData category/attribute definition — RM classification and retention schedule.	One category (CategoryID)	Load
`workflow` Workflow	WorkFlow/WorkTask instance — status and step assignment on a document.	One workflow instance (WorkID)	Optional
`permission` Permission	DTreeAcl/Perms entry — read/write/delete per node and user or group.	One permission grant (NodeID + RightID)	Optional
`user` User	KUAF user record — name/login/email/department; core master data with PII.	One user (UserID)	Load
`workspace` Business workspace	Extended ECM business workspace — links a business object (e.g. SAP order) to the filing structure.	One workspace (DataID)	Optional

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

Entity	Field	Role	Why
Document	`Document.DataID`	Key	Document join
Document	`Document.Name`	Dimension	Document title
Document	`Document.MimeType`	Dimension	File type dim
Document	`Document.ParentID`	Dimension	Folder back-join
Document	`Document.CategoryID`	Dimension	Category back-join
Document	`Document.CreateDate`	Measure	Creation date
Document	`Document.ModifyDate`	Measure	Last modified
Document	`Document.OwnerID`	Dimension	Owner back-join (user)
DocumentVersion	`DocumentVersion.DataID`	Dimension	Document back-join
DocumentVersion	`DocumentVersion.VersionNum`	Key	Version join
DocumentVersion	`DocumentVersion.FileSize`	Measure	Storage volume
DocumentVersion	`DocumentVersion.MimeType`	Dimension	File type per version
DocumentVersion	`DocumentVersion.CreateDate`	Measure	Version date
DocumentVersion	`DocumentVersion.Creator`	Dimension	Creator back-join
Folder	`Folder.DataID`	Key	Folder join
Folder	`Folder.Name`	Dimension	Folder name
Folder	`Folder.ParentID`	Dimension	Hierarchy
Folder	`Folder.SubType`	Dimension	Folder/workspace type
Category	`Category.CategoryID`	Key	Category join
Category	`Category.Name`	Dimension	Classification label
Category	`Category.RetentionScheduleID`	Dimension	Retention assignment
Workflow	`Workflow.WorkID`	Key	Workflow join
Workflow	`Workflow.DataID`	Dimension	Document back-join
Workflow	`Workflow.Status`	Dimension	Workflow status
Workflow	`Workflow.CurrentStep`	Dimension	Current step
Workflow	`Workflow.InitiatedDate`	Measure	Workflow start
Workflow	`Workflow.CompletedDate`	Measure	Workflow completion
Permission	`Permission.RightID`	Key	Permission join
Permission	`Permission.NodeID`	Dimension	Node back-join
Permission	`Permission.UserID`	Dimension	User/group back-join
Permission	`Permission.PermType`	Dimension	Read/write/delete dim
User	`User.UserID`	Key	User join
User	`User.Name`	PII	Name / PII
User	`User.Login`	PII	Login / PII
User	`User.EMail`	PII	Email / PII
User	`User.DeptID`	Dimension	Department dim
Workspace	`Workspace.DataID`	Key	Workspace join
Workspace	`Workspace.WorkspaceType`	Dimension	Workspace template dim
Workspace	`Workspace.BusinessObjectID`	Dimension	Business object back-join (e.g. SAP)

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

Skip tables / objects

Table	Category	Why
`Document body / binary content (all versions)`	System	Full file bodies — never into the warehouse; version metadata suffices.
`OCR full-text / content search index`	System	Full-text PII — metadata is enough for storage/retention KPIs.
`Rendition / preview thumbnails`	System	Derived binaries — never into the warehouse.
`Full audit trail (DAuditNew) bulk`	System	Technical audit — volume, little mart value; prefer meta/agg.

Skip fields / content

Document body / binary content (all versions) — Never into the warehouse — cost/security risk.
OCR full-text extraction (bulk) — Can contain third-party PII.
Rendition / preview thumbnails — Binaries without analytics value.
User Name/Login/EMail cleartext in default marts — UserID is enough for DMS KPIs.
Full audit trail / event log — Volume — meta/agg instead of full history.

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

Landing (RAW)

RAW OpenText document + user landing

Metadata only — no file bodies; email = PII in RAW.

-- Warehouse-neutral RAW from OpenText metadata extract
CREATE TABLE raw_ot_document (
  document_id             VARCHAR,
  name                 VARCHAR,
  mime_type            VARCHAR,
  size_bytes           BIGINT,
  owner_id             VARCHAR,
  folder_id         VARCHAR,
  created_at           TIMESTAMP,
  modified_at          TIMESTAMP,
  status               VARCHAR,
  _loaded_at           TIMESTAMP
  -- no file body / OCR text
);

CREATE TABLE raw_ot_user (
  user_id              VARCHAR,
  login                VARCHAR,
  email                VARCHAR, -- workforce PII
  display_name         VARCHAR,
  active               BOOLEAN,
  _loaded_at           TIMESTAMP
);

RAW version + ACL landing

Version meta + ACL principal ids — no binaries.

CREATE TABLE raw_ot_version (
  version_id           VARCHAR,
  document_id             VARCHAR,
  version_number       INT,
  size_bytes           BIGINT,
  created_by           VARCHAR,
  created_at           TIMESTAMP,
  _loaded_at           TIMESTAMP
  -- no binary stream
);

CREATE TABLE raw_ot_permission (
  permission_id        VARCHAR,
  document_id             VARCHAR,
  principal_id         VARCHAR,
  role                 VARCHAR,
  inherited            BOOLEAN,
  _loaded_at           TIMESTAMP
);

Curated

Curated fact version

Version KPI grain — parent required; no body.

CREATE TABLE curated_fct_ot_version AS
SELECT
  v.version_id,
  v.document_id,
  v.version_number,
  v.size_bytes,
  v.created_by,
  v.created_at
FROM raw_ot_version v
WHERE v.document_id IS NOT NULL;

Curated dim document / ACL fact

Doc dim without owner cleartext; ACL as ids.

CREATE TABLE curated_dim_ot_document AS
SELECT
  document_id,
  mime_type,
  size_bytes,
  owner_id,
  folder_id,
  created_at,
  modified_at,
  status
  -- omit email/display_name joins in default dim
FROM raw_ot_document;

CREATE TABLE curated_fct_ot_permission AS
SELECT
  permission_id,
  document_id,
  principal_id,
  role,
  inherited
FROM raw_ot_permission
WHERE principal_id IS NOT NULL;

Curated measure SELECTs

Documents created, versions, storage, ACL grants — adapt period filters.

-- Documents created in period
SELECT COUNT(*) AS documents_created
FROM curated_dim_ot_document
WHERE created_at BETWEEN :period_start AND :period_end;

-- Versions count
SELECT COUNT(*) AS versions_count
FROM curated_fct_ot_version
WHERE created_at BETWEEN :period_start AND :period_end;

-- Storage volume (bytes)
SELECT COALESCE(SUM(size_bytes), 0) AS storage_bytes
FROM curated_dim_ot_document
WHERE status = 'active';

-- ACL grants per document (avg)
SELECT AVG(grant_count) AS avg_acl_grants
FROM (
  SELECT document_id, COUNT(*) AS grant_count
  FROM curated_fct_ot_permission
  GROUP BY document_id
) g;

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 →