Functions/Import-BsgPbiReport.ps1

<#
    .SYNOPSIS
        Import Power BI report in a workspace.
         
    .DESCRIPTION
        Create a report from a definition stored in a JSON file called "Mapping_ReportDataset.json".
 
    .PARAMETER Target_WorkspaceId
        The id of the workspace where the report should be imported.
        You can find it in the Power BI workspace URL.
 
    .PARAMETER Source_ReportId
        The id of the original report you would like to import.
        You can find it in the Power BI report URL or in the exported file (when using our export Module).
        The id need to be placed in the end of the PBIX-filename (if you are not using our Module).
 
    .PARAMETER Path_Workspace
        The path to the workspace folder.
        Report files (PBIX) must be stored in the subfolder "reports".
 
    .PARAMETER ProcessTenantLevelReport
        Reports which are based on datasets in other workspaces must be processed after all the workspaces have been processed.
        Only process tenant level reports if all datasets have been imported.
        Default = $false.
 
    .EXAMPLE
        # Import report
        Import-BsgPbiReport -Target_WorkspaceId "37608cb0-489d-428f-b54f-34d07e0925e9" -Source_ReportId "feb0cb47-a831-49ed-86c9-ca58624b5521" -Path_Workspace "C:\temp\BSG PBI Administration\Backup\Workspaces\BSGroup DA - Test Workspace"
         
        # Import report tenant level
        Import-BsgPbiReport -Target_WorkspaceId "37608cb0-489d-428f-b54f-34d07e0925e9" -Source_ReportId "feb0cb47-a831-49ed-86c9-ca58624b5521" -Path_Workspace "C:\temp\BSG PBI Administration\Backup\Workspaces\BSGroup DA - Test Workspace" -ProcessTenantLevelReport $true
 
    .INPUTS
 
    .OUTPUTS
 
    .NOTES
        This script uses the Power BI Management module for Windows PowerShell. If this module isn't installed, install it by using the command 'Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser'.
#>


