Automating Power BI Data Collection with PowerShell

Blog | December 1, 2023 | By Hari Ankem

Connecting to the Power BI Service

Data collection workspace

Reports Data collections

Data management is a crucial aspect of any business intelligence strategy. For organizations using Power BI, keeping track of capacities, workspaces, users, reports, dashboards, datasets, dataflows, and more can be a daunting task. But with the right PowerShell script, you can automate this data collection process, saving time and ensuring data accuracy.

In this blog post, we’ll walk through a comprehensive PowerShell script that interacts with the Power BI Service to gather crucial information about capacities, workspaces, users, reports, datasets, dashboards, tiles, and dataflows. We’ll explore each section of the script, to give you insight into how to utilize it for your organization.

Efficient Strategies for Data Automation

Seamless Integration with PowerShell

Enhancing Business Intelligence with Automated Data Collection

Writing Headers to Data Files

In our PowerShell script, it’s essential to prepare the data files for each data category before collecting and saving information. This step ensures that our data is well-organized and easy to work with. Let’s explore how the script writes headers to the data files for each category:

Capacities

For capacities, we create a CSV file named PowerBICapacityInfo.csv. The headers for this file include:

  • CapacityId: Unique identifier for the capacity.
  • CapacityName: The name of the capacity.
  • SKU: Stock Keeping Unit – a code representing the capacity type.
