WhanauTahi.Xpm.Tooling.CLI datamanipulation

Powerful data manipulation operations for Dataverse environments

The datamanipulation command provides a comprehensive suite of data management and maintenance tools for Microsoft Dataverse environments. These operations are designed to help you extract, transform, repair, and maintain data across your Navigator Platform installations.

Note

All operations support preview mode (--PreviewMode) to test changes before applying them
Most operations use parallel processing for improved performance on large datasets
All operations create detailed log files in the Logs folder for auditing and troubleshooting
Currently supported only on Windows 10 and Windows 11
Requires Microsoft .NET 8 installed

Available Operations

The datamanipulation command supports several categories of operations:

Data Extraction

TDS Table Extraction - Extract large volumes of data from Dataverse to CSV files

Uses the TDS (Tabular Data Stream) endpoint for fast, efficient extraction
Automatic chunking for very large tables
Built-in row count validation
MFA authentication support
Perfect for data migration, reporting, backup, and compliance needs

Activity & Relationship Management

Rebuild Activity Relationships - Rebuild many-to-many relationships for activities

Repairs broken relationships between activities and individuals/referrals/outcomes
Updates multi-select fields (mag_familygroupmembers, mag_inboundreferrals, mag_outcomes)
Processes all activity types in the Navigator Platform
Parallel processing for high-performance operation
Supports both standard correction and advanced correction with referral mapping

Update Activity Relationships - Update relationship fields across all activities

Automatically populates mag_familygroupmembers from mag_familygroupmember lookups
Populates mag_inboundreferrals from mag_inboundreferral lookups
Populates mag_outcomes from mag_outcome lookups
Builds mag_relationships JSON for advanced relationship tracking
Handles all activity types registered in mag_activityregister
Intelligently detects which fields exist on each activity type

Update Email Activity Data - Parse email recipients and auto-link to contacts/referrals

Extracts contact email addresses from "To Recipients" field
Finds matching contacts in the system
Automatically links emails to contacts and referrals
Populates family group, family group members, and inbound referrals
Creates many-to-many relationship records
Excludes QEC-related emails automatically

Referral Operations

Recreate Additional Referrals - Create referrals for multiple individuals

Processes referrals marked with "Multiple Individuals"
Creates separate referral records for each additional contact
Maintains relationship to master referral
Preserves all relevant referral data
Automatically sets correct status codes and state codes
Batch processing for efficiency

Deactivate Referrals from Status - Update referral status based on legacy status values

Processes mag_statusvalue field from migrated data
Maps legacy status values to new status codes:
- "Admitted" → Active (809730002)
- "Pre-admitted", "Approval Required", "Referral Approved" → Entered (809730008)
- "Referral Cancelled", "Referral Declined", "Discharged" → Closed/Provider Declined/Client Non-Contact
Intelligently selects exit reason based on mag_reasonforexit
Bulk processing with parallel execution

Populate Exit for Referrals - Link exit activities to referrals

Finds exit activities without linked referrals
Updates parent referral with exit information (exit date, reason, exited by)
Creates child exit activities for additional referrals
Handles complex multi-individual referral scenarios
Preserves relationships JSON for proper tracking

Data Maintenance

Populate Family Group Member List - Build family member name lists

Updates mag_familygroupmemberslist field with comma-separated names
Queries mag_familygroupmembership table for relationships
Builds human-readable family member lists
Useful for views, reports, and quick reference

Recreate Family Groups - Auto-create family groups for individuals

Finds individuals without a family group (mag_whanauid is null)
Creates new family group record with individual as primary contact
Copies address information (physical and postal)
Sets up family group membership
Populates geocoding data (meshblock, longitude, latitude)

Change Owner to MRO Team - Bulk ownership transfer

Changes record ownership for individuals and family groups
Transfers to specified MRO (Multi-Regional Organization) team
Processes both contact and mag_familygroup entities
Parallel execution for large-scale ownership changes
Preview mode to verify changes before applying

Common Parameters

Most datamanipulation operations share these common parameters:

Required Parameters (Environment Connection)

`--EnvironmentUrl` (-e)

The URL of your Dataverse environment.

Format: https://environmentname.crm[X].dynamics.com

Example:

--EnvironmentUrl https://myorg.crm6.dynamics.com

`--ClientId` (-c)

The Azure AD application (client) ID used for authentication.

Example:

--ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef

Optional Parameters (Environment Connection)

`--Secret` (-s)

The client secret for the Azure AD application. If not provided, uses the default secret configured in the tool.

Example:

--Secret YourClientSecretHere

