Manage Power BI Reports with PowerShell


By:   |   Updated: 2021-01-29   |   Comments (1)   |   Related: > Power BI


Problem

Some organizations have governing standards that are based upon end user self-service business intelligence while other organizations have data that is very confidential in nature and promotes a more controlled reporting environment that is managed by the IT environment. Most IT organizations usually like to follow a software development life cycle (SDLC) in which developers have access to a workspace that is classified as development. When a developer is confident in the report, they might ask the Power BI administrator to copy the reports artifacts to the testing workspace. After testing is completed by the QA employees, the final report can be published into the production workspace. Microsoft has fulfilled this workspace object migration requirement through Power BI Deployment pipelines. However, a company must have a premium capacity to leverage this technology.

In the last two articles, we talked about how to manage both Workspaces and Datasets via PowerShell. The last piece of the puzzle is to learn how to manage Power BI reports using the PowerShell cmdlets. These three articles will be foundational for any future explorations with automating the migration of artifacts from one workspace to another.

Solution

Microsoft has provided the administrator PowerShell cmdlets to manage objects in the Power BI service including workspaces, datasets, and reports. The cmdlets are just a nice wrapper for REST API. If you cannot find a PowerShell cmdlet to perform the required action, call the web service directly. We are going to review cmdlets that manage reports in the cloud.

Business Problem

Our boss has asked us to investigate the use of PowerShell scripts to manage reports for the cloud service. Most windows operating systems come with the Power Shell ISE preinstalled. Please make sure you have version 5.1 or greater. You will need an Azure AD account that has been given rights to use the Power BI APIs. Please see my prior article on how to configure this setting in the Power BI tenant.

We are going to work with two sample reports from Microsoft that are stored in the PBIX format. The first report is a Sales and Marketing solution for the Van Arsdel Ltd. company. The second report is a map of COVID-19 cases in the United States. The actual screen shots were done a couple of months ago. However, the COVID-19 data is stored in the cloud. We can refresh the report to get an up-to-date view of the pandemic.

Review Modules and Cmdlets

Microsoft has supplied the PBI administrator with a bunch of modules and cmdlets that can be used to manage the online service. Please see the online documentation for details.

Power Shell Modules

We are going to focus on the module and cmdlets that deal with reports. Any missing functionality can be overcome with calling the swiss army cmdlet named Invoke-PowerBIRestMethod.

Power Shell Modules

To install these cmdlets, you must have a PowerShell version of 5.1 or later. The following script validates the version, updates the nuget package, installs the Microsoft Power BI management cmdlets, and lists cmdlets filtered by the datasets module.

# What version of PowerShell
$PSVersionTable

# Update nuget
Install-PackageProvider -Name NuGet -Force 

# Install power bi
Install-Module -Name MicrosoftPowerBIMgmt -Force

# Verify module
Get-Command -Module MicrosoftPowerBIMgmt 

# List cmdlets
Get-Command | Where-Object { $_.Source -match 'MicrosoftPowerBIMgmt.Data'} 

The image below shows that my computer has the correct version of PS software.

Power Shell Version

The nuget package has been updated to the latest.

Update nuget package

The image below shoes the cmdlets that are available to the PowerShell developer who works with Power BI reports.

MList Power Shell Cmdlets

We now have the cmdlets installed and can start exploring how to manage reports

Report Publishing

Before we can start exploring the cmdlets for reports, we need to publish a report to a workspace that the pbi@crafydba.com user has control over. The next few paragraphs will go over the steps to accomplish this task. Please download the sales and marketing PBIX file from this Microsoft link now. Next, open the report in the Power BI Desktop designer. The image below shows the user is already logged into the Power BI Service.

Marketing Report

The next step is to create a new workspace in the Power BI Service. I am calling the workspace "Van Arsdel Ltd" with the appropriate description.

Create shared workspace

Click the publish button in the Power BI Desktop Designer. The application is smart enough to know that I have the following workspaces: "my workspace" – is a local workspace that I have full control over, "all company" – is a workspace that is inherited by the Power BI tenant admin role, and "van arsdel ltd" is the new workspace that we just created. Please publish the data and report to the last workspace.

Publish report to service

If we look at the contents of the workspace, we can see that a PBIX report is shown as two components: dataset and report.

Marketing report in new workspace

In short, publishing a report is a trivial task. Make sure that the security is correctly set before publishing any sensitive data to a shared workspace.

Listing Workspaces and Reports

Before we can start exploring the cmdlets for reports, we need to log into the Power BI service using the Connect-PowerBIServiceAccount cmdlet. The PowerShell script creates a credential from a given username and secure password. Then, it uses this credential to connect to the Power BI Service. Please replace these entries with your own values.

