secondbrain/CDM_Implementation_Guide.md

Microsoft Common Data Model - Customer Service Implementation Guide

Overview

This document provides a comprehensive guide to the SQL Server implementation of the Microsoft Common Data Model (CDM) for Customer Service applications. The implementation includes 26 core tables, 4 analytical views, and sample data for testing and development.

Architecture Overview

The CDM Customer Service model is organized into seven functional categories:

1. Foundation Entities (4 tables)

The core tables that represent the fundamental business entities:

• User: System users with access to the CRM
• Site: Business locations/branches
• Account: Customer organizations
• Contact: Individual persons associated with accounts

2. Service Configuration (5 tables)

Tables for managing services and resources:

• Service: Services offered to customers
• Resource: Users or equipment that can be scheduled
• FacilityEquipment: Physical resources that can be scheduled
• SchedulingGroup: Resource groups or teams
• ResourceSpecification: Selection rules for resource allocation

3. Contract Management (3 tables)

Tables for managing customer contracts:

• Contract: Customer service contracts with terms and conditions
• ContractLine: Line items within contracts specifying services
• ServiceContractContact: Contacts associated with contracts

4. Entitlement Management (3 tables)

Tables for managing customer entitlements:

• Entitlement: Support entitlements for customers
• EntitlementContact: Contacts entitled to use services
• EntitlementProduct: Services/products covered by entitlements

5. Case Management (3 tables)

Tables for tracking service requests and issues:

• Case: Primary table for service request cases
• CaseResolution: Resolution details for cases
• ChildIncidentCount: Tracking of child incidents

6. Service Activities (2 tables)

Tables for scheduling and tracking service work:

• ServiceActivity: Scheduled service activities and appointments
• ActivityParty: Persons or groups associated with activities

7. Complementary Features (6 tables)

Additional tables supporting the core functionality:

• KnowledgeArticle: Knowledge base articles
• KnowledgeArticleIncident: Association between articles and cases
• IncidentKnowledgeBaseRecord: Knowledge base records for cases
• SLAKPIInstance: Service level agreement KPI tracking
• Task: Generic task activities
• ResourceExpansion: Resource expansion configurations

---

Table Descriptions and Key Fields

Foundation Tables

User

Represents users with access to the CRM system.

Field	Type	Description
userId	UNIQUEIDENTIFIER	Primary key
userName	NVARCHAR(200)	Unique username
firstName	NVARCHAR(100)	First name
lastName	NVARCHAR(100)	Last name
email	NVARCHAR(200)	Email address
phone	NVARCHAR(20)	Phone number
mobilePhone	NVARCHAR(20)	Mobile phone
isActive	BIT	Active status
createdOn	DATETIME2(7)	Creation timestamp
createdBy	UNIQUEIDENTIFIER	Creator user ID
modifiedOn	DATETIME2(7)	Last modification timestamp
modifiedBy	UNIQUEIDENTIFIER	Last modifier user ID

Account

Represents customer organizations.

Field	Type	Description
accountId	UNIQUEIDENTIFIER	Primary key
accountName	NVARCHAR(200)	Account name
accountNumber	NVARCHAR(50)	Unique account number
accountType	NVARCHAR(50)	Type: Prospect, Customer, Partner
parentAccountId	UNIQUEIDENTIFIER	Parent account (hierarchical)
ownerUserId	UNIQUEIDENTIFIER	Account owner
siteId	UNIQUEIDENTIFIER	Primary site
industry	NVARCHAR(100)	Industry classification
numberOfEmployees	INT	Employee count
revenue	DECIMAL(18,2)	Annual revenue
isActive	BIT	Active status

Contact

Represents individual persons.

Field	Type	Description
contactId	UNIQUEIDENTIFIER	Primary key
firstName	NVARCHAR(100)	First name
lastName	NVARCHAR(100)	Last name
email	NVARCHAR(200)	Email address
accountId	UNIQUEIDENTIFIER	Associated account
jobTitle	NVARCHAR(100)	Job title
department	NVARCHAR(100)	Department

Site

Represents business locations.

Field	Type	Description
siteId	UNIQUEIDENTIFIER	Primary key
siteName	NVARCHAR(200)	Location name
city	NVARCHAR(100)	City
stateProvince	NVARCHAR(100)	State/Province
country	NVARCHAR(100)	Country
isHeadquarters	BIT	Headquarters flag