Caution

Never commit client secrets to source control. Use environment variables or secure credential storage for production use.

Operation Control Parameters

`--PreviewMode`

Run the operation in preview mode without making any changes. Shows what would be done.

Example:

--PreviewMode

Tip

Always run with --PreviewMode first to verify the operation will do what you expect before applying changes to production data.

Operation-Specific Flags

Different operations use specific flags to enable them. Use exactly one of these per command:

Flag	Operation	Documentation
`--ExtractTable`	TDS Table Extraction	View Docs
`--RebuildMMRelationship`	Rebuild Activity Relationships (Standard)	View Docs
`--RebuildMMRelationship_withCorrection`	Rebuild Activity Relationships (Advanced)	View Docs
`--UpdateActivityRelationships`	Update Activity Relationship Fields	View Docs
`--UpdateEmailActivityData`	Parse Email Recipients & Link Contacts	View Docs
`--RecreateAdditionalReferrals`	Create Referrals for Multiple Individuals	View Docs
`--DeactiveReferralFromStatusValue`	Deactivate Referrals from Legacy Status	View Docs
`--PopulateExitForReferral`	Link Exit Activities to Referrals	View Docs
`--FamilyGroupMemberList`	Populate Family Member Name Lists	View Docs
`--RecreateFamilyGroup`	Auto-Create Family Groups	View Docs
`--ChangeOwnerToMROTeam`	Bulk Transfer Ownership	View Docs

Quick Start Examples

Example 1: Extract a table to CSV

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --ExtractTable mag_referral `
  --TdsEnvironmentHost myorg.crm6.dynamics.com `
  --TdsDatabaseName org12345678 `
  --TdsOrderByColumn mag_referralid `
  --TdsOutputPath .\exports\referrals.csv `
  --TdsUseInteractive

Example 2: Rebuild activity relationships (with preview)

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --RebuildMMRelationship `
  --EnvironmentUrl https://myorg.crm6.dynamics.com `
  --ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef `
  --Secret YourClientSecretHere `
  --PreviewMode

Example 3: Update email activity data

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --UpdateEmailActivityData `
  --EnvironmentUrl https://myorg.crm6.dynamics.com `
  --ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef

Example 4: Recreate additional referrals

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --RecreateAdditionalReferrals `
  --EnvironmentUrl https://myorg.crm6.dynamics.com `
  --ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef

Performance & Scalability

The datamanipulation command is designed for enterprise-scale operations:

Parallel Processing: Most operations use up to 50 concurrent threads for maximum throughput
Batch Operations: Records are processed in batches (typically 10-5000 records per batch)
Progress Tracking: Real-time progress updates show percentage complete and records processed
Automatic Retry: Built-in retry logic handles transient errors and throttling
Memory Efficient: Uses streaming and paging to handle millions of records without excessive memory

Typical Performance:

Operation	Records	Time	Throughput
TDS Extraction	500,000 rows	2-4 minutes	2,000-5,000 rows/sec
Rebuild Relationships	100,000 activities	10-15 minutes	100-200 activities/sec
Update Activities	50,000 activities	5-10 minutes	80-170 activities/sec
Recreate Referrals	10,000 referrals	3-5 minutes	30-60 referrals/sec

Note

Performance varies based on network speed, server load, Dataverse environment configuration, and data complexity.

Logging & Troubleshooting

All datamanipulation operations create detailed log files in the Logs folder:

Log File Naming:

DataManipulation_YYYY-MM-DD_HH-MM-SS-mmm.log - TDS extraction logs
ParallelProcessing_OperationName_YYYY-MM-DD_HH-MM-SS-mmm.log - Parallel operation logs

Log Contents:

Timestamp for every operation
Environment connection details
Record counts (total, processed, succeeded, failed)
Progress updates every few seconds
Error messages with full details
Performance metrics (throughput, elapsed time)

Example Log Entry:

[2024-11-17 14:35:22.123] Starting parallel relationship rebuilding...
[2024-11-17 14:35:22.456] Found 125,340 records to process
[2024-11-17 14:35:23.789] Creating pool of 50 service clients...
[2024-11-17 14:35:45.012] Producer: Queued 50,000 of 125,340 records (39.9%)
[2024-11-17 14:36:12.345] Progress: 75,000/125,340 (59.8%)
[2024-11-17 14:36:38.678] Progress: 125,000/125,340 (99.7%)
[2024-11-17 14:36:40.123] Relationship rebuilding completed: 125,340/125,340 records processed in 77.7 seconds

Best Practices

1. Always Use Preview Mode First

