Bulk-enables D365 F&O data entities for Fabric link / Synapse Link onboarding.

FabricLinkOnboarding.ps1

<#
.SYNOPSIS
    Bulk-enables D365 F&O data entities for Fabric link / Synapse Link onboarding.

.DESCRIPTION
    For each entity, this script:
      1. Looks up the entity in the Dataverse F&O entity catalog (mserp_financeandoperationsentity)
      2. Sets mserp_hasbeengenerated = true (the "Visible" toggle) to generate the mserp_ virtual table
      3. Polls EntityDefinitions until the virtual table has been generated
      4. Enables change tracking by setting mserp_changetrackingenabled = true on the
         catalog record (the "Change Tracking" checkbox on the catalog form)

    Authentication is delegated (user) via Azure CLI. Run 'az login' first.
    The signed-in user needs System Customizer (or higher) in the Dataverse environment.

    PREREQUISITE (F&O side): the feature "Row version change tracking for data entities"
    must be enabled in Feature management, or step 4 will fail validation for most entities.

    NOTE: On the mserp_financeandoperationsentity catalog table the "Visible" checkbox is
    backed by the logical column mserp_hasbeengenerated (there is NO mserp_isvisible column),
    and "Change Tracking" is backed by mserp_changetrackingenabled. Both are writable booleans
    set on the catalog record itself - change tracking can NOT be set via the EntityDefinitions
    metadata API (that returns "Operation not supported on EntityMetadata"). The catalog's only
    columns are: mserp_financeandoperationsentityid, mserp_physicalname,
    mserp_changetrackingenabled, mserp_hasbeengenerated, mserp_refresh.

.PARAMETER OrgUrl
    Dataverse environment URL, e.g. https://yourorg.crm3.dynamics.com

.PARAMETER Entities
    One or more F&O entity physical names (e.g. CustCustomerV3Entity).

.PARAMETER EntityListPath
    Optional path to a text file with one entity physical name per line
    (blank lines and lines starting with # are ignored). Merged with -Entities.

.PARAMETER ReportPath
    Optional path for a CSV status report. Defaults to ./FabricLinkOnboarding_<timestamp>.csv

.PARAMETER GenerationTimeoutMinutes
    How long to wait for each virtual table to be generated. Default: 10

.PARAMETER WhatIfMode
    Discover and report current state only; make no changes.

.EXAMPLE
    az login
    .\Enable-FnOEntitiesForFabricLink.ps1 -OrgUrl "https://contoso.crm3.dynamics.com" `
        -Entities CustCustomerV3Entity, VendVendorV2Entity, MainAccountBiEntity

.EXAMPLE
    .\Enable-FnOEntitiesForFabricLink.ps1 -OrgUrl "https://contoso.crm3.dynamics.com" `
        -EntityListPath .\entities.txt -WhatIfMode
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)]
    [ValidatePattern('^https://[a-zA-Z0-9-]+\.crm[0-9]*\.dynamics\.com/?$')]
    [string]$OrgUrl,

    [string[]]$Entities = @(),

    [string]$EntityListPath,

    [string]$ReportPath = ".\FabricLinkOnboarding_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv",

    [int]$GenerationTimeoutMinutes = 10,

    [switch]$WhatIfMode
)

$ErrorActionPreference = 'Stop'
$OrgUrl = $OrgUrl.TrimEnd('/')
$ApiBase = "$OrgUrl/api/data/v9.2"

# ---------------------------------------------------------------------------
# Build entity list
# ---------------------------------------------------------------------------
$entityList = [System.Collections.Generic.List[string]]::new()
$Entities | ForEach-Object { if ($_) { $entityList.Add($_.Trim()) } }

if ($EntityListPath) {
    if (-not (Test-Path $EntityListPath)) { throw "Entity list file not found: $EntityListPath" }
    Get-Content $EntityListPath | ForEach-Object {
        $line = $_.Trim()
        if ($line -and -not $line.StartsWith('#')) { $entityList.Add($line) }
    }
}

$entityList = $entityList | Select-Object -Unique
if (-not $entityList -or $entityList.Count -eq 0) {
    throw "No entities specified. Use -Entities and/or -EntityListPath."
}

Write-Host "Target environment : $OrgUrl"
Write-Host "Entities to process: $($entityList.Count)"
if ($WhatIfMode) { Write-Host "Mode               : WHAT-IF (no changes will be made)" -ForegroundColor Yellow }
Write-Host ""

# ---------------------------------------------------------------------------
# Auth via Azure CLI (delegated / user context)
# ---------------------------------------------------------------------------
$script:Token       = $null
$script:TokenExpiry = [datetime]::MinValue

