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 $_ } } |