---

Case Management Tables

Case

The primary table for service request tracking.

Field	Type	Description
caseId	UNIQUEIDENTIFIER	Primary key
caseNumber	NVARCHAR(50)	Unique case number
title	NVARCHAR(200)	Case subject
description	NVARCHAR(MAX)	Detailed description
priority	NVARCHAR(50)	Low, Medium, High, Critical
severity	NVARCHAR(50)	Low, Medium, High
status	NVARCHAR(50)	Active, Resolved, Closed, Waiting
accountId	UNIQUEIDENTIFIER	Associated account
contactId	UNIQUEIDENTIFIER	Associated contact
ownerUserId	UNIQUEIDENTIFIER	Assigned to user
contractId	UNIQUEIDENTIFIER	Associated contract
entitlementId	UNIQUEIDENTIFIER	Associated entitlement
parentCaseId	UNIQUEIDENTIFIER	Parent case (hierarchical)
createdDate	DATETIME2(7)	Creation date
resolvedDate	DATETIME2(7)	Resolution date
closedDate	DATETIME2(7)	Close date
resolutionNotes	NVARCHAR(MAX)	Resolution details
billingStatus	NVARCHAR(50)	Billable, Non-billable

CaseResolution

Details about how a case was resolved.

Field	Type	Description
resolutionId	UNIQUEIDENTIFIER	Primary key
caseId	UNIQUEIDENTIFIER	Associated case
description	NVARCHAR(MAX)	Resolution description
resolutionType	NVARCHAR(100)	Type of resolution
actualServiceDuration	INT	Time spent (minutes)
totalCostAmount	DECIMAL(18,2)	Cost of resolution

ServiceActivity

Scheduled service work for cases.

Field	Type	Description
activityId	UNIQUEIDENTIFIER	Primary key
subject	NVARCHAR(200)	Activity subject
serviceId	UNIQUEIDENTIFIER	Service type
caseId	UNIQUEIDENTIFIER	Associated case
ownerUserId	UNIQUEIDENTIFIER	Assigned technician
startTime	DATETIME2(7)	Start timestamp
endTime	DATETIME2(7)	End timestamp
duration	INT	Duration in minutes
location	NVARCHAR(200)	Service location
status	NVARCHAR(50)	Scheduled, In Progress, Completed, Cancelled

---

Contract Tables

Contract

Customer service contracts.

Field	Type	Description
contractId	UNIQUEIDENTIFIER	Primary key
contractNumber	NVARCHAR(100)	Unique contract number
contractName	NVARCHAR(200)	Contract name
accountId	UNIQUEIDENTIFIER	Customer account
startDate	DATE	Contract start date
endDate	DATE	Contract end date
status	NVARCHAR(50)	Draft, Active, Expired, Cancelled
contractValue	DECIMAL(18,2)	Total contract value
contractType	NVARCHAR(50)	Service, Support, Maintenance

ContractLine

Line items within contracts.

Field	Type	Description
contractLineId	UNIQUEIDENTIFIER	Primary key
contractId	UNIQUEIDENTIFIER	Parent contract
lineNumber	INT	Line sequence
serviceId	UNIQUEIDENTIFIER	Service included
quantityIncluded	INT	Units/hours included
quantityUsed	INT	Units/hours used
price	DECIMAL(18,2)	Line price
discount	DECIMAL(18,2)	Discount amount

---

Entitlement Tables

Entitlement

Customer support entitlements.

Field	Type	Description
entitlementId	UNIQUEIDENTIFIER	Primary key
entitlementName	NVARCHAR(200)	Entitlement name
accountId	UNIQUEIDENTIFIER	Customer account
startDate	DATE	Start date
endDate	DATE	Expiration date
type	NVARCHAR(50)	Support, Service, Training
unitType	NVARCHAR(50)	Cases, Hours, Incidents
unitCount	INT	Number of units
status	NVARCHAR(50)	Active, Inactive, Expired

---

Relationships and Hierarchy

Key Relationships

1. Account Hierarchy

   • Accounts can have parent accounts (self-referencing)
   • Useful for parent-subsidiary relationships

