Functions/Get-BsgPBIWorkspaceAssessment.ps1
|
<#
.SYNOPSIS Get a list of all the objects on one or more workspaces in Power BI to help to create an assesstment .DESCRIPTION Get a list of all the objects on one or more workspaces in Power BI. The result can be exported to MS Excel. This will help you to get an overview about the current situation of the workspaces and help to create an assessment. Use paramter -mode "Full" to get more assessment information. .PARAMETER PbiWorkspace Type: Microsoft.PowerBI.Common.Api.Workspaces.Workspace This is the result of the cmdlet Get-PowerBIWorkspace .PARAMETER PbiConnection The connection to the Power BI Tenant. .PARAMETER Path Path to the PBI Administration folder. The assessment will be stored in this folder. Please use the same folder for the backup. .PARAMETER Mode The assessment mode defines how much information you get from the assessment. To use "full" mode you first need to do a backup with our backup functions Backup-BsgPbiTenant or Backup-BsgPbiWorkspace. .EXAMPLE # Assessment (basic) $Path = "C:\temp\BSG PBI Administration" Get-PowerBIWorkspace -All | Get-bsgPbiWorkspaceAssessment -Path $Path .EXAMPLE # Assessment (full) $Path = "C:\temp\BSG PBI Administration" Get-PowerBIWorkspace -All | Get-bsgPbiWorkspaceAssessment -Path $Path -Mode "Full" .EXAMPLE # Assessment (full) for 2 specific workspaces $Path = "C:\temp\BSG PBI Administration" Get-PowerBIWorkspace -All | where-Object {$_.name -in ("BSGroup DA - Test Workspace", "BSgroup DA")} | Get-bsgPbiWorkspaceAssessment -Path $Path -PbiConnection $Pbilogin -Mode "Full" .INPUTS .OUTPUTS .NOTES This script uses the Power BI Management module for Windows PowerShell. If this module is not installed, install it by using the command 'Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser'. #> function Get-BsgPbiWorkspaceAssessment{ param ( [Parameter(Mandatory=$true)] [String]$Path, [Parameter(Mandatory=$false, ValueFromPipeline=$true)] [Microsoft.PowerBI.Common.Api.Workspaces.Workspace[]] $PbiWorkspace, [Parameter(Mandatory=$false)] [Microsoft.PowerBI.Common.Abstractions.Interfaces.IPowerBIProfile] $PbiConnection = $null, [Parameter(Mandatory=$false)] [ValidateSet("Basic", "Full")] [String[]]$Mode = "Basic" ) BEGIN { $Filename_Assessment = "Powerbi_Assessment_" + $Mode + "_" + (Get-Date -format "yyyyMMdd_hhmmss") + ".xlsx" $Path_Assessment = Join-Path -Path $Path -ChildPath "Assessment" $Path_AssessmentFile = Join-Path -Path $Path_Assessment -ChildPath $Filename_Assessment $Path_Backup = Join-Path -Path $Path -ChildPath "Backup" if ($Mode -eq "Full"){ if ((Test-Path $Path_Backup) -eq $false){ throw "The Backup Subfolder does not exist. It means, it doesn't have a full backup in the location and it is necessary to generate a Full assessment." } } Write-Host Write-Host Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------" Write-Host Write-PSFHostColor -Level Host -DefaultColor green -String (" Power BI Service assessment (" + $Mode + ") started... ") Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String " Location: <c='white'>$Path_Assessment</c>" Write-Host Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------" Write-Host Write-Host $assessmentWorkspace = @() $assessmentWorkspaceUsers = @() $assessmentDatasets = @() $assessmentDatasources = @() $assessmentDataflows = @() $assessmentDataflowsources = @() $assessmentReports = @() $assessmentDashboards = @() $assessmentDashboardTiles = @() # === # Get tenant level information # = if ($Mode -eq "Full"){ $orphanDatasets = Get-BsgPbiOrphanDatasets -Path $Path -SkipConfirmationMessage $true $DatasetsWithoutOwner = Get-BsgPbiDatasetsWithoutOwner -Path $Path -SkipConfirmationMessage $true $AllDatasetReport_Mappings = Get-BsgPbiAllMappingFiles -Path $Path } else { $orphanDatasets = @() $DatasetsWithoutOwner = @() $AllDatasetReport_Mappings = @() } $AllWorkspaces = Get-PowerBIWorkspace -All -Scope Organization $NumOfOrphanDatasets = 0 $NumOfDatasetsWithoutOwner = 0 $NumOfReportsSkipped = 0 $NumOfWorkspacesWithSameName = 0 Write-PSFHostColor -Level Host -DefaultColor white -String "Getting information for each workspace..." } PROCESS { foreach ($pbiws in $PbiWorkspace){ try { Write-PSFHostColor -Level Host -DefaultColor gray -String ("- " + $pbiws.Name) $WorkspaceAction = "" $WorkspaceProblem = "" $DuplicateWorkspaces = $AllWorkspaces | Where-Object {$_.name -eq $pbiws.name} if (@($DuplicateWorkspaces).length -gt 1){ $NumOfWorkspacesWithSameName += 1 $WorkspaceProblem = 'duplicate name' $WorkspaceAction = 'rename workspace' } $pbiwsEncriptationStatus = Get-PowerBIWorkspaceEncryptionStatus -Id $pbiws.Id -ErrorAction SilentlyContinue if (!$pbiwsEncriptationStatus){ $pbiwsEncriptationStatus = 'Unauthorized' } $assWorkspace = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name Description = $pbiws.Description Problem = $WorkspaceProblem Action = $WorkspaceAction Type = $pbiws.Type State = $pbiws.State IsReadOnly = $pbiws.IsReadOnly IsOnDedicatedCapacity = $pbiws.IsOnDedicatedCapacity CapacityId = $pbiws.CapacityId EncriptationStatus = $pbiwsEncriptationStatus } $assessmentWorkspace = $assessmentWorkspace + $assWorkspace $wsusers = Invoke-PowerBIRestMethod -Url "groups/$($pbiws.Id)/users" -Method Get | ConvertFrom-Json foreach ($u in $wsusers){ foreach ($uu in $u.value){ $assUser = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name emailAddress = $uu.emailAddress groupUserAccessRight = $uu.groupUserAccessRight } $assessmentWorkspaceUsers = $assessmentWorkspaceUsers + $assUser } } $wsreports = Get-PowerBIReport -WorkspaceId $pbiws.Id foreach($rep in $wsreports){ # restore action $restoreAction = '' $restoreDescription = '' if ($Mode -eq "Full"){ if ($rep.Id -in $AllDatasetReport_Mappings.reportId){ $ReportOfBackup = $AllDatasetReport_Mappings | Where-Object {$_.reportId -eq $rep.Id} $restoreAction = $ReportOfBackup.restoreAction $restoreDescription = $ReportOfBackup.restoreDescription } } else{ $restoreAction = 'unknown' $restoreDescription = 'Only available in full assessment mode.' } # set assessment level variables if ($restoreAction -eq 'Skip'){ $NumOfReportsSkipped += 1 } $assReport = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name ReportId = $rep.Id ReportName = $rep.Name restoreAction = $restoreAction restoreDescription = $restoreDescription DatasetId = $rep.DatasetId WebUrl = $rep.WebUrl EmbedUrl = $rep.EmbedUrl } $assessmentReports = $assessmentReports + $assReport } # $wsreportdatasets = $wsreports | ForEach-Object{ $_.DatasetId} | Select-Object -Unique $wsdatasets = Get-PowerBIDataset -WorkspaceId $pbiws.Id foreach($ds in $wsdatasets){ $usage = 'unknown' $comment = 'Only available in full assessment mode.' if ($Mode -eq "Full"){ $usage = 'is used' $comment = 'At least 1 report is bound to the dataset.' if ($ds.Id -in $orphanDatasets.id){ $DatasetIsOrphan = $true $DatasetHasNoOwner = $true $usage = 'is orphan' $comment = 'Not used by any report.' } else{ $DatasetIsOrphan = $false if ($ds.Id -in $DatasetsWithoutOwner.id){ $DatasetHasNoOwner = $true $usage = 'has no owner' $comment = 'No report is directly bound to the dataset.' }else{ $DatasetHasNoOwner = $false } } } # set assessment level variables if ($DatasetIsOrphan -eq $true){ $NumOfOrphanDatasets += 1 } if ($DatasetHasNoOwner -eq $true){ $NumOfDatasetsWithoutOwner += 1 } $assDataset = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name DatasetId = $ds.Id Dataset = $ds.Name Usage = $usage Comment = $comment WebUrl = $ds.WebUrl ConfiguredBy = $ds.ConfiguredBy DefaultRetentionPolicy = $ds.DefaultRetentionPolicy AddRowsApiEnabled = $ds.AddRowsApiEnabled DefaultMode = $ds.DefaultMode Datasources = $ds.Datasources IsRefreshable = $ds.IsRefreshable IsEffectiveIdentityRequired = $ds.IsEffectiveIdentityRequired IsEffectiveIdentityRolesRequired = $ds.IsEffectiveIdentityRolesRequired IsOnPremGatewayRequired = $ds.IsOnPremGatewayRequired TargetStorageMode = $ds.TargetStorageMode ActualStorage = $ds.ActualStorage } $assessmentDatasets = $assessmentDatasets + $assDataset $wsdatasources = Get-PowerBIDatasource -DatasetId $ds.Id -WorkspaceId $pbiws.Id foreach($dsrc in $wsdatasources){ $assDatasource = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name DatasetId = $ds.Id Dataset = $ds.Name DatasourceId = $dsrc.DatasourceId Datasource = $dsrc.Name ConnectionString = $dsrc.ConnectionString DatasourceType = $dsrc.DatasourceType GatewayId = $dsrc.GatewayId DetailServer = $dsrc.ConnectionDetails.Server DetailDatabase = $dsrc.ConnectionDetails.Database DetailUrl = $dsrc.ConnectionDetails.Url } $assessmentDatasources = $assessmentDatasources + $assDatasource } } $wsdataflows = Get-PowerBIDataflow -WorkspaceId $pbiws.Id foreach($df in $wsdataflows){ $assDataflow = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name DataflowId = $df.Id Dataflow = $df.Name ConfiguredBy = $df.ConfiguredBy Description = $df.Description ModelUrl = $df.ModelUrl } $assessmentDataflows = $assessmentDataflows + $assDataflow $wsdataflowsources = Get-PowerBIDataflowDatasource -DataflowId $df.Id -WorkspaceId $pbiws.Id $assessmentDataflowsources = $assessmentDataflowsources + $wsdataflowsources } $wsdashboards = Get-PowerBIDashboard -WorkspaceId $pbiws.Id foreach($db in $wsdashboards){ $assDashboard = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name DashboardId = $db.Id DashboardName = $db.Name IsReadOnly = $db.IsReadOnly EmbedUrl = $db.EmbedUrl } $assessmentDashboards = $assessmentDashboards + $assDashboard $wsdashboardtiles = Get-PowerBITile -WorkspaceId $pbiws.Id -DashboardId $db.Id foreach($tl in $wsdashboardtiles){ $assDashboardTile = [PSCustomObject]@{ WorkspaceId = $pbiws.Id Workspace = $pbiws.Name DashboardId = $db.Id DashboardName = $db.Name DashboardTileId = $tl.Id DashboardTileName = $tl.Name RowSpan = $tl.RowSpan ColumnSpan = $tl.ColumnSpan ReportId = $tl.ReportId DatasetId = $tl.DatasetId EmbedUrl = $tl.EmbedUrl EmbedData = $tl.EmbedData } $assessmentDashboardTiles = $assessmentDashboardTiles + $assDashboardTile } } } catch { Write-Host Stop-PSFFunction -Message ("Could not export workspace " + $pbiws.Name) -EnableException $False -Errorrecord $_ return } } } END { # === # Generate excel sheets # = $wsEncriptationKey = Get-PowerBIEncryptionKey -ErrorAction SilentlyContinue if (!$PbiWorkspaceEncriptationStatus){ $wsEncriptationKey = 'Unauthorized' } if ($null -ne $Path_AssessmentFile -and "" -ne $Path_AssessmentFile){ # Introduction Sheet $title = "Power BI Assessment" if ($Mode -eq "Full"){ $AssessmentMode = "Full" } else{ $AssessmentMode = "Basic" } $intro = ( [PSCustomObject]@{ A = "" B = "" }, [PSCustomObject]@{ A = "Assessment Mode" B = $AssessmentMode }, [PSCustomObject]@{ A = "Creation Date" B = Get-Date | Out-String }, [PSCustomObject]@{ A = "Encriptation Key" B = $wsEncriptationKey } ) if ($null -ne $PbiConnection){ $intro = $intro + ( [PSCustomObject]@{ A = "" B = "" }, [PSCustomObject]@{ A = "Environment" B = $PbiConnection.Environment }, [PSCustomObject]@{ A = "Tennant Id" B = $PbiConnection.TenantId }, [PSCustomObject]@{ A = "Username" B = $PbiConnection.UserName }, [PSCustomObject]@{ A = "Login type" B = $PbiConnection.LoginType } ) } $intro | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Introduction" -AutoSize -NoHeader -StartRow 2 -Title $title -TitleSize 20 # $ws = $excelpkg.Workbook.Worksheets['Introduction'] # Write-Host $ws # $xlParams = @{WorkSheet=$ws;Bold=$true;FontSize=18} # Set-Format -Range A4 -WorkSheet $ws -Bold #@xlParams # Workspace Sheet $ConditionalFormat =$( New-ConditionalText -Text "duplicate name" -Range "D:D" -BackgroundColor Red -ConditionalTextColor White ) $assessmentWorkspace | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Workspaces" -TableName "Workspaces" -AutoSize -TableStyle Light8 -ConditionalFormat $ConditionalFormat $assessmentWorkspaceUsers | Export-Excel -path $Path_AssessmentFile -WorkSheetname "WorkspaceUsers" -TableName "WorkspaceUsers" -AutoSize -TableStyle Light8 # Dataset Sheet $ConditionalFormat =$( New-ConditionalText -Text "is orphan" -Range "E:E" -BackgroundColor Red -ConditionalTextColor White New-ConditionalText -Text "has no owner" -Range "E:E" -BackgroundColor Red -ConditionalTextColor White New-ConditionalText -Text "unknown" -Range "E:E" -BackgroundColor Orange -ConditionalTextColor White New-ConditionalText -Text 'is used' -Range "E:E" -BackgroundColor Green -ConditionalTextColor White ) $assessmentDatasets | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Datasets" -TableName "Datasets" -ConditionalFormat $ConditionalFormat -AutoSize -TableStyle Light8 # Datasource Sheet $assessmentDatasources | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Datasources" -TableName "Datasources" -AutoSize -TableStyle Light8 # Dataflow Sheet $assessmentDataflows | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Dataflows" -TableName "Dataflows" -AutoSize -TableStyle Light8 # Dataflowsources Sheet $assessmentDataflowsources | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Dataflowsources" -TableName "Dataflowsources" -AutoSize -TableStyle Light8 # Report Sheet $ConditionalFormat =$( New-ConditionalText -Text "Skip_UsageMetrics" -Range "E:E" -BackgroundColor Orange -ConditionalTextColor White New-ConditionalText -Text "Skip" -Range "E:E" -BackgroundColor Red -ConditionalTextColor White New-ConditionalText -Text "CreateDataset" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White New-ConditionalText -Text "RebindReport" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White New-ConditionalText -Text "UpdatePbiConnection" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White New-ConditionalText -Text "Wait_UpdatePbiConnection" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White New-ConditionalText -Text "CreateDataset_RebindReport" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White New-ConditionalText -Text "unknown" -Range "E:E" -BackgroundColor Orange -ConditionalTextColor White ) $assessmentReports | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Reports" -TableName "Reports" -ConditionalFormat $ConditionalFormat -AutoSize -TableStyle Light8 # Dashboard Sheet $assessmentDashboards | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Dashboards" -TableName "Dashboards" -AutoSize -TableStyle Light8 # Dashboard Tiles Sheet $excelpkg = $assessmentDashboardTiles | Export-Excel -path $Path_AssessmentFile -WorkSheetname "DashboardTiles" -TableName "DashboardTiles" -AutoSize -TableStyle Light8 -PassThru # Warnings $NumOfDatasetsWithoutOwner = $NumOfDatasetsWithoutOwner - $NumOfOrphanDatasets if ($BackupExists -eq $false){ Write-Host Write-Warning "Not all information found." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: In assessment basic mode, not all information can be found." Write-PSFHostColor -Level Host -DefaultColor gray -String "Some information (f.e. orphan datasets or reports which cannot be restored) can only be found after you export each object." Write-PSFHostColor -Level Host -DefaultColor gray -String "To include the information please run <c='white'>Backup-BsgPbiTenant</c>... or <c='white'>Backup-BsgPbiWorkspace</c>... first." Write-PSFHostColor -Level Host -DefaultColor gray -String "After the backup is done, run the assessment again with <c='white'>-Mode `"Full`"</c>" } if ($NumOfWorkspacesWithSameName -eq 1){ Write-Host Write-Warning "$NumOfWorkspacesWithSameName workspace has a duplicate name. The workspace will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>workspaces marked as `"duplicate name`"</c> in the excel file." Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore the workspace, you need to <c='white'>rename the workspace</c> in PBI Service." } if ($NumOfWorkspacesWithSameName -gt 1){ Write-Host Write-Warning "$NumOfWorkspacesWithSameName workspaces have a duplicate name. They will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>workspaces marked as `"duplicate name`"</c> in the excel file." Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore those workspaces, you need to <c='white'>rename each workspaces</c> in PBI Service." } if ($NumOfOrphanDatasets -eq 1){ Write-Host Write-Warning "$NumOfOrphanDatasets orphan dataset (dataset is not used by any report). It will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>dataset marked as `"is orphan`"</c> in the excel file." Write-PSFHostColor -Level Host -DefaultColor gray -String "No more used datasets should be deleted before starting the restoration." Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore this dataset, you need to <c='white'>save the dataset as a report</c> in PBI Service." } elseif ($NumOfOrphanDatasets -gt 1){ Write-Host Write-Warning "$NumOfOrphanDatasets orphan datasets (datasets are not used by any report). They will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>datasets marked as `"is orphan`"</c> in the excel file." Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore those datasets, you need to <c='white'>save each dataset as a report</c> in PBI Service." } if ($NumOfDatasetsWithoutOwner -eq 1){ Write-Host Write-Warning "$NumOfDatasetsWithoutOwner dataset without owner (dataset has no report bound to it). It will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>dataset marked as `"has no owner`"</c> in the excel file." Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore this dataset, you need to <c='white'>save the dataset as a report</c> in PBI Service." } elseif ($NumOfDatasetsWithoutOwner -gt 1){ Write-Host Write-Warning "$NumOfDatasetsWithoutOwner datasets without owner (datasets have no report bound to them). They will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>datasets marked as `"has no owner`"</c> in the excel file." Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore those datasets, you need to <c='white'>save each dataset as a report</c> in PBI Service." } if ($NumOfReportsSkipped -eq 1){ Write-Host Write-Warning "$NumOfReportsSkipped report cannot be exported. It will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>report marked as `"Skip`"</c> in the excel file." } elseif ($NumOfReportsSkipped -gt 1){ Write-Host Write-Warning "$NumOfReportsSkipped reports cannot be exported. They will not be restored." Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>reports marked as `"Skip`"</c> in the excel file." } Write-Host Write-PSFHostColor -Level Host -DefaultColor white -String "Opening excel file..." Close-ExcelPackage -ExcelPackage $excelpkg -Show Write-Host Write-Host Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------" Write-Host if ($Mode -eq "Full"){ Write-PSFHostColor -Level Host -DefaultColor green -String " Power BI Service assessment (full) finished." } else{ Write-PSFHostColor -Level Host -DefaultColor green -String " Power BI Service assessment (basic) finished." } Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String " Location: <c='white'>$Path_AssessmentFile</c>" Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String " Developed by <c='white'>BSGroup Data Analytics AG</c>" Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------" Write-Host Write-Host }else { $assessmentWorkspace } } } |