function Import-BsgPbiReport{

    param(
        [Parameter(Mandatory=$true)][string]$Target_WorkspaceId,
        [Parameter(Mandatory=$true)][guid]$Source_ReportId,
        [Parameter(Mandatory=$true)][string]$Path_Workspace,
        [Parameter(Mandatory=$false)][bool]$ProcessTenantLevelReport = $false
    )

    try{

        Write-Host
        Write-PSFHostColor -Level Host -DefaultColor white -String "== Importing report..."

        # Define paths and filenames
        $Path_Reports = Join-Path -Path $Path_Workspace -ChildPath 'reports'
        $FileName_ReportDatasetMapping = "Mapping_ReportDataset.json"
        $Path_ReportDatasetMapping = Join-Path $Path_Workspace -ChildPath $FileName_ReportDatasetMapping

        if ($Target_WorkspaceId -eq 'me'){
            $Target_WorkspaceUrl = "https://api.powerbi.com/v1.0/myorg"
        } else{
            $Target_WorkspaceUrl = "https://api.powerbi.com/v1.0/myorg/groups/" + $Target_WorkspaceId
        }


        # ===
        # Get mapping file
        # =

        try{
            
            # Check path
            if ((Test-Path $Path_Reports) -eq $false){
                throw "Path `"$Path_Reports`" does not exist."
            }

            # Check mapping file
            $Path_Workspace = Split-Path -Path $Path_Reports -Parent
            if ((Test-Path $Path_ReportDatasetMapping) -eq $false){
                throw "File does not exist is folder `"$Path_Workspace`"."
            }
            $DatasetReport_Mapping = Get-Content -Path $Path_ReportDatasetMapping | ConvertFrom-Json -ErrorAction Stop

        } catch{
            throw "Error while getting report-dataset mapping file `"$FileName_ReportDatasetMapping`". `n$_"   
        }

        
        # ===
        # Get report metadata
        # =

        try{

            # Get file
            $MappingObject = $DatasetReport_Mapping | Where-Object {$_.reportId -eq $Source_ReportId}

            # Check if report id exists
            if ($null -eq $MappingObject){
                throw "Report id `"$Source_ReportId`" not found."
            } else{
                $Source_ReportName = $MappingObject.reportName
                $Source_DatasetId = $MappingObject.datasetId
                $Target_ReportName = $MappingObject.new_reportName
                $Path_TargetReport = Join-Path -Path $Path_Reports -ChildPath $Target_ReportName # without file ending
                $isDatasetOwner = $MappingObject.isDatasetOwner

                Write-PSFHostColor -Level Host -DefaultColor gray -String " Name: <c='white'>$Target_ReportName</c>"
            }
            
            # Check if report was already processed
            if ($null -ne $MappingObject.new_ReportId){
                Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Info</c>: Report with ID $Source_ReportId has already been processed."
                Write-PSFHostColor -Level Host -DefaultColor gray -String " Remove new_reportId and new_datasetId property in mapping file if you would like to import the report again."
                Write-PSFHostColor -Level Host -DefaultColor green -String " Report skipped (already processed)."
                return
            }

            # ===
            # Find shared dataset
            # =

            $SharedDatasetObject = $DatasetReport_Mapping | Where-Object {($_.datasetId -eq $Source_DatasetId) -and ($_.isDatasetOwner)}
            if ($SharedDatasetObject.Count -gt 1){
                throw "DatasetId $Source_DatasetId must have a unique 'isDatasetOwner = true' property defined in mapping file. `nPlease edit mapping file."
            }
            if ($ProcessTenantLevelReport){
                $Target_SharedDatasetId = $MappingObject.new_datasetId
                $Target_DatasetName = $MappingObject.new_datasetName
                $Target_ModelId = $MappingObject.new_modelId
            } else {
                $Target_SharedDatasetId = $SharedDatasetObject.new_datasetId
                $Target_DatasetName = $SharedDatasetObject.new_datasetName
                $Target_ModelId = $SharedDatasetObject.new_modelId
            }

        } catch{
            throw "Error while checking report-dataset mapping file `"$FileName_ReportDatasetMapping`". `n$_"
        }


        # ===
        # Prepare new report file (PBIX)
        # =

        try{

            # Get filename based on report ID
            $Source_ReportFilename = Get-ChildItem -Path $Path_Reports -Include "*$Source_ReportId.pbix" -Name -ErrorAction Stop
            $Path_SourceFile = Join-Path -Path $Path_Reports -ChildPath $Source_ReportFilename

            # Copy, rename and change to zip file
            Copy-Item -Path $Path_SourceFile -Destination "$Path_TargetReport.pbix" -ErrorAction Stop

        } catch{
            throw "Error while preparing new report file. `n$_"
        }


        # ===
        # Restore report by action (before import report)
        # =

        switch ($MappingObject.restoreAction) {
            "CreateDataset" { 
                # Normal case - Report and dataset will be imported...
            }
            "RebindReport" {
                # later: import and rebind report
            }
            "UpdatePbiConnection" {

                try{

                    # ===
                    # Update report connection
                    # =

                    # Get connection
                    $PbiReportConnection = Get-BsgPbiReportConnection -Filename $Target_ReportName -Path $Path_Reports

                    # change connection string
                    $PbiReportConnection.Connections[0].ConnectionString = $PbiReportConnection.Connections[0].ConnectionString -replace $Source_DatasetId, $Target_SharedDatasetId

                    # change PbiModelDatabaseName
                    $PbiReportConnection.Connections[0].PbiModelDatabaseName = $PbiReportConnection.Connections[0].PbiModelDatabaseName -replace $Source_DatasetId, $Target_SharedDatasetId

                    # change PbiServiceModelId, when we know it
                    if ($null -ne $Target_ModelId){
                        $PbiReportConnection.Connections[0].PbiServiceModelId = $Target_ModelId
                    }

                    # Update connection file
                    Update-BsgPbiReportConnection -Filename $Target_ReportName -Path $Path_Reports -ConnectionObject $PbiReportConnection

                    Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Report connection updated</c>."

                    # define new dataset id
                    $Target_DatasetId = $Target_SharedDatasetId


                    # ===
                    # Open and save .pbix file for PBI Service connection to update PbiServiceModelId in connection file
                    # =

                    if ($null -eq $Target_ModelId){
                        # Open .pbix file
                        Invoke-Item -Path "$Path_TargetReport.pbix"
                                            
                        # Save .pbix file
                        # ?: Is there way to automate saving power bi file?
                        Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Info</c>: The connection to report `"$Target_ReportName`" needs to be reestablished."
                        Write-PSFHostColor -Level Host -DefaultColor gray -String " In order to update the connection, please wait for Power BI to start, then save and close the file..."
                        Write-Host
                        $confirmation = Read-Host "Did you save and close the PBI report file? [y/n]"
                        Write-Host
                        if ($confirmation -eq 'y'){
                            # continue processing...
                        } else{
                            Write-Host
                            Write-Warning "Report $Target_ReportName needs to be imported manually."
                            Write-PSFHostColor -Level Host -DefaultColor gray -String " Location: <c='white'>$Path_TargetReport.pbix</c>"
                            Write-Host
                            return
                        }

                        # get new PbiServiceModelId
                        $PbiReportConnection = Get-BsgPbiReportConnection -Filename $Target_ReportName -Path $Path_Reports
                        $Target_ModelId = $PbiReportConnection.Connections.PbiServiceModelId

                        # update mapping objects
                        foreach ($report in $DatasetReport_Mapping) {
                            if ($report.datasetId -eq $Source_DatasetId){
                                
                                # Add new model id
                                $report | Add-Member -MemberType NoteProperty -Name "new_modelId" -Value $Target_ModelId -Force
            
                            }
                        }
                    }

                } catch{
                    throw "Error trying to update report connection. `n$_"
                }

            }
            "Wait_UpdatePbiConnection" {

                if ($ProcessTenantLevelReport){
                    try{

                        # ===
                        # Update report connection
                        # =
    
                        # Get connection
                        $PbiReportConnection = Get-BsgPbiReportConnection -Filename $Target_ReportName -Path $Path_Reports
    
                        # change connection string
                        $PbiReportConnection.Connections[0].ConnectionString = $PbiReportConnection.Connections[0].ConnectionString -replace $Source_DatasetId, $Target_SharedDatasetId
    
                        # change PbiModelDatabaseName
                        $PbiReportConnection.Connections[0].PbiModelDatabaseName = $PbiReportConnection.Connections[0].PbiModelDatabaseName -replace $Source_DatasetId, $Target_SharedDatasetId
    
                        # change PbiServiceModelId, when we know it
                        if ($null -ne $Target_ModelId){
                            $PbiReportConnection.Connections[0].PbiServiceModelId = $Target_ModelId
                        }
    
                        # Update connection file
                        Update-BsgPbiReportConnection -Filename $Target_ReportName -Path $Path_Reports -ConnectionObject $PbiReportConnection
    
                        Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Report connection updated</c>."
    
                        # define new dataset id
                        $Target_DatasetId = $Target_SharedDatasetId
    
    
                        # ===
                        # Open and save .pbix file for PBI Service connection to update PbiServiceModelId in connection file
                        # =

                        if ($null -eq $Target_ModelId){
                            # Open .pbix file
                            Invoke-Item -Path "$Path_TargetReport.pbix"
                            
                            # Save .pbix file
                            # ?: Is there way to automate saving power bi file?
                            Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Info</c>: The connection to report `"$Target_ReportName`" needs to be reestablished."
                            Write-PSFHostColor -Level Host -DefaultColor gray -String " In order to update the connection, please wait for Power BI to start, then save and close the file..."
                            Write-Host
                            $confirmation = Read-Host "Did you save and close the PBI report file? [y/n]"
                            Write-Host
                            if ($confirmation -eq 'y'){
                                # continue processing...
                            } else{
                                Write-Host
                                Write-Warning "Report $Target_ReportName needs to be imported manually."
                                Write-PSFHostColor -Level Host -DefaultColor gray -String " Location: <c='white'>$Path_TargetReport.pbix</c>"
                                Write-Host
                                return
                            }

                            # get new PbiServiceModelId
                            $PbiReportConnection = Get-BsgPbiReportConnection -Filename $Target_ReportName -Path $Path_Reports
                            $Target_ModelId = $PbiReportConnection.Connections.PbiServiceModelId

                            # update mapping objects
                            foreach ($report in $DatasetReport_Mapping) {
                                if ($report.datasetId -eq $Source_DatasetId){
                                    
                                    # Add new model id
                                    $report | Add-Member -MemberType NoteProperty -Name "new_modelId" -Value $Target_ModelId -Force
                
                                }
                            }
                        }
    
                    } catch{
                        throw "Error trying to update report connection. `n$_"
                    }
                } else {
                    Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Info</c>: Report with ID $Source_ReportId is based on a dataset in another workspace."
                    Write-PSFHostColor -Level Host -DefaultColor gray -String " The report will be imported after all workspaces have been imported."
                    Write-PSFHostColor -Level Host -DefaultColor yellow -String " Report skipped (waiting for other workspaces)."

                    # ===
                    # Delete temporary report file
                    # =

                    if (Test-Path -Path "$Path_TargetReport.pbix"){
                        Remove-Item "$Path_TargetReport.pbix"
                    }

                    return
                }
                

            }
            "CreateDataset_RebindReport" {

                # ===
                # Craete dataset (import reprot with dataset name)
                # =

                try{

                    # Create dataset
                    if ($Target_WorkspaceId -eq 'me'){
                        $Target_ReportHoldingDataset = New-PowerBIReport -Path "$Path_TargetReport.pbix" -Name $Target_DatasetName -ErrorAction Stop
                    } else{
                        $Target_ReportHoldingDataset = New-PowerBIReport -Path "$Path_TargetReport.pbix" -Name $Target_DatasetName -WorkspaceId $Target_WorkspaceId -ErrorAction Stop
                    }

                    # Get new report again, to get dataset id (not available before)
                    try{
                        if ($Target_WorkspaceId -eq "me") { 
                            $Target_ReportHoldingDataset = Get-PowerBIReport -Id $Target_ReportHoldingDataset.id
                        } else { 
                            $Target_ReportHoldingDataset = Get-PowerBIReport -Id $Target_ReportHoldingDataset.id -WorkspaceId $Target_WorkspaceId 
                        }
                        $Target_SharedDatasetId = $Target_ReportHoldingDataset.datasetId
                        $Target_ReportHoldingDatasetId = $Target_ReportHoldingDataset.id
                        $Target_ReportHoldingDatasetName = $Target_ReportHoldingDataset.name
                    } catch{
                        throw "Error trying to get dataset id of new report. `n$_"
                    }

                } catch{
                    throw "Error trying to create new dataset in workspace. `n$_"
                }

                # ===
                # Delete report assigned to dataset
                # =

                try{

                    # Create URL for API requests
                    $RequestUrl = $Target_WorkspaceUrl + "/reports/$Target_ReportHoldingDatasetId"

                    # API-call
                    $response = Invoke-PowerBIRestMethod -Url $RequestUrl -Method Delete -ErrorAction Stop

                } catch{
                    throw "Error trying to delete temporary report `"$Target_ReportHoldingDatasetName`" after creating dataset with id `"$Target_SharedDatasetId`". `n$_"
                }

                # later: import and rebind report

            }
            "Skip_UsageMetrics" {
                Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Info</c>: Usage Metrics report can be recreated by the user."
                Write-PSFHostColor -Level Host -DefaultColor green -String " Report skipped (expected behaviour)."
                # ===
                # Delete temporary report file
                # =

                if (Test-Path -Path "$Path_TargetReport.pbix"){
                    Remove-Item "$Path_TargetReport.pbix"
                }

                return
            }
            "Skip" {
                Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Info</c>: RestoreAction is marked as 'Skip'."
                Write-PSFHostColor -Level Host -DefaultColor yellow -String " Report skipped."

                # ===
                # Delete temporary report file
                # =

                if (Test-Path -Path "$Path_TargetReport.pbix"){
                    Remove-Item "$Path_TargetReport.pbix"
                }

                return
            }
            Default {
                Write-Host
                Write-Warning "Report has no defined restore action."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor yellow -String " Report skipped."

                # ===
                # Delete temporary report file
                # =

                if (Test-Path -Path "$Path_TargetReport.pbix"){
                    Remove-Item "$Path_TargetReport.pbix"
                }

                return
            }
        }


        # ===
        # Import new report into workspace
        # =

        try{

            # Create Report
            if ($Target_WorkspaceId -eq 'me'){
                $Target_Report = New-PowerBIReport -Path "$Path_TargetReport.pbix" -Name $Target_ReportName -ErrorAction Stop
            } else{
                $Target_Report = New-PowerBIReport -Path "$Path_TargetReport.pbix" -Name $Target_ReportName -WorkspaceId $Target_WorkspaceId -ErrorAction Stop
            }
            $Target_ReportId = $Target_Report.id

        } catch{
            throw "Error trying to create new report in workspace. `n$_"
        }


        # ===
        # Get new report again, to get dataset id (not available before)
        # =

        try{
            if ($Target_WorkspaceId -eq "me") { 
                $Target_Report = Get-PowerBIReport -Id $Target_ReportId
            } else { 
                $Target_Report = Get-PowerBIReport -Id $Target_ReportId -WorkspaceId $Target_WorkspaceId 
            }
            $Target_DatasetId = $Target_Report.datasetId
        } catch{
            throw "Error trying to get dataset id of new report. `n$_"
        }      


        # ===
        # Delete temporary report file
        # =

        try{
            Remove-Item "$Path_TargetReport.pbix" -ErrorAction Stop
        } catch{
            throw "Error trying to cleanup temporary report file. `n$_"
        }


        # ===
        # Rebind report to other dataset
        # =
        if ($MappingObject.restoreAction -match 'RebindReport'){
            try{

                # ===
                # Rebind dataset
                # =

                try{

                    # Prepare new JSON
                    $Target_SharedDatasetId_JSON = '{"datasetId" : "' + $Target_SharedDatasetId + '"}'

                    # Create URL for API requests
                    $RequestUrl = $Target_WorkspaceUrl + "/reports/$Target_ReportId/Rebind"
                    
                    # API-call
                    $response = Invoke-PowerBIRestMethod -Url $RequestUrl -Method Post -Body $Target_SharedDatasetId_JSON -ErrorAction Stop
                    Write-PSFHostColor -Level Host -DefaultColor gray -String " <c='white'>Report rebound</c> to dataset id $Target_SharedDatasetId"

                } catch{
                    throw "Request: $RequestUrl. `n$_"
                }

                # ===
                # Delete old dataset after rebind
                # =

                try{

                    # Create URL for API requests
                    $RequestUrl = $Target_WorkspaceUrl + "/datasets/$Target_DatasetId"

                    # API-call
                    $response = Invoke-PowerBIRestMethod -Url $RequestUrl -Method Delete -ErrorAction Stop
                    # Write-PSFMessage -Level Verbose -Message "Old dataset with id `"$Target_DatasetId`" deleted."

                } catch{
                    throw "Could not delete old dataset with id `"$Target_DatasetId`". `n$_"
                }

                # define new dataset id
                $Target_DatasetId = $Target_SharedDatasetId


            } catch{
                throw "Error trying to rebind report `"$Target_ReportName`" with dataset id `"$Target_DatasetId`". `n$_"
            }
        }

        # ===
        # Delete report if it is a temporary report to create the dataset
        # =

        if ($Target_ReportName -match "BSG.Migration.Temp"){
            try{

                # Create URL for API requests
                $RequestUrl = $Target_WorkspaceUrl + "/reports/$Target_ReportId"
    
                # API-call
                $response = Invoke-PowerBIRestMethod -Url $RequestUrl -Method Delete -ErrorAction Stop
    
            } catch{
                throw "Error trying to delete temporary report `"$Target_ReportName`". `n$_"
            }
        }


        # ===
        # Change mapping file
        # =

        try{
            foreach ($report in $DatasetReport_Mapping) {
                if ($report.reportId -eq $Source_ReportId){
                    
                    # Add new report id
                    $report | Add-Member -MemberType NoteProperty -Name "new_reportId" -Value $Target_ReportId -Force

                    # Add new dataset id
                    $report | Add-Member -MemberType NoteProperty -Name "new_datasetId" -Value $Target_DatasetId -Force

                }
            }
            

            # ===
            # Save changes to JSON
            # =

            $DatasetReport_Mapping | ConvertTo-Json | Out-File -FilePath $Path_ReportDatasetMapping -ErrorAction Stop
            
        } catch{
            throw "Error while changing report-dataset mapping file `"$FileName_ReportDatasetMapping`". `n$_"
        }

        Write-PSFHostColor -Level Host -DefaultColor green -String ' Report imported.'

    } catch{
        # Cleanup temporary files
        if (Test-Path "$Path_TargetReport.pbix"){
            Remove-Item -Path "$Path_TargetReport.pbix"
        }
        Write-Host
        Stop-PSFFunction -Message "Could not import report." -EnableException $False -ErrorRecord $_
    }
}