Power BI Artifacts Per Workspace

In response to last week’s post regarding the retrieval of the artifacts (reports, dashboards, datasets) within a premium capacity via the Power BI PowerShell cmdlets, a follow up question from a reader was (essentially) “Great….but how can I see the artifacts per workspace?”

The standard Get-PowerBIReport, Get-PowerBIDashboard, and Get-PowerBIDataset cmdlets unfortunately do not include the workspace for these artifacts, which is of course one of the most important attributes. Therefore, you may find the script referenced in this post or some variation of your own choosing to be useful.

PBI Artifact per Workspace Script

The following PowerShell script available on my GitHub repository retrieves the datasets, reports, and dashboards (including their workspaces) for a given Power BI Premium capacity and then exports these custom objects to their own CSV files:

#1. Authenticate to Power BI Service with credential of Power BI Service Admin

$User = "abcdefg@bigcompany.com"
$PW = "yourpassword"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

#2. Required Variables
$CapacityWorkspace = "Name of Workspace"
$CapacityName = "My Capacity"
$ExportPath = "C:\Users\Brett Powell\Desktop\Power BI Artifacts per Workspace\"
$DateRetrieved = Get-Date

#3. Retrieve Capacity ID and workspaces in the premium capacity for the given workspace
$CapacityID = Get-PowerBIWorkspace -Scope Organization -Name $CapacityWorkspace | ForEach {$_.CapacityId}

$CapWorkspaces = Get-PowerBIWorkspace -Scope Organization -All | Where {$_.CapacityId -eq $CapacityID -and $_.State -eq "Active"}

#4. Get distinct list of datasets, reports, and dashboard objects per workspace of capacity workspaces from Step #3 
$CapacityDatasets = ForEach ($Workspace in $CapWorkspaces) 
    {
       $WSID = $Workspace.Id
       $WSName = $Workspace.Name
        Get-PowerBIDataset -Scope Organization -WorkspaceId $WSID | `
        Select *,@{Name="Workspace";Expression={$WSName}}, @{Name="Date"; Expression={$DateRetrieved}}, `
        @{Name="Capacity";Expression={$CapacityName}} | `
        Select Name,Workspace,Capacity,Date | Sort-Object -Property Name | Get-Unique -AsString
    }
   
$CapacityReports = ForEach ($Workspace in $CapWorkspaces)
   {
       $WSID = $Workspace.Id
       $WSName = $Workspace.Name
        Get-PowerBIReport -Scope Organization -WorkspaceId $WSID | `
        Select *,@{Name="Workspace";Expression={$WSName}}, @{Name="Date"; Expression={$DateRetrieved}}, `
        @{Name="Capacity";Expression={$CapacityName}} | `
        Select Name,Workspace,Capacity,Date | Sort-Object -Property Name | Get-Unique -AsString
    }

$CapacityDashboards = ForEach ($Workspace in $CapWorkspaces)
   {
       $WSID = $Workspace.Id
       $WSName = $Workspace.Name
        Get-PowerBIDashboard -Scope Organization -WorkspaceId $WSID | `
        Select *,@{Name="Workspace";Expression={$WSName}}, @{Name="Date"; Expression={$DateRetrieved}}, `
        @{Name="Capacity";Expression={$CapacityName}} | `
        Select Name,Workspace,Capacity,Date | Sort-Object -Property Name | Get-Unique -AsString
    }

#5. Export custom objects from Step #4 to CSV files

$DashboardPath = $ExportPath + "Dashboards.csv"
$ReportPath = $ExportPath + "Reports.csv"
$DatasetsPath = $ExportPath + "Datasets.csv"

$CapacityDashboards | Export-Csv -Path $DashboardPath
$CapacityReports | Export-Csv -Path $ReportPath
$CapacityDatasets | Export-Csv -Path $DatasetsPath

Per the embedded comments, the script following the following 5-step process:

1. Authenticate to Power BI Service
   • As mentioned before, if you’re just going to run this for ad hoc scenarios, you can just Connect-PowerBIServiceAccount.
2. Required Variables
   • In this example you provide the name of a workspace contained within a premium capacity, the directory path to export the files to, and the name of a premium capacity.
   • Please note that you could modify this script to work with a different set of app workspaces – they don’t have to be confined to a particular premium capacity.
     • Just remember that running this against a very large set of workspaces (like all workspaces in your tenant (including personal workspaces)) could take a very long time to execute.
3. Retrieve set of workspaces
   • This step uses the variables from Step #2 to build a PowerShell object containing individual workspaces
   • This set of workspaces serves as the source for the datasets, reports, and dashboards retrieved in the next step
4. Retrieve datasets, reports, and dashboards per workspace
   • This step loops through the workspaces from the custom object in Step #3 and, in doing so, adds columns which reference the workspace for the given artifact
   • This step also limits the columns exported to just four columns (name of the artifact, name of the workspace, name of the capacity, and date retrieved).
     • You can of course modify this to retrieve more of the available columns (e.g. IDs) – I just wanted these tables to be as simple and intuitive as possible to work with.
   • Moreover, this step removes duplicate artifacts that would be present if apps have been published from the workspace via the Sort-Object and Get-Unique cmdlets.
5. Export the distinct artifacts as CSV files
   • Finally, like several past examples, the custom objects created in Step #4 are piped to the Export-Csv cmdlet which exports this detail to a directory path for further analysis.

Wrapping Up

So if you’re just looking for a simple table format containing the artifacts per workspace, this script (or some related variation) may help you out.

As always thanks for visiting this blog and feel welcome to share any thoughts or concerns you may have in comments or emails.