Rebuild Activity Relationships

Repair and rebuild many-to-many relationships between activities and individuals, referrals, and outcomes

Overview

The Rebuild Activity Relationships operation processes activities (mag_outcomestepsummary) and rebuilds their many-to-many relationships with individuals, referrals, and outcomes. This is essential after data migrations, when relationship data has been corrupted, or when activities have lost their associations due to system issues.

The operation comes in two variants:

Standard Rebuild (--RebuildMMRelationship) - Rebuilds relationships based on existing mag_relationships JSON data
Advanced Rebuild with Correction (--RebuildMMRelationship_withCorrection) - Also corrects mismatched referral-individual combinations

Both variants use parallel processing with 50 concurrent threads for maximum performance on large datasets.

When to Use This Operation

Use Standard Rebuild When:

✅ After migrating activity data from legacy systems
✅ Activities show "No data available" in relationship grids
✅ Multi-select fields (mag_familygroupmembers, mag_inboundreferrals, mag_outcomes) are empty
✅ Activities have mag_relationships JSON data but no N:N records
✅ You need to populate relationship fields after bulk imports

Use Advanced Rebuild with Correction When:

✅ All of the above scenarios, plus:
✅ Referrals are linked to wrong individuals
✅ Activities show incorrect individual-referral combinations
✅ Parent-child referral relationships need to be corrected
✅ You've identified data quality issues with referral assignments

Don't Use This Operation When:

❌ Creating new activities (use normal create operations)
❌ Activities don't have mag_relationships data (they'll be skipped)
❌ You only need to update a few activities (use manual edits or flows)

Quick Start

Example 1: Preview Standard Rebuild

Test the operation first to see what will be changed:

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

Expected Output:

[2024-11-17 14:30:15.123] Starting parallel relationship rebuilding in PREVIEW MODE...
[2024-11-17 14:30:15.456] Creating pool of 50 service clients...
[2024-11-17 14:30:18.789] Found 45,230 activity records to process
[2024-11-17 14:30:19.012] PREVIEW: Would process 45,230 activities
[2024-11-17 14:30:19.345] PREVIEW: Estimated changes:
  - Family Group Members: ~125,000 associations
  - Inbound Referrals: ~78,000 associations
  - Outcomes: ~12,500 associations
[2024-11-17 14:30:19.678] Preview completed. No changes made.

Example 2: Run Standard Rebuild

After verifying the preview, run the operation for real:

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

Expected Output:

[2024-11-17 14:35:22.123] Starting parallel relationship rebuilding...
[2024-11-17 14:35:22.456] Found 45,230 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 10,000 of 45,230 records (22.1%)
[2024-11-17 14:36:12.345] Progress: 20,000/45,230 (44.2%)
[2024-11-17 14:36:38.678] Progress: 30,000/45,230 (66.3%)
[2024-11-17 14:37:05.901] Progress: 40,000/45,230 (88.5%)
[2024-11-17 14:37:22.234] Progress: 45,000/45,230 (99.5%)
[2024-11-17 14:37:28.567] Relationship rebuilding completed: 45,230/45,230 records processed in 126.4 seconds

Example 3: Advanced Rebuild with Correction

Use this when you need to fix incorrect referral-individual combinations:

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

Expected Output:

[2024-11-17 14:40:15.123] Starting advanced relationship rebuilding with correction...
[2024-11-17 14:40:15.456] Building referral-to-individual mapping...
[2024-11-17 14:40:18.789] Found 8,540 unique referrals
[2024-11-17 14:40:22.012] Building parent-child referral mapping...
[2024-11-17 14:40:24.345] Found 1,240 child referrals
[2024-11-17 14:40:25.678] Creating pool of 50 service clients...
[2024-11-17 14:40:28.901] Found 45,230 activity records to process
[2024-11-17 14:40:29.234] PREVIEW: Would correct 3,456 incorrect referral-individual combinations
[2024-11-17 14:40:29.567] Preview completed. No changes made.