#
# Connect to service
#

# User credential
$User = "pbi@craftydba.com"
$Pword = ConvertTo-SecureString –String 'MySamplePwd' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword

# Connect to service
Connect-PowerBIServiceAccount -Credential $Credential

The image below shows a successful connection has been made.

Log into PBI service

If you take another look at the cmdlets listed above, you will notice that about half of the cmdlets are dedicated to listing information about reports, dashboards, and tiles. In the next few sections, we will go over these cmdlets in detail with simple examples.

The Get-PowerBIWorkspace cmdlet can be used to locate the recently created workspace named "Van Arsdel Ltd". We will need the workspace id for future cmdlet calls. Therefore, let’s save the GUID to a local variable. Please note that most PBI cmdlets that get (list) a bunch of objects accept a scope indicator of either individual or organization. The individual scope returns only dashboards assigned to the caller. The organization scope returns all dashboards within a tenant using the calling credential of an administrator.

#
# Find new workspace
#
$workspace1 = Get-PowerBIWorkspace -Scope Organization -Name "Van Arsdel Ltd"
$workspace1

The image below shows that pbi@crafydba.com is an admin of this new workspace.

List Power BI workspace

Executing the Get-PowerBIReport cmdlet returns a list of all reports contained in the workspace. The Select-Object cmdlet is used to reduce the total number of output fields and the Format-List is used to display the results.

#
# List reports
#
$report1 = Get-PowerBIReport -Scope Organization -WorkspaceId $workspace1.Id | Select-Object Id, Name, WebUrl, DatasetId
$report1 | Format-List

The image below shows the id of both the report and dataset.

List Power BI report

Listing the workspaces and reports can be achieved with prebuilt PowerShell cmdlets. There are a couple cmdlets that deal with dashboards and tiles. However, we don’t have a dashboard yet. We will be creating one shortly in the next section to explore those cmdlets.

Listing Dashboards and Tiles

The PBI user is prompted to create a new dashboard when a report object is pinned as a tile to a dashboard. I will let you read about the types of objects that can be pinned within the PBI service using this MSDN link. I want to pin the two multi-row cards that depict market share onto a new Executive Dashboard. The screen shot below shows the dialog box for the first tile. Please repeat the process for the second tile. Instead of creating a new dashboard on the second task, please use the existing dashboard.

Pin units market share to new dashboard

The image below shows the Market Share report of the Van Arsdel report. On the right-hand side, we can see our new dashboard named "Executive Dash".

Marketing objects in workspace

If we drill into the dashboard, we can see one tile for "percentage of units by market share" and another tile for "total category volume". Now that we have one new dashboard with two new tiles, let’s explore the cmdlets associated with these object types.

Examine new dashboard with two tiles

Running the Get-PowerBIDashboard cmdlet will result in a list of all the dashboards in a given workspace. Since we have only one dashboard, the results will show one row.

#
# List dashboard
#
$dash1 = Get-PowerBIDashboard -Scope Organization -WorkspaceId $workspace1.Id
$dash1 | Format-List

The output below shows properties of the Executive Dashboard. The embed url is a shortcut link that can be shared with another user that has access to the workspace.

List Power BI dashboard

Use the Get-PowerBITile cmdlet to list all the tiles in a given dashboard. The id of the dashboard is a key parameter to pass when calling the cmdlet. Since we have only two tiles, the results will show two rows.

#
# List dashboard tiles
#
$tiles1 = Get-PowerBITile -Scope Organization -DashboardId $dash1.Id
$tiles1

Since the tile is associated with both a report and dataset, the ids included in the output are the same for both tiles. However, the tile id is different since the two tiles are unique.

List Power BI tiles

To recap, the Power BI cmdlets can also be used to retrieve information about dashboards and tiles.

Importing and Exporting Reports

The Power BI Desktop Designer uses the same REST API calls to publish a report to the cloud service as does the PowerShell module. In this section, we will learn how to display any recent uploads, download an existing report to disk, and upload a new report from disk to the service.

There is the ability called the Get-PowerBIImport cmdlet from the PowerShell ISE environment to list all recent imports into the Power BI Service. The -Filter parameter can be used to narrow down the search to a known file name. The code below looks for any recent imports of the Van Arsdel Ltd, Sales and Marketing PBIX file.

#
# Find most recent import (created date vs update date)
#
Get-PowerBIImport -Scope Organization -Filter "name eq 'Sales and Marketing Sample PBIX.pbix'"

The screen shot below shows that we uploaded this report one time because any additional loads would advance the updated date.

List Power BI imports

