Managing your Report Server via PowerShell
We have written several blog posts that explain what Power BI Report Server (PBIRS) is, how to acquire it, how it can modernize your on-premises BI solution, and how it supplements Power BI Premium implementations as a component of a hybrid BI solution. What we haven’t discussed are strategies around managing and deploying various PBIRS assets.
PBIRS can be thought of as a superset of SQL Server Reporting Services (SSRS). Therefore, traditional SSRS, Mobile, Key Performance Indicators (KPIs), and Excel-based reports can all be hosted alongside your Power BI reports. With this post, we will describe a large-scale PBIRS implementation we have been working on for a client, as well as some useful DevOps strategies we’ve implemented along the way.
Background
We began collaborating with a client on a brand-new data warehouse last year. BlueGranite partnered with the company to implement everything from overall architecture and design, ETL, and semantic modeling, to development of reports and scorecards. Reporting assets included Power BI, traditional paginated (SSRS), and Excel-based reports. Once we empowered the company’s key stakeholders with self-service business intelligence they grew excited about Power BI development, and began to create their own Power BI reports against the SSAS tabular models we developed.
Due to the complexity of underlying Data Analysis Expressions (DAX) queries, BlueGranite continues supporting our client with ongoing SSRS report development. Today, we maintain three reporting environments (Development, UAT, and Production) with 50+ SSRS reports we developed, along with 50+ Power BI reports developed largely by business users. You can probably imagine, from a DevOps standpoint, the potential for headache.
NOTE: We are not using Mobile Reports and KPIs as part of our solution.
Traditional SSRS development leverages SQL Server Data Tools (SSDT) as a development environment to maintain an overall reporting solution, as well as provide a simple deployment interface for SSRS projects. However, Power BI and Excel-based reports aren’t natively developed and maintained within SSDT. Each type of report has its own client development environment (Power BI Desktop and Excel, respectively) and is maintained as an individual object, as opposed to SSRS reports that are maintained as part of a larger project. From a deployment standpoint, this means they must be manually uploaded/downloaded from one PBIRS instance to another, one report at a time. This is on top of manually maintaining the connection strings for each environment! Luckily, there is an open-source PowerShell module called ReportingServicesTools to help us manage our report servers and deployments.
ReportingServicesTools PowerShell Module
The ReportingServicesTools module was originally developed by the Microsoft Reporting Services Team to enable users to perform various tasks against the SSRS report server within PowerShell. Now the module is open source and available through GitHub. This module is constantly being enhanced by the community to provide more functionality. The list of commands is available on GitHub here.
NOTE: Aaron Nelson has written extensively on this topic. His session on the module at last year’s PASS Summit got us excited to try it out on our client initiative. Thanks, Aaron!
Setup
You can download the module from the PowerShell Gallery with this link, OR you can use the Install.ps1 script from GitHub site above.
Once you have the module downloaded, you must also have at least PowerShell 3.0 installed (but PowerShell 5+ is highly recommended) before you can install the ReportingServicesTools module. If you always want to get latest module when you are running your deployment script, you can check for updates each time using this code snippet:
#Installs SSRS module (need to run as admin to do this)
Install-Module -Name ReportingServicesTools;
#Update SSRS module
Update-Module -Name ReportingServicesTools;
#Import SSRS module
Import-Module -Name ReportingServicesTools;
USE CASE: Exporting
Let’s assume both BI Developers and business power users are developing and modifying reports against your Development PBIRS server. Often, we’ll want to integrate these changes into our source control repository.
To export your reports from your PBIRS development environment, first connect to your PBIRS server and then iterate through all the folders/reports. Next, create an exact copy in your source control environment using the code snippet below. Please replace localhost with the name of your report server and update the LocalPath variable with your local source control folder (in our case, we used Git).
#Create new web session to your Power BI Report portal
$ReportPortalUri = 'http://localhost/reports'; #Replace with your report server URL
$session = New-RsRestSession -ReportPortalUri $ReportPortalUri;
$LocalPath =’C:GitSSRS'; #Specify local folder to download assets to
#Get Power BI and Excel reports from Power BI Report Server
#NOTE: Since we use SSDT for managing SSRS reports, we only wanted to download Power BI & Excel reporting assets
$reports = Get-RsCatalogItems -ReportServerUri $ReportPortalUri -RsFolder '/' -Recurse
|
Where {$_.TypeName -eq 'PowerBIReport' -Or $_.TypeName -eq 'ExcelWorkbook'};
#Export each report that was selected
foreach ($report in $reports)
{
$reportFolder = (Split-Path $report.Path).Substring(1)
$exportPath = $LocalPath + "" + $reportFolder;
#Create folder if it does not exist
if (-Not (Test-Path $exportPath))
{
New-Item $exportPath -ItemType Directory | Out-Null;
}
#Export the report
Out-RsRestCatalogItem -Destination $exportPath -RsItem $report.Path -Overwrite - WebSession $session;
Write-Host "Report exported: $($report.Name)";
}
USE CASE: Deploying
Source control integration is only half of the battle. Often, we’ll also need to deploy these new reports and/or enhancements to a higher environment (i.e. UAT or Production).
Now that your source control environment matches your Development PBIRS server, you can deploy these reports to a different PBIRS instance, such as one used for UAT or Production.
#Again, we use SSDT for deploying SSRS reports. Therefore, we're only using PowerShell for Power BI & Excel reports
#That said, there is nothing preventing you from deploying all report types via PowerShell. Simply remove the "Include" parameter below.
$reports = Get-ChildItem -Path $LocalPath -Recurse -Include "*.pbix","*.xlsx";
#Loop through selected reports
ForEach ($report in $reports)
{
#Get the report folder from source control path
$reportFolder = Split-Path -Path "$($report.Directory)" -Leaf
#Deploy the report to the server
Write-RsRestCatalogItem -Path $report.FullName -RsFolder "/$($reportFolder)" - Overwrite -WebSession $session;
}
Enhancing Your Scripts
There are many ways to enhance these scripts beyond the basic exporting and deployment of Power BI and Excel-based reports. For example, we could have just as easily modified the scripts above to include ALL report types compatible with PBIRS, not just Power BI and Excel. This would allow for a complete deployment via PowerShell, without the need to deploy SSRS reports separately via SSDT.
Grid View & Manual Selections
Parameterizing your script to display a grid view enables the user to select items at run-time, instead of having to update the script prior to running. This is great for items like environment selection and individual, or multiple, report selection for exporting/deploying. The example below displays the reports available on the server, so the user can select which ones they would like to export. The key items below are the Out-GridView cmdlet, along with the -PassThru parameter.
$reports = Get-RsCatalogItems -ReportServerUri $ReportPortalUri -RsFolder '/' -Recurse
|
Where {$_.TypeName -ne 'Folder'} |
Select @{Name="Folder"; Expression={(Split-Path $_.Path).Substring(1)}}, @{Name="Name"; Expression={$_.Name}}, @{Name="Report Type"; Expression={$_.TypeName}}
|
Out-GridView -PassThru -Title 'Select reports to export';
 |
|
Figure 1 – Our PBIRS Folder contains five reports. Three Power BI, one SSRS, and one Excel report. |
 |
|
Figure 2 – The same five reports are shown via a PowerShell grid. We can select one, some, or all, reports to include in our deployment. |
Modifying Connection Strings
Another accelerator we found necessary was to use PowerShell to modify the Power BI and Excel report connection strings prior to deployment. This removes the tedious process of updating each connection string on reports that were deployed to a different environment than they were created in. This is very useful since Power BI and Excel report connections are embedded and do not have the ability to use Shared Data Sources at this time. Open the Power BI or Excel file as though it were a zip file in PowerShell and modify the connection string inside the connections file.
NOTE: This is not a Microsoft supported solution, but sometimes you must get creative when managing hundreds of reports.
Tidying up Source Control
You can also include logic in your script to clean up your source control repository to match what exists on your PBIRS instance. For example, you can traverse through your reporting structure in source control and compare it to what is currently in your PBIRS instance. If any reports were removed on the PBIRS instance, these reports can be outputted into a grid view to allow the developer to review if they should also be removed from source control.
Have questions about Power BI Report Server or just want to learn more? Contact us! We would be happy to share more examples of how you can also take advantage of this great technology.