2. Case Hierarchy

   • Cases can have parent cases (self-referencing)
   • Useful for tracking related or child incidents

3. Resource Allocation

   • Resources can be users, equipment, or facilities
   • Resources are organized into SchedulingGroups
   • ResourceSpecifications define which resources are needed

4. Contract Structure

   • Contracts contain ContractLines
   • Each ContractLine specifies a Service and quantity
   • ServiceContractContact links contacts to contracts

5. Entitlement Management

   • Entitlements provide support/service access
   • EntitlementContact associates specific contacts
   • EntitlementProduct specifies which services are covered

6. Case Tracking

   • Cases are linked to Accounts and Contacts
   • ServiceActivities are scheduled for Cases
   • CaseResolution provides final resolution details
   • SLAKPIInstance tracks SLA compliance

---

Indexes for Performance

The implementation includes 36 indexes optimized for common queries:

Search Indexes

• Email-based lookups (User, Contact)
• Account number searches
• Case number searches

Filtering Indexes

• Status fields (Case, Contract, Service, etc.)
• Priority and severity filtering
• Type classification (AccountType, ResourceType)

Relationship Indexes

• Foreign key relationships
• Parent-child relationships
• User ownership

Time-Range Indexes

• Date range queries for contracts and activities
• Appointment/schedule lookups

---

Views for Analytics

Four pre-built views support common analytical queries:

1. v_ActiveCasesSummary

Shows all active and waiting cases with:

• Case number and title
• Priority and owner
• Associated account and contact
• Days since creation

SELECT * FROM cdm.v_ActiveCasesSummary
WHERE priority = 'High'
ORDER BY daysSinceCreation DESC;

2. v_ContractSummary

Shows contract overview with:

• Contract details and status
• Associated account
• Number of line items
• Contract value and owner

SELECT * FROM cdm.v_ContractSummary
WHERE status = 'Active'
ORDER BY contractValue DESC;

3. v_CaseServiceActivity

Shows service activities linked to cases:

• Case and activity details
• Assigned technician
• Time information

SELECT * FROM cdm.v_CaseServiceActivity
WHERE status = 'Scheduled'
ORDER BY startTime;

4. v_CustomerSupportStatus

Summarizes support metrics by customer:

• Total, active, resolved, closed cases
• Active entitlements per customer

SELECT * FROM cdm.v_CustomerSupportStatus
ORDER BY activeCases DESC;

---

Sample Data Included

The implementation includes realistic sample data:

Organizations

• 4 sample accounts (Acme Corporation, TechVision Inc, Global Services Ltd, StartupX)
• 3 sample sites (New York, Los Angeles, Chicago)

People

• 5 system users (support staff and managers)
• 5 customer contacts with different roles

Services & Resources

• 5 service types (Technical Support, Installation, Maintenance, Training, Premium Support)
• 5 resources (3 user resources, 2 facility/equipment resources)

Contracts & Entitlements

• 3 active contracts with 4 contract lines
• 3 entitlements with varying support levels

Cases & Activities

• 4 sample cases with various priorities and statuses
• 3 service activities scheduled for cases

Knowledge & Tracking

• 4 knowledge articles (some published, some draft)
• 4 SLA KPI instances tracking case performance

---

Key Design Decisions

1. GUID as Primary Keys: Ensures global uniqueness and distributed system compatibility

2. Audit Columns: All tables include createdBy, createdOn, modifiedBy, modifiedOn for tracking

3. Soft Deletes: isActive bit field instead of hard deletes for data preservation

4. Extensibility: Schema designed to accommodate CDM extensions

5. Performance: Indexes on common query patterns (status, owner, dates, hierarchies)

6. Type Safety: Enumerated values (as strings) for status, priority, and type fields

7. Relationships: Cascading deletes on child records for data integrity

---

Common Queries

Find Active Cases by Priority

SELECT 
    c.caseNumber,
    c.title,
    a.accountName,
    u.firstName + ' ' + u.lastName AS owner,
    DATEDIFF(DAY, c.createdDate, GETUTCDATE()) AS dayOpen
FROM cdm.Case c
JOIN cdm.Account a ON c.accountId = a.accountId
JOIN cdm.[User] u ON c.ownerUserId = u.userId
WHERE c.status = 'Active'
  AND c.priority = 'High'