A PowerShell script developer can call the Export-PowerBIReport cmdlet to download an existing report to disk. You should have the report id handy since it is a required parameter. One can only work with shared workspaces. Otherwise, the PBI administrator would be able to export data from company employees, such as the CEO, that might be confidential. Please review this GitHub issue for the cmdlet that clarifies how the program can be used.

#
# Export report to local file system
#
Export-PowerBIReport -Id $report1.Id -OutFile 'c:\temp\Sales.pbix' 

The screen shot below was grabbed from Windows Explorer. Exporting the report to disk completed successful. Please download the Covid-19 Tracking report or PBIX file from this Microsoft link now. Save the file in the "c:\temp" directory with a name of "Covid19.pbix".

Local temp directory with both export and import PBIX files

Now, let’s try importing the Covid-19 Power BI report file. The PowerShell script developer can execute the New-PowerBIReport cmdlet to import an existing report to the Azure Power BI service. The path to the PBIX file and name to give the report are the only parameters.

#
# Import report to Power BI Service (my workspace) 
#
New-PowerBIReport -Path 'c:\temp\Covid19.pbix' -Name 'Covid 19 Tracking'

Since we did not supply a scope when calling this cmdlet, the code assumes that an individual scope was required since this is the default option. The image below shows the report and dataset that exist in "my workspace".

Covid 19 report in local workspace

Importing and exporting reports and datasets are very common operations. In fact, these commands can be used in the future to automate deployments from one workspace to another using CI/CD in Azure DevOps.

Dataset Refresh

After importing the Covid19 report, you can investigate when the last refresh for the dataset might have occurred. We can see the refresh failed due to credentials.

Dataset refresh issue

Please edit the dataset credentials. Set the authentication method to anonymous and the privacy level to public. Please repeat this task for both Azure Blobs.

Configure credentials for blob storage

If we refresh the dataset and verify the report, we can see that the Covid-19 map of the United States will be updated. The image was last updated in my Power BI tenant last August. If you follow along with this article, you will have more recent tracking numbers.

Show Covid 19 report with updated data.

In a nutshell, it is very important to refresh the datasets so that the reports have up-to-date data.

Copying Objects

It is now time to test the remaining copy and new cmdlets. The task from our boss is to recreate both the report, dashboard, and tiles in another workspace while sharing the dataset. If we wanted to make a copy of the dataset, we would have to export from one workspace and import it into another one.

The script below creates a new workspace called "Northeast US Reports".

#
# Define new workspace
#
$workspace2 = New-PowerBIWorkspace -Name "Northeast US Reports"
$workspace2

The screen shot captured from the PowerShell ISE shows the new workspace id.

Create new workspace

The Copy-PowerBIReport cmdlet can be used to duplicate the "Van Arsdel - Sales and Marketing" report in the new workspace. We will take advantage of the fact that we have the old workspace, report, and dataset ids handy. Execute the script now.

#
# Copy existing report
#
# Re-use existing dataset
Copy-PowerBIReport -Name "Sales N Marketing" -Id $report1.Id -WorkspaceId $workspace1.Id -TargetWorkspaceId $workspace2.Id -TargetDatasetId $report1.DatasetId

To validate the results of the prior cmdlet, let's look at the report in the new workspace called "Northeast US Reports".

#
# Select the 1 report
#
$report2 = Get-PowerBIReport -Scope Organization -WorkspaceId $workspace2.Id | Select-Object Id, Name, WebUrl, DatasetId
$report2 | Format-List

The image below shows the copied report in the new workspace.

Copy report from old to new workspace

There is no cmdlet to copy a dashboard from one workspace to another. However, we can use the New-PowerBIDashboard cmdlet to create a dashboard named "Northeast Mgmt Dash".

#
# Create new dashboard
#
$dash2 = New-PowerBIDashboard -Name "Northeast Mgmt Dash" -WorkspaceId $workspace2.Id
$dash2 | Format-List

The output below shows the results after executing the above script.

Create a new dashboard

The last step in the task is to copy the two tiles from the original workspace to the new workspace. The script below uses the fact that the output from one cmdlet can be piped into another construct. We know that the output from the Get-PowerBITile cmdlet is an array of object. We can send this data for each loop. In turn, the Copy-PowerBITile cmdlet is called to duplicate the two multi-row cards that represent market share.

#
# Copy dashboard tiles
#
(Get-PowerBITile -Scope Organization -DashboardId $dash1.Id) | ForEach {
Copy-PowerBITile -DashboardId $dash1.Id -TileId $_.Id -TargetDashboardId $dash2.Id -targetWorkspaceId $workspace2.Id -TargetReportId $report2.Id -TargetDatasetId $report1.DatasetId
Write-Host
}

