Premium Capacity Contents

Earlier today someone from one of the largest companies in the world asked “Is there any script for counting reports and dashboards in each workspace for the entire (premium) capacity?”

The answer is yes, the script in this blog (available on GitHub) delivers this information as well as the count of datasets and the details of each artifact. Per the following two images, you can quickly view a summary level table from within the PowerShell terminal window and you can also view/analyze the exported artifact details as CSV files.

In the example above, a Power BI Service Administrator observes (from the PowerShell terminal window) that the Finance P1 premium capacity has 30 reports, 9 workspaces, 13 dashboards, and 17 datasets. Maybe this is sufficient information but in many scenarios the admin will need to see the names of the reports and dashboards, the user that configured the datasets, and more details. For this detail, per the second image above, the script exports the artifact details to individual CSV files.

Why…and then how

Why would someone, typically a Power BI Service Administrator, be interested in a script that retrieves this information? Here are a couple reasons:

1. To obtain a quick, high level view of how a capacity is being used.
   • The Capacity Settings page in the Power BI Admin portal provides the number of workspaces assigned to the capacity but not the artifacts contained with the workspaces.
   • Similarly, the Power BI Premium Metrics app provides the workspaces and datasets within a premium capacity but it doesn’t tell you which reports and dashboards are generating the queries against these datasets.
2. To understand the distribution of artifacts within the capacity.
   • Are dashboards being used (at all) to help summarize and link to multiple reports?
   • Are datasets being re-used (at all) by multiple reports via live connection or does each report essentially have its own dataset?
     • Obviously this has significant implications for resource utilization, version control, and overall data governance.
   • I typically find that dashboards are underutilized, if used at all, and that teams/users don’t fully grasp the distinction between datasets and reports and thus create duplicate datasets.

Premium Capacity Contents Script

Per the embedded comments, the following script contains X steps:

1. Authenticate to Power BI (as a Power BI Service Admin)
   • This example uses the PowerShell credential object for remote authentication like recent past examples.
   • However, if you only intend to run this script ad hoc or as necessary, you can leave out this syntax and just use the Connect-PowerBIServiceAccount cmdlet.
2. Provide a name for the premium capacity and capture the current date/time
   • This doesn’t have to be the actual name of the premium capacity for the script to work but it probably should be.
   • Like the current date, the capacity name is just additional metadata to give the data more meaning if/when you need to refer back to it.
3. Retrieve the Premium Capacity ID
   • This script uses the name of an app workspace (Finance Quarterly Review), which is assigned to the Finance P1 capacity, to capture the ID of this premium capacity.
   • You could use the Capacity Settings page in the Power BI Admin portal to find a workspace assigned to the capacity you want to analyze. Alternatively, the workspace settings pane identifies the capacity (if any) to which the workspace is assigned.
   • As yet another alternative, you could use the Get-PowerBIWorkspace to find the capacity ID for a workspace without leaving the PowerShell window.
     • Either way, it should be very easy to find the name of a workspace assigned to the capacity.
4. Retrieve all the active app workspaces for the premium capacity
   • This object will serve as the source for capturing the artifacts and there counts.
5. Retrieve the artifacts (dashboards, datasets, reports)
   • The ForEach-Object will iterate or walk through each app workspace for the given premium capacity and get the artifacts via the Power BI Management Module’s GET cmdlets corresponding to each artifact type
6. Create a PowerShell object for the summary view
   • A PowerShell object with six members is created to support the summary table (with artifact counts) displayed in the slideshow above.
   • The counts of the variables corresponding to the artifacts from the prior step are referenced as the value for the members, along with the metadata variables for date retrieved and capacity name.
7. Optionally, export the summary and artifact details as CSV files
   • In the event you see something that’s interesting from the summary table such as a significantly higher number of reports than you expected, you can just open the reports file.

Again, the PowerShell (.ps1) file is available for download from GitHub.

#1. Authenticate to Power BI Service with Power BI Service Administrator credential

