Creating a Dataverse Relationship Report using PowerShell

Dataverse relationships define how tables (entities) connect within the Microsoft Power Platform. A relationship report helps analyze dependencies between tables, identify one-to-many (1:N), many-to-one (N:1), and many-to-many (N:N) relationships, and document them for auditing or development purposes.

This guide covers:

Connecting to Dataverse using PowerShell
Retrieving all table relationships
Extracting relationship types (1:N, N:1, N:N)
Exporting the report to CSV/JSON
Automating the report generation

---

Step 1: Prerequisites

1. Install Required PowerShell Modules

Ensure the necessary PowerShell module is installed:

# Install Power Platform module if not installed
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import the module
Import-Module Microsoft.PowerPlatform.Cds.Client

2. Required Permissions

You need the System Administrator or Customizer role to retrieve table relationships.

---

Step 2: Connecting to Dataverse

Option 1: Interactive Login

Use this method for manual execution:

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

Option 2: Using Service Principal (For Automation)

For scheduled or automated execution, use Azure AD App Registration:

# Define credentials
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Convert secret to secure string
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Now connected to Dataverse!

---

Step 3: Retrieving Table Relationships

Fetch all relationships in Dataverse

# Retrieve all table relationships
$relationships = Get-CdsRelationship -Connection $connection

# Display the total count
Write-Host "Total relationships found: $($relationships.Count)"

This fetches all relationships across Dataverse.

---

Step 4: Extracting Relationship Types

Dataverse supports three relationship types:

1. One-to-Many (1:N) → A single record in Table A links to multiple records in Table B.
2. Many-to-One (N:1) → Multiple records in Table A link to a single record in Table B.
3. Many-to-Many (N:N) → Records in Table A and Table B can be linked to multiple records in both tables.

# Classify relationships by type
$oneToMany = $relationships | Where-Object { $_.Type -eq "OneToMany" }
$manyToOne = $relationships | Where-Object { $_.Type -eq "ManyToOne" }
$manyToMany = $relationships | Where-Object { $_.Type -eq "ManyToMany" }

Write-Host "One-to-Many Relationships: $($oneToMany.Count)"
Write-Host "Many-to-One Relationships: $($manyToOne.Count)"
Write-Host "Many-to-Many Relationships: $($manyToMany.Count)"

Relationships are now classified!

---

Step 5: Formatting the Relationship Report

To create a structured report:

# Create an array for storing report data
$reportData = @()

# Process each relationship
foreach ($rel in $relationships) {
    $reportData += [PSCustomObject]@{
        RelationshipName = $rel.SchemaName
        RelationshipType = $rel.Type
        PrimaryTable = $rel.PrimaryEntityLogicalName
        RelatedTable = $rel.ReferencingEntityLogicalName
    }
}

# Display the report in a table format
$reportData | Format-Table -AutoSize

The report is now structured and readable.

---

Step 6: Exporting the Report

1. Export to CSV

To save the report in CSV format:

# Define export path
$csvPath = "C:\Reports\Dataverse_Relationships.csv"

# Export to CSV
$reportData | Export-Csv -Path $csvPath -NoTypeInformation

Write-Host "Relationship report exported to $csvPath"

The CSV report is now ready!

---

2. Export to JSON

To save the report in JSON format:

# Define export path
$jsonPath = "C:\Reports\Dataverse_Relationships.json"

# Export to JSON
$reportData | ConvertTo-Json | Out-File -FilePath $jsonPath

Write-Host "Relationship report exported to $jsonPath"

The JSON report is now saved!

---

Step 7: Automating the Report Generation

To schedule the report daily at 2 AM, use Windows Task Scheduler:

# Schedule the script
$taskAction = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-ExecutionPolicy Bypass -File C:\Scripts\DataverseRelationshipReport.ps1"
$taskTrigger = New-ScheduledTaskTrigger -Daily -At 2AM
Register-ScheduledTask -TaskName "Dataverse Relationship Report" -Action $taskAction -Trigger $taskTrigger -User "NT AUTHORITY\SYSTEM" -RunLevel Highest -Force

The script runs automatically every day at 2 AM!

---

Step 8: Verifying the Report

To manually review the report, open:
📄 C:\Reports\Dataverse_Relationships.csv
📄 C:\Reports\Dataverse_Relationships.json

Alternatively, use PowerShell:

# Read the exported CSV
Import-Csv -Path "C:\Reports\Dataverse_Relationships.csv" | Format-Table -AutoSize

Ensures the report contains accurate data.