# Prepare the Capacity data file
$CapacityFileCsv = '.\PowerBICapacityInfo.csv'
If (Test-Path $CapacityFileCsv) {
    Remove-Item -Path $CapacityFileCsv
}
$DataLine = '"{0}","{1}","{2}"' -f `    
 'CapacityId', `
 'CapacityName', `
 'SKU'
$DataLine | Add-Content $CapacityFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Workspaces

To store workspace data, we use the file PowerBIWorkspaceInfo.csv. Its headers are:

  • WorkspaceId: The unique identifier for the workspace.
  • WorkspaceName: The name of the workspace.
  • Type: The type of the workspace.
  • State: The state of the workspace.
  • IsReadOnly: Indicates if the workspace is read-only.
  • IsOrphaned: Reflects whether the workspace is orphaned.
  • IsOnDedicatedCapacity: Shows if the workspace is on dedicated capacity.
  • CapacityId: Links the workspace to its associated capacity.
# Prepare the Workspace data file
$WorkspaceFileCsv = '.\PowerBIWorkspaceInfo.csv'
If (Test-Path $WorkspaceFileCsv) {
    Remove-Item -Path $WorkspaceFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f `
    'WorkspaceId', `
    'WorkspaceName', `
    'Type', `
    'State', `
    'IsReadOnly', `
    'IsOrphaned', `
    'IsOnDedicatedCapacity', `
    'CapacityId'
$DataLine | Add-Content $WorkspaceFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Users

User data is stored in PowerBIUserInfo.csv with the following headers:

  • WorkspaceId: The identifier of the workspace.
  • Identifier: Unique identifier for the user.
  • PrincipalType: The type of principal (e.g., user or group).
  • AccessRight: Describes the user’s access rights within the workspace.
# Prepare the User data file
$UserFileCsv = '.\PowerBIUserInfo.csv'
If (Test-Path $UserFileCsv) {
    Remove-Item -Path $UserFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f `
    'WorkspaceId', `
    'Identifier', `
    'PrincipalType', `
    'AccessRight'
$DataLine | Add-Content $UserFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Reports

Reports are saved in PowerBIReportInfo.csv, which contains these headers:

  • WorkspaceId: The identifier of the workspace.
  • ReportId: Unique identifier for the report.
  • ReportName: The name of the report.
  • DatasetId: Associates the report with its underlying dataset.
# Prepare the Report data file
$ReportFileCsv = '.\PowerBIReportInfo.csv'
If (Test-Path $ReportFileCsv) {
    Remove-Item -Path $ReportFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f `
    'WorkspaceId', `
    'ReportId', `
    'ReportName', `
    'DatasetId'
$DataLine | Add-Content $ReportFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Datasets

The dataset data is written to PowerBIDatasetInfo.csv with the following headers:

  • WorkspaceId: The identifier of the workspace.
  • DatasetId: Unique identifier for the dataset.
  • DatasetName: The name of the dataset.
  • ConfiguredBy: Shows who configured the dataset.
  • IsRefreshable: Indicates if the dataset is refreshable.
  • IsEffectiveIdentityRequired: Reflects whether effective identity is required.
  • IsEffectiveIdentityRolesRequired: Shows if effective identity roles are required.
  • IsOnPremGatewayRequired: Indicates if an on-premises gateway is needed.
# Prepare the Dataset data file
$DatasetFileCsv = '.\PowerBIDatasetInfo.csv'
If (Test-Path $DatasetFileCsv) {
    Remove-Item -Path $DatasetFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f `
    'WorkspaceId', `
    'DatasetId', `
    'DatasetName', `
    'ConfiguredBy', `
    'IsRefreshable', `
    'IsEffectiveIdentityRequired', `
    'IsEffectiveIdentityRolesRequired', `
    'IsOnPremGatewayRequired'
$DataLine | Add-Content $DatasetFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Dashboards

For dashboard data, we create PowerBIDashboardInfo.csv, which includes these headers:

  • WorkspaceId: The identifier of the workspace.
  • DashboardId: Unique identifier for the dashboard.
  • DashboardName: The name of the dashboard.
  • IsReadOnly: Indicates if the dashboard is read-only.
# Prepare the Dashboard data file
$DashboardFileCsv = '.\PowerBIDashboardInfo.csv'
If (Test-Path $DashboardFileCsv) {
    Remove-Item -Path $DashboardFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f `
    'WorkspaceId', `
    'DashboardId', `
    'DashboardName', `
    'IsReadOnly'
$DataLine | Add-Content $DashboardFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Tiles (within Dashboards)

Within the dashboard section, we also collect tile data. This is stored in PowerBITileInfo.csv, with the following headers:

  • WorkspaceId: The identifier of the workspace.
  • TileId: Unique identifier for the tile.
  • TileTitle: The title of the tile.
  • DashboardId: Links the tile to its parent dashboard.
  • ReportId: Associates the tile with its underlying report.
  • DatasetId: Links the tile to its dataset.
# Prepare the Tile data file
$TileFileCsv = '.\PowerBITileInfo.csv'
If (Test-Path $TileFileCsv) {
    Remove-Item -Path $TileFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}"' -f `
    'WorkspaceId', `
    'TileId', `
    'TileTitle', `
    'DashboardId', `
    'ReportId', `
    'DatasetId'
$DataLine | Add-Content $TileFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Dataflows

Finally, dataflow information is written to PowerBIDataflowInfo.csv with the headers:

  • WorkspaceId: The identifier of the workspace.
  • DataflowId: Unique identifier for the dataflow.
  • DataflowName: The name of the dataflow.
  • ConfiguredBy: Indicates who configured the dataflow.
# Prepare the Dataflow data file
$DataflowFileCsv = '.\PowerBIDataflowInfo.csv'
If (Test-Path $DataflowFileCsv) {
    Remove-Item -Path $DataflowFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f `
    'WorkspaceId', `
    'DataflowId', `
    'DataflowName', `
    'ConfiguredBy'
$DataLine | Add-Content $DataflowFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue

Understanding these headers is crucial as they form the basis of data collection in our PowerShell script. Now, let’s explore how the script collects and organizes data within these categories.

Comprehensive PowerShell script that interacts with the Power BI Service

automate this data collection process using Power BI

Gathering Power BI Data

The script is divided into sections, each responsible for gathering specific types of data. Let’s break it down step by step: 

Section 1: Installing Required Modules

Before we dive into Power BI management, we start by installing the necessary PowerShell module for Power BI.

# Install the required module for Power BI
Install-Module -Name MicrosoftPowerBIMgmt -Force -AllowClobber

Section 2: Connecting to the Power BI Service

We establish a connection to the Power BI Service. This connection allows us to interact with Power BI resources programmatically.

# Connect to the Power BI Service
Connect-PowerBIServiceAccount

Section 3: Capacity Data Collection

We collect information about Power BI capacities. This includes the capacity’s ID, name, and SKU (Stock Keeping Unit).

# Collect a list of capacities from the Power BI Service
# Comment the below line if you do not have Administrator privileges
Get-PowerBICapacity -Scope Organization | ForEach-Object {
# Uncomment the below line if you do not have Administrator privileges
# Get-PowerBICapacity -Scope Individual | ForEach-Object {
    # Save the capacity info
    If ($_.DisplayName) { $CapacityName = ($_.DisplayName).Replace('"',' ').Trim() } Else { $CapacityName = $Null }
    $DataLine = '"{0}","{1}","{2}"' -f `
        $_.Id, `
        $CapacityName, `
        $_.SKU
    $DataLine | Add-Content $CapacityFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue
}

