Taking Inventory of Power BI Premium Workspaces

mark fennellAzure, PowerShell

While Microsoft’s Power BI Premium Capacity Metrics app provides a wealth of information on the health of your Power BI Premium capacity datasets, queries and refreshes, one feature that I’ve found missing is just a list of reports hosted in premium workspaces. The following PowerShell script generates a list of workspace names and the reports and datasets in each workspace.

Sample Output: 

1 Executive Reports 
....Reports
........ Executive Daily Report
....Datasets
........ Executive Daily Report
............DataSource:  AnalysisServices
............Connection Info: asazure://southcentralus.asazure.windows.net/aasprod : SalesModel 
2 Orders 
....Reports
........ Enterprise Order Tracker
........ New Product Orders
....Datasets
........ Enterprise Order Tracker
............DataSource:  Sql
............Connection Info:  SQLServer.MyCompany.com : dbName 
........ New Product Orders
............DataSource:  Web
............Connection Info: https://myCompany.sharepoint.com/sites/Finance/Shared%20Documents/Operations%20&%20Support/Power%20BI/New%20Product%20Orders.xlsx

To run this script for a premium capacity, login with the account of a capacity admin. This will allow the script access to the organization scope.

Login-PowerBI

$workspaces = Get-PowerBIWorkspace -Scope Organization
$n = 0
Write-Host "Premium Groups"
ForEach($ws in $workspaces)
    {
        if($ws.IsOnDedicatedCapacity -eq $true)
            {    $n=$n+1
                Write-Host $n $ws.Name $ws.Description
                    $reports = Get-PowerBIReport -Scope Organization -WorkspaceId $ws.Id
                    Write-Host "....Reports" 
                    ForEach($report in $reports){
                        Write-Host "........" $report.Name
                    }
                    Write-Host "....Datasets"
                    $datasets = Get-PowerBIDataset -Scope Organization -WorkspaceId $ws.Id
                        ForEach($dataset in $datasets){
                        Write-Host "........" $dataset.Name
                        $datasources = Get-PowerBIDatasource -Scope Organization -DatasetID $dataset.Id
                            ForEach($datasource in $datasources){
                            Write-Host "............DataSource: " $datasource.DatasourceType
                            Write-Host "............Connection Info: " `
                                $datasource.ConnectionDetails.Server `
                                ":"$datasource.ConnectionDetails.Database `
                                $datasource.ConnectionDetails.URL
                            }
                    }
            }
    }

Leave a Reply