How It Works

Standard Rebuild Process

Retrieve Activities: Queries all mag_outcomestepsummary records with non-null mag_relationships
Parse Relationships: Extracts Individual IDs, Referral IDs, and Outcome IDs from JSON
Update Multi-Select Fields: Populates mag_familygroupmembers, mag_inboundreferrals, mag_outcomes with NTONDATA format
Create N:N Records: Creates association records in many-to-many relationship tables
Parallel Processing: Processes 10 activities per batch across 50 concurrent threads

Advanced Rebuild with Correction Process

All of the standard process steps, plus:

Build Referral Mapping: Creates dictionary of referral→individual relationships
Identify Parent-Child Referrals: Maps child referrals to parent referrals via mag_masterreferral
Validate Combinations: Checks if each referral's individual matches the activity's individual
Correct Mismatches:
- Removes incorrect referral associations
- Adds correct referral associations
- Updates mag_inboundreferrals field accordingly

mag_relationships JSON Format

The operation expects this JSON structure in the mag_relationships field:

{
  "Individuals": [
    {"Id": "12345678-1234-1234-1234-123456789012"}
  ],
  "Referrals": [
    {"Id": "23456789-2345-2345-2345-234567890123"},
    {"Id": "34567890-3456-3456-3456-345678901234"}
  ],
  "Outcomes": [
    {"Id": "45678901-4567-4567-4567-456789012345"}
  ]
}

NTONDATA Multi-Select Format

The operation populates multi-select fields with this format:

NTONDATA:{"actions":[],"len":"mag_inboundreferral","ida":"mag_inboundreferralid","na":"mag_name","ids":"guid1,guid2","names":"Referral 1,Referral 2"}

Parameters

Required Parameters

`--RebuildMMRelationship` or `--RebuildMMRelationship_withCorrection`

Choose exactly one of these flags to enable the operation.

Examples:

# Standard rebuild
--RebuildMMRelationship

# Advanced rebuild with correction
--RebuildMMRelationship_withCorrection

`--EnvironmentUrl` (-e)

The URL of your Dataverse environment.

Example:

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

`--ClientId` (-c)

The Azure AD application (client) ID for authentication.

Example:

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

Optional Parameters

`--Secret` (-s)

The client secret for authentication. If omitted, uses the default secret.

Example:

--Secret YourClientSecretHere

Caution

Never commit secrets to source control. Use environment variables or Azure Key Vault for production.

`--PreviewMode`

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

Example:

--PreviewMode

Tip

Always run with --PreviewMode first to verify the operation will work as expected.

Real-World Scenarios

Scenario 1: Post-Migration Relationship Repair

Situation: You've migrated 50,000 activities from a legacy system. The mag_relationships JSON data was imported, but the N:N relationship records weren't created.

Solution:

# Step 1: Preview to verify count
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --RebuildMMRelationship `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:DATAVERSE_SECRET `
  --PreviewMode

# Step 2: Check the log file
Get-Content ".\Logs\ParallelProcessing_RebuildRelationships_*" -Tail 50

# Step 3: If preview looks good, run for real
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --RebuildMMRelationship `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:DATAVERSE_SECRET

Expected Outcome:

All activities have populated multi-select fields
N:N relationship tables contain association records
Activities show related individuals/referrals/outcomes in grids

Scenario 2: Fixing Incorrect Referral Assignments

Situation: After investigating user complaints, you discover 3,000+ activities are linked to the wrong referrals. The individuals on the activities don't match the individuals on the referrals.

Solution:

# Use advanced rebuild with correction
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --RebuildMMRelationship_withCorrection `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:DATAVERSE_SECRET `
  --PreviewMode

# Review the correction count in the log
Get-Content ".\Logs\ParallelProcessing_RebuildRelationshipsWithCorrection_*" | Select-String "Would correct"

# If the count matches expectations, run without preview
.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --RebuildMMRelationship_withCorrection `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:DATAVERSE_SECRET