function Get-DataverseToken {
    # az caches the refresh token from 'az login'; this silently renews.
    if ((Get-Date) -lt $script:TokenExpiry -and $script:Token) { return $script:Token }

    Write-Verbose "Acquiring Dataverse access token via Azure CLI..."
    $raw = az account get-access-token --resource $OrgUrl --output json 2>$null
    if ($LASTEXITCODE -ne 0 -or -not $raw) {
        throw "Failed to get a token from Azure CLI. Run 'az login' first (and 'az account set' if you have multiple tenants)."
    }
    $tok = $raw | ConvertFrom-Json
    $script:Token = $tok.accessToken
    # Renew 5 minutes before actual expiry
    $script:TokenExpiry = ([datetime]$tok.expiresOn).AddMinutes(-5)
    return $script:Token
}

function Get-Headers {
    param([switch]$Json, [switch]$MergeLabels)
    $h = @{
        Authorization      = "Bearer $(Get-DataverseToken)"
        'OData-MaxVersion' = '4.0'
        'OData-Version'    = '4.0'
        Accept             = 'application/json'
    }
    if ($Json)        { $h['Content-Type']     = 'application/json' }
    if ($MergeLabels) { $h['MSCRM.MergeLabels'] = 'true' }
    return $h
}

# ---------------------------------------------------------------------------
# REST helper with retry / backoff and 401 token refresh
# ---------------------------------------------------------------------------
function Invoke-DataverseApi {
    param(
        [Parameter(Mandatory)] [string]$Uri,
        [string]$Method = 'GET',
        [string]$Body,
        [switch]$MergeLabels,
        [int]$MaxAttempts = 5
    )

    for ($attempt = 1; $attempt -le $MaxAttempts; $attempt++) {
        try {
            $headers = if ($Body) { Get-Headers -Json -MergeLabels:$MergeLabels } else { Get-Headers -MergeLabels:$MergeLabels }
            $params = @{ Method = $Method; Uri = $Uri; Headers = $headers }
            if ($Body) { $params.Body = $Body }
            return Invoke-RestMethod @params
        }
        catch {
            $status = $null
            if ($_.Exception.Response) { $status = [int]$_.Exception.Response.StatusCode }

            # Force token refresh on 401 and retry immediately
            if ($status -eq 401 -and $attempt -lt $MaxAttempts) {
                $script:TokenExpiry = [datetime]::MinValue
                continue
            }
            # Retry transient errors with exponential backoff (cap 60s)
            if ($status -in 429, 500, 502, 503, 504 -and $attempt -lt $MaxAttempts) {
                $delay = [math]::Min(60, [math]::Pow(2, $attempt) * 2)
                # Honor Retry-After if present
                $retryAfter = $_.Exception.Response.Headers['Retry-After']
                if ($retryAfter) { $delay = [math]::Min(120, [int]$retryAfter) }
                Write-Verbose "HTTP $status on attempt $attempt; retrying in ${delay}s..."
                Start-Sleep -Seconds $delay
                continue
            }
            throw
        }
    }
}

function Get-ErrorMessage {
    param($ErrorRecord)
    try {
        $detail = $ErrorRecord.ErrorDetails.Message | ConvertFrom-Json
        if ($detail.error.message) { return $detail.error.message }
    } catch { }
    return $ErrorRecord.Exception.Message
}

# ---------------------------------------------------------------------------
# Step functions
# ---------------------------------------------------------------------------
function Get-CatalogRecord {
    param([string]$PhysicalName)
    $uri = "$ApiBase/mserp_financeandoperationsentities" +
           "?`$filter=mserp_physicalname eq '$PhysicalName'" +
           "&`$select=mserp_financeandoperationsentityid,mserp_physicalname,mserp_hasbeengenerated,mserp_changetrackingenabled"
    $result = Invoke-DataverseApi -Uri $uri
    if ($result.value.Count -gt 0) { return $result.value[0] }
    return $null
}

function Set-EntityVisible {
    param([string]$CatalogId)
    Invoke-DataverseApi -Method PATCH `
        -Uri "$ApiBase/mserp_financeandoperationsentities($CatalogId)" `
        -Body '{ "mserp_hasbeengenerated": true }' | Out-Null
}

function Wait-ForVirtualTable {
    param([string]$LogicalName, [int]$TimeoutMinutes)
    $deadline = (Get-Date).AddMinutes($TimeoutMinutes)
    while ((Get-Date) -lt $deadline) {
        try {
            return Invoke-DataverseApi -MaxAttempts 1 `
                -Uri "$ApiBase/EntityDefinitions(LogicalName='$LogicalName')?`$select=ChangeTrackingEnabled"
        }
        catch {
            $status = if ($_.Exception.Response) { [int]$_.Exception.Response.StatusCode } else { 0 }
            if ($status -ne 404) { throw }
            Write-Host "    ...waiting for table generation" -ForegroundColor DarkGray
            Start-Sleep -Seconds 20
        }
    }
    return $null
}

function Enable-ChangeTracking {
    # Change tracking for an F&O virtual table is NOT set via the EntityDefinitions
    # metadata API (that returns "Operation not supported on EntityMetadata"). It is a
    # writable boolean on the catalog record - the same "Change Tracking" checkbox on the
    # "Available Finance and Operations Entity" form.
    param([string]$CatalogId)
    Invoke-DataverseApi -Method PATCH `
        -Uri "$ApiBase/mserp_financeandoperationsentities($CatalogId)" `
        -Body '{ "mserp_changetrackingenabled": true }' | Out-Null
}