Test every operation with --PreviewMode before running on production data:

# Test the operation first
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation --RebuildMMRelationship --PreviewMode -e <url> -c <clientId>

# If preview looks good, run for real
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation --RebuildMMRelationship -e <url> -c <clientId>

2. Schedule During Off-Peak Hours

Large-scale operations can impact system performance. Schedule them during maintenance windows or off-peak hours.

3. Review Logs After Completion

Always check the log files after operations complete:

Verify record counts match expectations
Check for error messages
Review performance metrics

4. Backup Before Major Changes

For operations that modify data (rebuild relationships, deactivate referrals, etc.), consider backing up affected records first using TDS extraction:

# Extract affected records before modifying
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --ExtractTable mag_outcomestepsummary `
  --TdsEnvironmentHost myorg.crm6.dynamics.com `
  --TdsDatabaseName org12345678 `
  --TdsOutputPath .\backups\activities_before_rebuild.csv

5. Use Service Accounts for Automation

For scheduled/automated operations:

Create dedicated service accounts
Use service principal authentication
Store credentials securely (Azure Key Vault, etc.)
Audit service account usage

6. Monitor Environment Health

Watch for:

Dataverse API request limits
Storage capacity
Plugin execution timeout warnings
Workflow backlogs

Security Considerations

Authentication

The datamanipulation command supports multiple authentication methods:

Client ID + Secret (Default) - Azure AD app registration with client secret
Interactive Authentication (TDS only) - Browser-based MFA login for TDS extraction

Permissions Required

Operations require specific Dataverse security roles:

Operation	Required Permissions
TDS Extraction	Read on target table, TDS endpoint access enabled
Rebuild Relationships	Read/Write on activities, many-to-many relationship entities
Update Activities	Read/Write on all activity types
Email Operations	Read/Write on email, contact, mag_referral
Referral Operations	Read/Write on mag_referral, create permission
Family Group Operations	Read/Write on contact, mag_familygroup, mag_familygroupmembership
Change Owner	Share privilege on affected entities

Data Protection

Log files may contain sensitive data - store securely
Extracted CSV files contain production data - handle per compliance policies
Use secure channels (SharePoint, OneDrive with encryption) for file transfers
Delete temporary files when no longer needed

Troubleshooting Common Issues

Issue: "Anonymous authentication error"

Cause: Authentication failed or insufficient permissions

Solution:

# For TDS extraction, add --TdsUseInteractive
--TdsUseInteractive

# For other operations, verify ClientId and Secret are correct
--ClientId <verify this value>
--Secret <verify this value>

Issue: "Execution Timeout Expired"

Cause: Query or operation exceeds 2-minute timeout

Solution: For TDS extraction, automatic chunking will activate. For other operations, check the logs for specific table/record causing timeout.

Issue: "Throttling detected"

Cause: Too many API requests to Dataverse

Solution: The tool automatically retries with exponential backoff. If throttling persists:

Reduce MaxConcurrency (requires code change)
Schedule during off-peak hours
Contact support to review API limits

Issue: "Row count validation failed"

Cause: Not all rows were extracted (TDS extraction only)

Solution:

Check network connectivity
Retry the extraction
Review log file for errors during extraction
Contact support if issue persists

Table of Contents

WhanauTahi.Xpm.Tooling.CLI datamanipulation

Note

Available Operations

Data Extraction

Activity & Relationship Management

Referral Operations

Data Maintenance

Common Parameters

Required Parameters (Environment Connection)

--EnvironmentUrl (-e)

--ClientId (-c)

Optional Parameters (Environment Connection)

--Secret (-s)

Caution

Operation Control Parameters

--PreviewMode

Tip

Operation-Specific Flags

Quick Start Examples

Example 1: Extract a table to CSV

Example 2: Rebuild activity relationships (with preview)

Example 3: Update email activity data

Example 4: Recreate additional referrals

Performance & Scalability

Note

Logging & Troubleshooting

Best Practices

1. Always Use Preview Mode First

2. Schedule During Off-Peak Hours

3. Review Logs After Completion

4. Backup Before Major Changes

5. Use Service Accounts for Automation

6. Monitor Environment Health

Security Considerations

Authentication

Permissions Required

Data Protection

Troubleshooting Common Issues

Issue: "Anonymous authentication error"

Issue: "Execution Timeout Expired"

Issue: "Throttling detected"

Issue: "Row count validation failed"

See Also

`--EnvironmentUrl` (-e)

`--ClientId` (-c)

`--Secret` (-s)

`--PreviewMode`