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.

12 comments

      1. Are you familiar with the get-azureadgroup Azure Active Directory powershell commandlets? I am trying to get a list of Azure AD groups that contains PowerBI in the name no matter where in the group name it is, but I can’t find a similar way to do a contains or like operator.

        Like

      2. Hi Roger,

        Yes, I shared an example script for exporting Azure AD data in the Power BI Admin Datasets posts. This particular script doesn’t filter for specific groups but you should be able to either A) pipe the variable to a Where-Object and implement the ‘like’ filter there or B) implement the filtering logic via Power Query (M) expressions when loading a Power BI dataset or in a Power BI dataflow. In some of my project admin scripts I conditionally apply a team name to the workspace based on the existence of a certain acronym referring to the team such as RM for the Revenue Management team. I’m not sure if these examples would make for a good blog post..I’ll think about it.

        Like

  1. Thx for the suggestion, but I get no results when I do this: Get-AzureADGroup | Where-Object {$_.DisplayName -like “PowerBI”}

    Like

      1. I appreciate it, this works much better, but it only returns group names that start with PowerBI. I have group names that have PowerBI in the middle of the name. Any ideas?

        Like

  2. Hi Brett, can you please help me modify this portion of the code to exclude report names that equal Dashboard Usage Metrics Report AND Report Usage Metrics Report?

    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

    Like

    1. Try this (see the Where-object to filter out usage metrics names):

      Get-PowerBIReport -Scope Organization -WorkspaceId $WSID | Where {$_.Name -ne “Dashboard Usage Metrics Report” -and $_.Name -ne “Report Usage Metrics Report”} | `
      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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s