Expected Outcome:

Incorrect referral associations removed
Correct referral associations created
mag_inboundreferrals field updated with correct referrals
Activities now show correct individual-referral combinations

Scenario 3: Scheduled Weekly Maintenance

Situation: You want to run relationship rebuilding every weekend to catch any activities that lost relationships during the week.

Solution:

Create a scheduled task (Windows Task Scheduler or Azure Logic App):

# weekly-rebuild.ps1
$ErrorActionPreference = "Stop"

# Set environment variables securely
$env:CLIENT_SECRET = Get-Secret -Name "DataverseClientSecret"

# Run rebuild
& "C:\Tools\WhanauTahi.Xpm.Tooling.CLI.exe" datamanipulation `
  --RebuildMMRelationship `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:CLIENT_SECRET

# Archive the log file
$logFile = Get-ChildItem ".\Logs\ParallelProcessing_RebuildRelationships_*" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Copy-Item $logFile "\\fileserver\logs\dataverse\rebuild-$(Get-Date -Format 'yyyy-MM-dd').log"

# Send notification
Send-MailMessage -To "admin@example.com" -Subject "Rebuild Complete" -Body "See attached log" -Attachments $logFile

Performance & Scalability

Processing Characteristics

Configuration	Value
Max Concurrent Threads	50
Records per Batch	10
Page Size (Retrieve)	5,000
Preview Mode Limit	100 records

Typical Performance

Records	Standard Rebuild	Advanced Rebuild	Throughput
10,000	30-45 seconds	45-60 seconds	150-330 records/sec
50,000	2-3 minutes	3-5 minutes	170-400 records/sec
100,000	5-8 minutes	8-12 minutes	140-330 records/sec
500,000	25-40 minutes	40-60 minutes	125-330 records/sec

Note

Performance varies based on:

Network latency between tool and Dataverse
Dataverse API limits and throttling
Number of relationships per activity
Server load at time of execution

Optimisation Tips

1. Run during off-peak hours to avoid API throttling:

# Schedule for 2 AM Sunday
$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At 2am

2. Monitor progress in real-time:

# In another PowerShell window
Get-Content ".\Logs\ParallelProcessing_RebuildRelationships_*" -Wait -Tail 20

3. Use Preview Mode to estimate time:

Preview processes 100 records
Multiply by (total records / 100) to estimate full duration

Troubleshooting

Issue: "No records found to process"

Cause: No activities have mag_relationships data, or all activities are already processed.

Solution:

Verify activities have mag_relationships field populated:

SELECT TOP 10 mag_outcomestepsummaryid, mag_relationships 
FROM mag_outcomestepsummary 
WHERE mag_relationships IS NOT NULL

Check if you're targeting the right environment:

--EnvironmentUrl https://correct-environment.crm6.dynamics.com

Issue: "Throttling detected"

Cause: Exceeded Dataverse API limits (typically 6,000 requests per 5 minutes per user).

Solution:

The tool automatically retries with exponential backoff
Check the log for "Throttling detected, waiting X seconds"
If throttling persists, schedule during off-peak hours or reduce concurrency (requires code change)

Issue: "InvalidActivityTypeException"

Cause: Activity type not recognised or not registered in mag_activityregister.

Solution:

Verify the activity type exists:

SELECT mag_activitytype, mag_logicalname FROM mag_activityregister

If missing, register the activity type in mag_activityregister table

Issue: "Preview shows 0 corrections needed" (Advanced Rebuild)

Cause: All referral-individual combinations are already correct.

Solution:

This is good news! Your data is clean.
You can use standard rebuild instead: --RebuildMMRelationship

Issue: "Relationship records created but multi-select fields empty"

Cause: Entity metadata doesn't include the multi-select fields, or fields don't exist on that activity type.

Solution:

Verify fields exist on the activity entity:

