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:

  1. Client ID + Secret (Default) - Azure AD app registration with client secret
  2. 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:

  1. Check network connectivity
  2. Retry the extraction
  3. Review log file for errors during extraction
  4. Contact support if issue persists

See Also