Functions/Get-BsgPbiDatasetsWithoutOwner.ps1
|
<#
.SYNOPSIS Find all Power BI datasets in your backup which have no report bound to it but reports using a live connection. .DESCRIPTION Get Power BI datasets in an existing backup which have no report bound to it. They can have reports with live connection connected to them. To download or export this datasets, you need to save the dataset as a report which includes the dataset ("save as" in PBI Service). Navigate to the dataset in the PBI Service portal and use the "save as" function to create a report holding the dataset. Warning: Before you execute the function, please first backup the tenant with "Backup-BsgPbiTenant...". We need to export the PBIX files to know if there are any live connections associated with the dataset. .PARAMETER Path The path to the folder where the tenant is backed up. .PARAMETER PrintOutput Print a list of datasets without owner grouped by workspace name. .PARAMETER ShowConfirmationMessage Show a confirmation message to ensure everything is ready. .EXAMPLE # Get datasets withotu owners $DatasetsWithoutOwner = Get-BsgPbiDatasetsWithoutOwner -Path "C:\temp\BSG PBI Administration" # Get and print datasets without owner $DatasetsWithoutOwner = Get-BsgPbiDatasetsWithoutOwner -Path "C:\temp\BSG PBI Administration" -PrintOutput $true .INPUTS .OUTPUTS Returns an array of PS objects with datasets, which have no report owner. .NOTES #> function Get-BsgPbiDatasetsWithoutOwner{ param ( [Parameter(Mandatory=$true)][string]$Path, [Parameter(Mandatory=$false)][string]$PrintOutput = $false, [Parameter(Mandatory=$false)][string]$SkipConfirmationMessage = $false ) try{ # Confirm backup is finished if ($SkipConfirmationMessage -eq $false){ Write-PSFHostColor -Level Host -DefaultColor gray -String "Before you can find datasets without owners, you have to backup the tenant with `"Backup-BsgPbiTenant...`"." Write-PSFHostColor -Level Host -DefaultColor gray -String "Please clean all reports which are no longer used before you do the backup." Write-PSFHostColor -Level Host -DefaultColor white -String "Did you backup the tenant? [y/n]: " $confirmBackupFinished = Read-Host if ($confirmBackupFinished -eq 'y'){ # continue... } else{ Write-PSFHostColor -Level Host -DefaultColor gray -String "Process stopped. Please first backup your tenant." return } } # Define paths and filenames $Path_Backup = Join-Path -Path $Path -ChildPath "Backup" $Path_Workspaces = Join-Path -Path $Path_Backup -ChildPath "Workspaces" # === # Get all dataset from last backup # = $FileName_DatasetMetadata = "Datasets.json" $Source_Workspaces = Get-ChildItem -Path $Path_Workspaces -Name $AllDataset_Mappings = @() foreach ($Source_WorkspaceName in $Source_Workspaces) { # Define paths and filenames $Path_Workspace = Join-Path -Path $Path_Workspaces -ChildPath $Source_WorkspaceName $Path_Datasets = Join-Path -Path $Path_Workspace -ChildPath 'Datasets' $Path_DatasetMetadata = Join-Path $Path_Datasets -ChildPath $FileName_DatasetMetadata # create overall mapping if (Test-Path $Path_DatasetMetadata){ # get mapping file for workspace $Dataset_Mapping = Get-Content -Path $Path_DatasetMetadata | ConvertFrom-Json -ErrorAction Stop # Add each dataset to overall mapping foreach ($dataset in $Dataset_Mapping) { # Add workspace name $dataset | Add-Member -MemberType NoteProperty -Name "workspaceName" -Value $Source_WorkspaceName # add to mapping if ($dataset.name -ne 'Report Usage Metrics Model'){ $AllDataset_Mappings += $dataset } } } } # === # Get all report dataset mappings from last backup # = $AllDatasetReport_Mappings = Get-BsgPbiAllMappingFiles -Path $Path # === # Check which datasets have no associated report # = $ProcessedDatasets = @() $DatasetsWithoutOwner = @() $DatasetOwners = $AllDatasetReport_Mappings | Where-Object {$_.isDatasetOwner -eq $true} foreach ($dataset in $AllDataset_Mappings) { # skip already processed datasets if ($ProcessedDatasets.id -notcontains $dataset.id){ # save datasets without owner if ($DatasetOwners.datasetId -notcontains $dataset.id){ $DatasetsWithoutOwner += $dataset } # save processed datasets $ProcessedDatasets += $dataset } } # sort datasets by workspace name $DatasetsWithoutOwner = $DatasetsWithoutOwner | Sort-Object -Property workspaceName # print output if ($PrintOutput -eq $true){ foreach ($dataset in $DatasetsWithoutOwner){ if ($dataset.workspaceName -ne $LastWorkspace){ Write-Host Write-PSFHostColor -Level Host -DefaultColor gray -String $($dataset.workspaceName) } Write-PSFHostColor -Level Host -DefaultColor gray -String "- <c='white'>$($dataset.name)</c>" $LastWorkspace = $dataset.workspaceName } } return $DatasetsWithoutOwner } catch{ } } |