# Check via Power Apps Maker Portal
# Settings → Advanced Settings → Customizations → Customize the System
# Entities → [Activity Type] → Fields → Look for mag_familygroupmembers, mag_inboundreferrals, mag_outcomes

If fields are missing, they need to be added to the activity entity definition

Output & Logs

Log File Location

Logs are created in the Logs folder with timestamped filenames:

Standard Rebuild:

Logs\ParallelProcessing_RebuildRelationships_2024-11-17_14-35-22-123.log

Advanced Rebuild:

Logs\ParallelProcessing_RebuildRelationshipsWithCorrection_2024-11-17_14-40-15-456.log

Log File Contents

Startup Phase:

[2024-11-17 14:35:22.123] Starting parallel relationship rebuilding...
[2024-11-17 14:35:22.456] Found 45,230 records to process
[2024-11-17 14:35:23.789] Creating pool of 50 service clients...
[2024-11-17 14:35:25.012] Service client pool created successfully

Processing Phase:

[2024-11-17 14:35:45.345] Producer: Queued 10,000 of 45,230 records (22.1%)
[2024-11-17 14:36:12.678] Progress: 20,000/45,230 (44.2%) - 180 records/sec
[2024-11-17 14:36:38.901] Progress: 30,000/45,230 (66.3%) - 195 records/sec

Completion Phase:

[2024-11-17 14:37:28.567] Relationship rebuilding completed: 45,230/45,230 records processed
[2024-11-17 14:37:28.890] Total time: 126.4 seconds
[2024-11-17 14:37:28.123] Average throughput: 357 records/second
[2024-11-17 14:37:28.456] Successful: 45,230
[2024-11-17 14:37:28.789] Failed: 0

Error Examples (if any):

[2024-11-17 14:36:55.123] ERROR processing activity 12345678-1234-1234-1234-123456789012: Invalid JSON in mag_relationships field
[2024-11-17 14:37:02.456] ERROR processing activity 23456789-2345-2345-2345-234567890123: Referenced individual does not exist

Understanding the Results

Successful Run Indicators:

✅ "Completed: X/X records processed" where X matches total count
✅ "Failed: 0"
✅ No ERROR lines in the log
✅ Activities now show relationships in Dataverse grids

Warning Signs:

⚠️ "Failed: X" where X > 0 - Review error messages
⚠️ Multiple "Throttling detected" messages - Consider rescheduling
⚠️ "Invalid JSON" errors - mag_relationships data may be corrupted

Table of Contents

Rebuild Activity Relationships

Overview

When to Use This Operation

Use Standard Rebuild When:

Use Advanced Rebuild with Correction When:

Don't Use This Operation When:

Quick Start

Example 1: Preview Standard Rebuild

Example 2: Run Standard Rebuild

Example 3: Advanced Rebuild with Correction

How It Works

Standard Rebuild Process

Advanced Rebuild with Correction Process

mag_relationships JSON Format

NTONDATA Multi-Select Format

Parameters

Required Parameters

--RebuildMMRelationship or --RebuildMMRelationship_withCorrection

--EnvironmentUrl (-e)

--ClientId (-c)

Optional Parameters

--Secret (-s)

Caution

--PreviewMode

Tip

Real-World Scenarios

Scenario 1: Post-Migration Relationship Repair

Scenario 2: Fixing Incorrect Referral Assignments

Scenario 3: Scheduled Weekly Maintenance

Performance & Scalability

Processing Characteristics

Typical Performance

Note

Optimisation Tips

Troubleshooting

Issue: "No records found to process"

Issue: "Throttling detected"

Issue: "InvalidActivityTypeException"

Issue: "Preview shows 0 corrections needed" (Advanced Rebuild)

Issue: "Relationship records created but multi-select fields empty"

Output & Logs

Log File Location

Log File Contents

Understanding the Results

See Also

`--RebuildMMRelationship` or `--RebuildMMRelationship_withCorrection`

`--EnvironmentUrl` (-e)

`--ClientId` (-c)

`--Secret` (-s)

`--PreviewMode`