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.
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.
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.
The nuget package has been updated to the latest.
The image below shoes the cmdlets that are available to the PowerShell developer who works with Power BI reports.
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.
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.
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.
If we look at the contents of the workspace, we can see that a PBIX report is shown as two components: dataset and report.
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.
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.
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.
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.
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".
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.
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.
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.
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.
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".
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".
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.
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.
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.
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.
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.
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.
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.
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.
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
- Download PowerShell scripts.
- How to implement CI/CD for PowerShell objects using Azure DevOps.
