Managing Power BI Shared Datasets Using PowerShell

A shared dataset in Power BI allows multiple reports across different workspaces to connect to the same dataset, promoting data consistency and reducing redundancy. Using PowerShell, you can automate the management of shared datasets, including listing, assigning, updating, and monitoring dataset usage.

Step 1: Prerequisites

1. Install Power BI PowerShell Module

Ensure the Microsoft Power BI Management module is installed. If not, install it using:

Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser -Force

Power BI PowerShell module installed!

2. Sign in to Power BI

Authenticate with your Power BI account:

Connect-PowerBIServiceAccount

You will be prompted to log in with your admin or workspace owner account.

Authenticated successfully!

Step 2: List All Datasets

To retrieve all datasets in your Power BI tenant:

Get-PowerBIDataset

Example Output

Id                                   Name                    Workspace
------------------------------------ ------------------------ ---------------
1234abcd-5678-ef90-ghij-klmnopqrstuv Sales Dataset           Sales Workspace
5678efgh-1234-ijkl-mnop-qrstuvwxyzab Marketing Dataset       Marketing Workspace

Datasets listed successfully!

Step 3: Identify Shared Datasets

To check which datasets are shared across multiple workspaces, use:

Get-PowerBIDataset | Where-Object { $_.IsEffectiveIdentityRequired -eq $true }

Shared datasets retrieved!

Step 4: Assign a Shared Dataset to a Report

To connect a report to a shared dataset:

$workspaceId = "abcd1234-5678-ef90-ghij-klmnopqrstuv"
$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"
$reportId = "5678efgh-1234-ijkl-mnop-qrstuvwxyzab"

Invoke-PowerBIRestMethod -Url "groups/$workspaceId/reports/$reportId/Rebind" -Method Post -Body (@{ datasetId = $datasetId } | ConvertTo-Json -Depth 2)

Shared dataset assigned to a report!

Step 5: Update a Shared Dataset’s Refresh Settings

To modify the refresh schedule of a dataset:

$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

Set-PowerBIDataset -Id $datasetId -RefreshScheduleEnabled $true

Dataset refresh schedule updated!

Step 6: Refresh a Shared Dataset

To trigger a manual dataset refresh:

$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

Invoke-PowerBIRestMethod -Url "datasets/$datasetId/refreshes" -Method Post

Dataset refresh triggered!

Step 7: List Reports Connected to a Shared Dataset

To find reports linked to a shared dataset:

$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

Invoke-PowerBIRestMethod -Url "datasets/$datasetId/reports" -Method Get

Reports linked to shared dataset retrieved!

Step 8: Monitor Dataset Usage

To track how a dataset is being used:

$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

Invoke-PowerBIRestMethod -Url "datasets/$datasetId/usageMetrics" -Method Get

Dataset usage statistics retrieved!

Step 9: Delete an Unused Shared Dataset

If a dataset is no longer needed:

$datasetId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"

Remove-PowerBIDataset -Id $datasetId -Confirm:$false

Unused shared dataset deleted!

Step 10: Automate Shared Dataset Management

To automate dataset refresh, save the following script as AutoRefreshDataset.ps1:

# Connect to Power BI
Connect-PowerBIServiceAccount

# Get shared datasets
$datasets = Get-PowerBIDataset | Where-Object { $_.IsEffectiveIdentityRequired -eq $true }

# Refresh each shared dataset
foreach ($dataset in $datasets) {
    Invoke-PowerBIRestMethod -Url "datasets/$($dataset.Id)/refreshes" -Method Post
    Write-Host " Refreshed dataset: $($dataset.Name)"
}

Then, schedule it using Task Scheduler.

Automated shared dataset refresh enabled!