Section 4: Workspace Data Collection

We move on to gathering details about Power BI workspaces. This includes workspace IDs, names, types, states, and related properties.

# Collect list of workspaces from the Power BI Service
# Comment the below line if you do not have Administrator privileges
Get-PowerBIWorkspace -Scope Organization -Include All -All | ForEach-Object {
# Uncomment the below line if you do not have Administrator privileges
# Get-PowerBIWorkspace -Scope Individual -All | ForEach-Object {
    # Save the workspace info
    If ($_.Name) { $WorkspaceName = ($_.Name).Replace('"',' ').Trim() } Else { $WorkspaceName = $Null } 
    $DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f `
        $_.Id, `
        $WorkspaceName, `
        $_.Type, `
        $_.State, `
        $_.IsReadOnly, `
        $_.IsOrphaned, `
        $_.IsOnDedicatedCapacity, `
        $_.CapacityId
    $DataLine | Add-Content $WorkspaceFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue
 
    # <!--Include Sub-Sections Here-->
}

4.1 Sub-Section: Users Data Collection

In this subsection, we collect information about the users within the workspace. This data includes details such as user identifiers, principal types, and access rights.

# Save the user info
    ForEach ($User In $_.Users) {
        $DataLine = '"{0}","{1}","{2}","{3}"' -f `
            $_.Id, `
            $User.Identifier, `
            $User.PrincipalType, `
            $User.AccessRight
        $DataLine | Add-Content $UserFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue       
    }

4.2 Sub-Section: Reports Data Collection

In this subsection, we collect information about reports within each workspace. This includes the report ID, name, and dataset ID.

# Save the report info
    ForEach ($Report In $_.Reports) {
        If ($Report.Name) { $ReportName = ($Report.Name).Replace('"',' ').Trim() } Else { $ReportName = $Null }
        $DataLine = '"{0}","{1}","{2}","{3}"' -f `
            $_.Id, `
            $Report.Id, `
            $ReportName, `
            $Report.DatasetId
        $DataLine | Add-Content $ReportFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue    
    }

4.3 Sub-Section: Datasets Data Collection

Here, we gather details about datasets within the workspace. This includes dataset ID, name, and various properties.

# Save the dataset info
    ForEach ($Dataset In $_.Datasets) {
        If ($Dataset.Name) { $DatasetName = ($Dataset.Name).Replace('"',' ').Trim() } Else { $DatasetName = $Null }
        $DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f `
            $_.Id, `
            $Dataset.Id, `
            $DatasetName, `
            $Dataset.ConfiguredBy, `
            $Dataset.IsRefreshable, `
            $Dataset.IsEffectiveIdentityRequired, `
            $Dataset.IsEffectiveIdentityRolesRequired, `
            $Dataset.IsOnPremGatewayRequired
        $DataLine | Add-Content $DatasetFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue
    }