ORDER BY c.createdDate;

Contract Utilization Report

SELECT 
    c.contractNumber,
    c.contractName,
    cl.lineNumber,
    s.serviceName,
    cl.quantityIncluded,
    cl.quantityUsed,
    CAST(CAST(cl.quantityUsed AS FLOAT) / CAST(cl.quantityIncluded AS FLOAT) * 100 AS DECIMAL(5,2)) AS utilizationPercent
FROM cdm.Contract c
JOIN cdm.ContractLine cl ON c.contractId = cl.contractId
JOIN cdm.Service s ON cl.serviceId = s.serviceId
WHERE c.status = 'Active'
ORDER BY c.contractNumber, cl.lineNumber;

Customer Support Effectiveness

SELECT 
    a.accountName,
    COUNT(DISTINCT c.caseId) AS totalCases,
    AVG(DATEDIFF(HOUR, c.createdDate, ISNULL(c.resolvedDate, GETUTCDATE()))) AS avgResolutionHours,
    SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) AS closedCases,
    SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS closureRate
FROM cdm.Account a
LEFT JOIN cdm.Case c ON a.accountId = c.accountId
WHERE c.createdDate >= DATEADD(MONTH, -3, GETUTCDATE())
GROUP BY a.accountId, a.accountName
ORDER BY closureRate DESC;

SLA Compliance Dashboard

SELECT 
    c.caseNumber,
    c.title,
    sk.kpiName,
    sk.metricValue,
    sk.metricUnit,
    sk.targetValue,
    sk.status,
    CASE 
        WHEN sk.isCritical = 1 THEN 'CRITICAL'
        WHEN sk.isWarning = 1 THEN 'WARNING'
        ELSE 'OK'
    END AS severity
FROM cdm.Case c
JOIN cdm.SLAKPIInstance sk ON c.caseId = sk.caseId
WHERE c.status IN ('Active', 'Waiting')
ORDER BY c.priority DESC, sk.status;

---

Performance Recommendations

Query Optimization

1. Always filter by status when querying cases
2. Use date range filters for time-based queries
3. Include isActive filters to exclude deleted records

Maintenance Tasks

1. Index Maintenance: Rebuild fragmented indexes monthly
2. Statistics: Update statistics weekly on high-traffic tables
3. Archive Old Cases: Archive cases older than 2 years
4. Partition Large Tables: Consider partitioning Case table by year

Monitoring

1. Monitor slow queries on Case, ServiceActivity tables
2. Check index fragmentation regularly
3. Monitor deadlocks in concurrent environments

---

Extending the Model

Adding Custom Fields

1. Create extension tables with CDM entity foreign keys
2. Use naming convention: CustomField_<YourCompany>_<EntityName>
3. Maintain referential integrity

Adding Custom Entities

1. Follow the same design patterns as existing entities
2. Include audit columns (createdBy, createdOn, etc.)
3. Add isActive soft delete column
4. Create appropriate indexes

Adding Business Logic

1. Create stored procedures for complex operations
2. Use triggers for audit trails
3. Implement views for reporting

---

Security Considerations

1. Row-Level Security: Implement using views or stored procedures
2. User Permissions: Restrict based on ownerUserId and siteId
3. Audit Trail: All modifications tracked in audit columns
4. Data Encryption: Consider encryption for sensitive fields
5. Access Control: Use database roles for different user types

---

Migration from Other Systems

When migrating from other CRM systems:

1. Data Mapping: Create mapping between legacy and CDM fields
2. Entity Matching: Match legacy entities to CDM entities
3. History Preservation: Use createdBy/createdOn for original data dates
4. Validation: Implement data quality checks during migration
5. Testing: Validate all relationships and referential integrity

---

Support and Resources

• Microsoft CDM Documentation: https://github.com/microsoft/CDM
• SQL Server Best Practices: Microsoft SQL Server documentation
• Performance Tuning: SQL Server Query Store analysis

---

Version History

• v1.0 (2026-09-23): Initial implementation with 26 core tables, 4 views, and sample data

---

License

This implementation is provided as a reference based on the Microsoft Common Data Model specification.