[PBIRS] Power BI report server Migration - moxuemeivip/Microsoft-Analysis-Service-Or-Fabric GitHub Wiki

useful blog

Troubleshooting Common Migration Issues

Useful Scripts

Use these PowerShell and T-SQL scripts to help with your inventory and auditing process.

PowerShell: Inventory All Reports & Data Sources

# Requires ReportingServicesTools module: Install-Module -Name ReportingServicesTools
$ReportServerUri = "http://YourOldServer/ReportServer"
$OutFile = "C:MigrationPBIRS_Inventory.csv"

$session = New-RsRestSession -ReportPortalUri $ReportServerUri
$catalogItems = Get-RsFolderContent -ReportServerUri $ReportServerUri -RsFolder "/" -Recurse

$reportInventory = @()
foreach ($item in $catalogItems) {
    $itemType = $item.TypeName
    $details = ""
    if ($itemType -in @("PowerBIReport", "Report")) {
        try {
            $dataSources = Get-RsDataSource -ReportServerUri $ReportServerUri -RsItem $item.Path
            $dsNames = ($dataSources.DataSource.Name | ForEach-Object { $_ -replace ';', ',' }) -join "; "
            $details = $dsNames
        } catch { $details = "Error fetching data sources" }
    }
    $reportInventory += @{
        Name         = $item.Name
        Path         = $item.Path
        Type         = $itemType
        CreatedBy    = $item.CreatedBy
        CreationDate = $item.CreationDate
        ModifiedBy   = $item.ModifiedBy
        ModifiedDate = $item.ModifiedDate
        DataSources  = $details
    }
}
$reportInventory | Export-Csv -Path $OutFile -NoTypeInformation
Write-Host "Inventory exported to $OutFile"

PowerShell: Inventory Power BI Refresh Schedules

# Requires ReportingServicesTools module
$ReportServerUri = "http://YourOldServer/ReportServer"
$OutFile = "C:MigrationPBIRS_RefreshSchedules.csv"

$session = New-RsRestSession -ReportPortalUri $ReportServerUri
$pbiReports = Get-RsFolderContent -ReportServerUri $ReportServerUri -RsFolder "/" -Recurse | Where-Object { $_.TypeName -eq "PowerBIReport" }

$refreshPlans = @()
foreach ($report in $pbiReports) {
    try {
        $plans = Get-RsRestCacheRefreshPlan -ReportServerUri $ReportServerUri -Path $report.Path
        if ($null -ne $plans) {
            foreach ($plan in $plans.value) {
                $refreshPlans += @{
                    ReportName   = $report.Name
                    ReportPath   = $report.Path
                    PlanID       = $plan.Id
                    Owner        = $plan.Owner
                    ScheduleDesc = $plan.Description
                    LastRunTime  = $plan.LastRunTime
                    LastStatus   = $plan.LastStatus
                }
            }
        }
    } catch { Write-Warning "Could not get refresh plan for $($report.Path)" }
}
$refreshPlans | Export-Csv -Path $OutFile -NoTypeInformation
Write-Host "Refresh schedule inventory exported to $OutFile"

T-SQL: Audit Folder and Item Security

-- Run against the ReportServer database
SELECT
    c.Path,
    c.Name AS ItemName,
    CASE c.Type
        WHEN 1 THEN 'Folder'
        WHEN 2 THEN 'Paginated Report'
        WHEN 5 THEN 'Data Source'
        WHEN 7 THEN 'Shared Dataset'
        WHEN 13 THEN 'Power BI Report'
        ELSE 'Other'
    END AS ItemType,
    u.UserName,
    r.RoleName,
    r.Description AS RoleDescription
FROM
    dbo.Catalog c
JOIN
    dbo.PolicyUserRole pur ON c.PolicyID = pur.PolicyID
JOIN
    dbo.Users u ON pur.UserID = u.UserID
JOIN
    dbo.Roles r ON pur.RoleID = r.RoleID
ORDER BY
    c.Path, u.UserName;

T-SQL: Audit Paginated Report Subscriptions

-- Run against the ReportServer database
SELECT
    c.Name AS ReportName,
    c.Path AS ReportPath,
    s.Description AS SubscriptionDescription,
    s.EventType,
    s.DeliveryExtension,
    u.UserName AS Owner,
    s.LastStatus,
    s.LastRunTime,
    s.Parameters,
    CASE s.DataChanged
        WHEN 1 THEN 'Data-Driven'
        ELSE 'Standard'
    END AS SubscriptionType
FROM
    dbo.Subscriptions s
JOIN
    dbo.Catalog c ON s.Report_OID = c.ItemID
JOIN
    dbo.Users u ON s.OwnerID = u.UserID
ORDER BY
    c.Path, c.Name;