4.4 Sub-Section: Dashboards Data Collection

We gather information about dashboards and tiles in this sub-section. This includes the dashboard ID, name, and whether it is read-only.

# Save the dashboard info
    ForEach ($Dashboard In $_.Dashboards) {
        If ($Dashboard.Name) { $DashboardName = ($Dashboard.Name).Replace('"',' ').Trim() } Else { $DashboardName = $Null }
        $DataLine = '"{0}","{1}","{2}","{3}"' -f `
            $_.Id, `
            $Dashboard.Id, `
            $DashboardName, `
            $Dashboard.IsReadOnly
        $DataLine | Add-Content $DashboardFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue
                
        # <!--Include Tiles Sub-Section Here-->

    }

4.4.1 Sub-Section: Tiles Data Collection (Within Dashboards)

This sub-section deals specifically with tiles within the dashboards. It gathers tile information, including title, associated dashboard, report, and dataset.

# Save the tile info
        $WorkspaceId = $_.Id
        Try {
            # Comment the below line if you do not have Administrator privileges
            Get-PowerBITile -Scope Organization -DashboardId $Dashboard.Id | ForEach-Object {
            # Uncomment the below line if you do not have Administrator privileges
            # Get-PowerBITile -Scope Individual -DashboardId $Dashboard.Id | ForEach-Object {

                If ($_.Title) { $TileTitle = ($_.Title).Replace('"',' ').Trim() } Else { $TileTitle = $Null }
                $DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}"' -f `
                    $WorkspaceId, `
                    $_.Id, `
                    $TileTitle, `
                    $Dashboard.Id, `
                    $_.ReportId, `
                    $_.DatasetId
                $DataLine | Add-Content $TileFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue            
            }
        }
        Catch { }

4.5 Sub-Section: Dataflows Data Collection

In this sub-section, we collect information about dataflows, such as dataflow ID, name, and the user who configured it.

# Save the dataflow info
    ForEach ($Dataflow In $_.Dataflows) {
        If ($Dataflow.Name) { $DataflowName = ($Dataflow.Name).Replace('"',' ').Trim() } Else { $DataflowName = $Null }
        $DataLine = '"{0}","{1}","{2}","{3}"' -f `
            $_.Id, `
            $Dataflow.Id, `
            $DataflowName, `
            $Dataflow.ConfiguredBy
        $DataLine | Add-Content $DataflowFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue
    }

Section 5: Wrapping It Up

The CSV files now contain a wealth of information about your Power BI resources, making it easier to track, manage, and optimize your Power BI environment.

Managing a Power BI environment can be complex, but with the right tools, it doesn’t have to be. By scheduling and running this PowerShell script on a regular basis, you can build a systematic approach for monitoring your Power BI ecosystem. The script eliminates manual tracking of resources, while the outputted CSV files give you an organized reference to analyze adoption, usage, and performance over time.

As Power BI usage grows within an organization, oversight is critical not only for optimization, but for security and compliance as well. This PowerShell script serves as a foundational piece for gaining control over your Power BI landscape. In the fast-moving world of business intelligence and analytics, being able to tame your Power BI environment is essential. Take control with automation using this PowerShell approach.

End

About the Author
As a 5-time Tableau Community Forums Ambassador and leader of the Tableau and Power BI Center of Excellence (COE) for a global organization, I bring extensive experience in data analytics and visualization. While my expertise lies in Tableau, my commitment to continuous learning led me to research and script data gathering for Power BI. Join me on this journey as we explore the synergies between Tableau and Power BI, unravel complexities, and drive transformative outcomes through the power of visualization and data.
Hari AnkemAssociate Enterprise Architect | USEReady