$User = "powerbiadmin@bigcompany.com"
$PW = "pbiadminpassword"

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

Connect-PowerBIServiceAccount -Credential $myCred

#2. Provide the name of the premium capacity and capture the current date

$CapacityName = "Finance P1"
$RetrieveDate = Get-Date

#3. Retrieve the ID of a premium capacity into a variable based on the name of a workspace which is hosted on the premium capacity

$WorkspaceName = "Finance Quarterly Review"

$CapacityId = Get-PowerBIWorkspace -Scope Organization -Name $WorkspaceName | ForEach {$_.CapacityId}

#4. Retrieve the active app workspaces for the premium capacity identified in step #3.

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

#5. Retrieve the dashboards, reports, and datasets contained within the app workspaces of the premium capacity identified in step #4. 

$CapacityDashboards = $CapacityWorkspaces | ForEach {$WSID = $_.Id; Get-PowerBIDashboard -Scope Organization -WorkspaceId $WSID}

$CapacityReports = $CapacityWorkspaces | ForEach {$WSID = $_.Id; Get-PowerBIReport -Scope Organization -WorkspaceId $WSID}

$CapacityDatasets = $CapacityWorkspaces | ForEach {$WSID = $_.Id; Get-PowerBIDataset -Scope Organization -WorkspaceId $WSID}

#6. Create an object for capacity artifact count values

$CapacityCountObject = New-Object -TypeName psobject

$CapacityCountObject | Add-Member -MemberType NoteProperty -Name Capacity -Value $CapacityName
$CapacityCountObject | Add-Member -MemberType NoteProperty -Name Workspaces -Value $CapacityWorkspaces.Count
$CapacityCountObject | Add-Member -MemberType NoteProperty -Name Dashboards -Value $CapacityDashboards.Count
$CapacityCountObject | Add-Member -MemberType NoteProperty -Name Reports -Value $CapacityReports.Count
$CapacityCountObject | Add-Member -MemberType NoteProperty -Name Datasets -Value $CapacityDatasets.Count
$CapacityCountObject | Add-Member -MemberType NoteProperty -Name Date -Value $RetrieveDate

#View the object created in this step as a table within the PowerShell terminal window
$CapacityCountObject | Format-Table -AutoSize

#7. Optionally export the capacity content details as CSV files

#Define directory paths for export files
$BasePath = "C:\Users\Brett Powell\Desktop\CapacityContent"

$SummaryObjectPath = $BasePath + "\Capacity Summary.csv"
$CapWorkspacesPath = $BasePath + "\Capacity Workspaces.csv"
$CapDatasetsPath = $BasePath + "\Capacity Datasets.csv"
$CapDashboardsPath = $BasePath + "\Capacity Dashboards.csv"
$CapReportsPath =  $BasePath + "\Capacity Reports.csv"

#Export premium capacity artifact details to files

$CapacityCountObject |  Export-Csv $SummaryObjectPath
$CapacityWorkspaces | Select *, @{Name="Date Retrieved"; Expression={$RetrieveDate}} | Export-Csv $CapWorkspacesPath
$CapacityDatasets | Select *, @{Name="Date Retrieved"; Expression={$RetrieveDate}} | Export-Csv $CapDatasetsPath
$CapacityDashboards | Select *, @{Name="Date Retrieved"; Expression={$RetrieveDate}} | Export-Csv $CapDashboardsPath
$CapacityReports | Select *, @{Name="Date Retrieved"; Expression={$RetrieveDate}} | Export-Csv $CapReportsPath

As is often the case, the script isn’t complex when you think and walk through the process. However, a PowerShell script which is significantly more complex, though also much more powerful and potentially valuable for actively managing and streamlining enterprise Power BI deployments could be coming soon.

Wrapping Up

Thanks for visiting this blog and I hope you find this post/example and others useful. Feel welcome to subscribe (‘Follow’) or share any questions or comments you have.

Also, if you’re going to be in the Los Angeles area this summer I’ll be speaking about Power BI Administration solutions at the SQL Saturday event there on June 15th.