# ---------------------------------------------------------------------------
# Sanity checks
# ---------------------------------------------------------------------------
if (-not (Get-Command az -ErrorAction SilentlyContinue)) {
    throw "Azure CLI ('az') not found on PATH. Install it or use a machine that has it."
}
$whoami = Invoke-DataverseApi -Uri "$ApiBase/WhoAmI"
Write-Host "Authenticated. Dataverse UserId: $($whoami.UserId)"
Write-Host ""

# ---------------------------------------------------------------------------
# Main loop
# ---------------------------------------------------------------------------
$report = [System.Collections.Generic.List[pscustomobject]]::new()

foreach ($entity in $entityList) {
    Write-Host "[$entity]" -ForegroundColor Cyan
    $row = [pscustomobject]@{
        Entity                = $entity
        FoundInCatalog        = $false
        WasVisible            = $false
        VisibilityEnabled     = $false
        TableGenerated        = $false
        ChangeTrackingAlready = $false
        ChangeTrackingEnabled = $false
        Status                = ''
        Error                 = ''
    }

    try {
        # --- Step 1: catalog lookup ---
        $cat = Get-CatalogRecord -PhysicalName $entity
        if (-not $cat) {
            $row.Status = 'NotFound'
            Write-Warning "  Not found in the F&O entity catalog (check the physical name)."
            $report.Add($row); continue
        }
        $row.FoundInCatalog = $true
        $row.WasVisible     = [bool]$cat.mserp_hasbeengenerated

        # --- Step 2: visibility ---
        if ($cat.mserp_hasbeengenerated) {
            Write-Host "  Already visible." -ForegroundColor DarkGray
        }
        elseif ($WhatIfMode) {
            Write-Host "  WOULD set mserp_hasbeengenerated = true" -ForegroundColor Yellow
        }
        else {
            Set-EntityVisible -CatalogId $cat.mserp_financeandoperationsentityid
            $row.VisibilityEnabled = $true
            Write-Host "  Visibility enabled; virtual table generation triggered."
        }

        # --- Step 3: wait for the virtual table ---
        $logical = 'mserp_' + $entity.ToLower()
        if ($WhatIfMode -and -not $cat.mserp_hasbeengenerated) {
            $row.Status = 'WhatIf'
            $report.Add($row); continue
        }

        $def = Wait-ForVirtualTable -LogicalName $logical -TimeoutMinutes $GenerationTimeoutMinutes
        if (-not $def) {
            $row.Status = 'GenerationTimeout'
            Write-Warning "  Table '$logical' did not appear within $GenerationTimeoutMinutes minutes."
            $report.Add($row); continue
        }
        $row.TableGenerated = $true

        # --- Step 4: change tracking (set on the catalog record, not via metadata) ---
        if ([bool]$cat.mserp_changetrackingenabled) {
            $row.ChangeTrackingAlready = $true
            $row.Status = 'AlreadyOnboarded'
            Write-Host "  Change tracking already enabled. Ready for Fabric link." -ForegroundColor Green
        }
        elseif ($WhatIfMode) {
            $row.Status = 'WhatIf'
            Write-Host "  WOULD enable change tracking (mserp_changetrackingenabled = true)" -ForegroundColor Yellow
        }
        else {
            try {
                Enable-ChangeTracking -CatalogId $cat.mserp_financeandoperationsentityid
                $row.ChangeTrackingEnabled = $true
                $row.Status = 'Ready'
                Write-Host "  Change tracking enabled. Ready for Fabric link." -ForegroundColor Green
            }
            catch {
                $row.Status = 'ChangeTrackingFailed'
                $row.Error  = Get-ErrorMessage $_
                Write-Warning "  Change tracking failed validation: $($row.Error)"
                Write-Warning "  (Check that 'Row version change tracking for data entities' is enabled in F&O Feature management, or the entity may be unsupported.)"
            }
        }
    }
    catch {
        $row.Status = 'Error'
        $row.Error  = Get-ErrorMessage $_
        Write-Warning "  Unexpected error: $($row.Error)"
    }

    $report.Add($row)
}

# ---------------------------------------------------------------------------
# Summary
# ---------------------------------------------------------------------------
Write-Host ""
Write-Host "================ SUMMARY ================" -ForegroundColor Cyan
$report | Format-Table Entity, Status, TableGenerated, ChangeTrackingEnabled, Error -AutoSize

$report | Export-Csv -Path $ReportPath -NoTypeInformation -Encoding UTF8
Write-Host "Report written to: $ReportPath"

$ready = @($report | Where-Object { $_.Status -in 'Ready', 'AlreadyOnboarded' }).Count
Write-Host ""
Write-Host "$ready of $($entityList.Count) entities are ready for Fabric link."
if ($ready -gt 0) {
    Write-Host "Final step (manual): PPAC > your Fabric link profile > Manage tables > add the mserp_ tables."
}