Again, a screen shot was captured from the Power Shell ISE environment. We can see the two tiles were copied to the dashboard.

Copy tiles from old to new workspace

Another way to verify the results of the PowerShell script is to view the Northeast US Reports workspace thru the Power BI Service. We can see both the report and dashboard were created as expected. But where is the dataset? This is a trick question. It was a shared dataset.

Verify the copied report and dashboard tiles via the PBI service

I will leave the manual verification of the dashboard tiles as an exercise for you to do. In the next section, we will talk about cleaning up the environments by deleting objects.

Deleting Objects

Microsoft has supplied script developer with Power BI cmdlets which are just a nice wrapper for REST API. Not all objects can be deleted from the PBI service using PowerShell. Today, we reviewed cmdlets that are associated with Reports, Dashboard, and Tiles. However, there is only one remove cmdlet made for reports. How about Dashboards and Tiles? Please review the Microsoft documentation under "Delete almost anything in Power BI". Almost all items can be deleted from the Power BI Service.

In the large script below, we will talk about what can be deleted in PowerShell. First, we can delete a report by a given id. This does not delete the dataset or any tiles associated with the dataset. In sections two and three, I tried deleting tiles and dashboards via the REST API, but both these endpoints do not exist. It is a little-known fact that deleting a dataset will remove all reports and tiles that are associated with the object. Thus, the action in section four will remove tiles from both workspaces as well as the remaining report in the first workspace. Last but not least, the removal of a workspace makes the dashboard disappear. See section 5 for details. Last but not least, the developer should disconnect from the service when the script is done executing.

#
# 1 - Delete the duplicate report
#
Remove-PowerBIReport -Id $report2.Id

#
# 2 - Delete a single tile (not supported) 
#
$d = $dash2.Id
$t = $(Get-PowerBITile -WorkspaceId $workspace2.Id -DashboardId $dash2.Id | Where-Object { $_.Title -eq "% Units Market Share" }).Id
$Url = "/dashboards/$d/tiles/$t" 
Invoke-PowerBIRestMethod -Url $Url -Method Delete

#
# 3 - Delete a single dashboard (not supported) 
#
$Url = "/dashboards/$d"
Invoke-PowerBIRestMethod -Url $Url -Method Delete

#
# 4 - Delete dataset
#
# Delete dataset
$Id = $dataset1.Id
$Url = "datasets/$Id"
Invoke-PowerBIRestMethod -Url $Url -Method Delete

#
# 5 - Delete dataset
#
# Delete workspace #1
$SearchName = "Van Arsdel Ltd" 
(Get-PowerBIWorkspace -Name $SearchName) | foreach {
$Id = $_.Id
$Url = "groups/$Id"
Invoke-PowerBIRestMethod -Url $Url -Method Delete
}
# Delete workspace #2
$SearchName = "Northeast US Reports" 
(Get-PowerBIWorkspace -Name $SearchName) | foreach {
$Id = $_.Id
$Url = "groups/$Id"
Invoke-PowerBIRestMethod -Url $Url -Method Delete
}

#
# 6 - Stop the session
#
Disconnect-PowerBIServiceAccount

It would be nice if Microsoft opened the REST API services to fully support the deletion of all objects that can be created with PowerShell cmdlets. For now, removing a dataset or workspace will clear out objects that cannot be explicitly deleted.

Summary

This article focused on how to manage Power BI reports, dashboards, and tiles. Retrieving information about the objects is fully supported. However, the deletion of dashboards and tiles is not supported by the cmdlets. In fact, it is not even supported by the REST API. One simple fact helps the developer when programmatically cleaning up an environment. If a Power BI dataset is removed, all reports and tiles are removed.

The export and import cmdlets are the most important cmdlets that were reviewed in this article. We can easily export a report and its corresponding dataset to a PBIX file. The new report cmdlet allows for importing of a PBIX file into a new workspace. The only artifacts that do not programmatically copied are dashboards and datasets. Of course, we did not talk about security on the workspace. However, if we are trying to synchronize the development workspace with the testing workspace, the security is probably already set manually.

I hope you enjoyed reading this article as much as I did writing it. Enclosed is the full script for use in your own investigations. Both sample reports can be downloaded from the Microsoft website. Next time I will be talking about how to use migrate Power BI objects from one workspace to another using Azure DevOps.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

View all my tips


Article Last Updated: 2021-01-29

Comments For This Article




Thursday, August 26, 2021 - 3:10:49 PM - Phil Back To Top (89175)
Hi John,

I can't seem to find a PBIX file that is built from these cmdlet export files....to get a 'report of reports'... do you know of anyone that has done this?


download














get free sql tips
agree to terms