BI-Ops – 5/5 – Deploy PowerBI Report Server (CD)

BI-Ops – 1/5 – What we are trying to do
BI-Ops – 2/5 – Source Control (Git)
BI-Ops – 3/5 – Build (CI)
BI-Ops – 4/5 – Deploy DB, SSIS and SSAS (CD)
BI-Ops – 5/5 – Deploy PowerBI (CD)

Now that we’ve deployed what I like to call the backend of our BI solution, lets look at how to deploy our PowerBI reports. For various reasons, we are not using PowerBI.com but PowerBI Report Server hosted by another team in our group, shared amongst companies.

In order to publish our reports, we are going to leverage the REST APIs that are now available inside SSRS/PBiRS.

To do this, we use a script taking in 6 parameters:

Param(  [parameter(Mandatory=$true)][String]$portalURL
,[parameter(Mandatory=$true)][String]$sourceFolder
,[parameter(Mandatory=$true)][String]$targetFolder
,[parameter(Mandatory=$false)][String]$connectionString = 'Keep'
,[parameter(Mandatory=$false)][String]$login
,[parameter(Mandatory=$false)][SecureString]$password
)

• The URL of the portal to deploy the reports to
• The source folder on the local machine to browse for reports
• The target folder on the server
• The connection string if you want to update it
• If we update the connection string, the AD login of the service account browsing the data
• If we update the connection string, the password of the service account browsing the data

We deploy the script on the server with an agent and we call the script with the proper parameters from a deployment pipeline. We still do this manually but I’ll update this post with pipeline screenshots when we switch to full automatic deployment for PowerBI Reports.

What the script then does is pretty straight forward. It is also very inspired from what the guys at Blue Granite do here: https://www.blue-granite.com/blog/power-bi-report-server-devops

For log purposes, it tells us what we entered, except for the password obviously.

Write-Host "Target portal: $portalURL"
Write-Host "Source Folder: $sourceFolder"
Write-Host "Target Folder: $targetFolder"
Write-Host "Data Source: $connectionString"
Write-Host "Service Account: $login"

Then we create a web sessions with the server

$session = New-RsRestSession -ReportPortalUri $portalURL;

And we create a list of all the reports located in the source folder

$LocalPath = $sourceFolder;
$reports = Get-ChildItem -Path $LocalPath -Recurse -Include "*.pbix";

Once that is done, we simply loop on that list and deploy each report

ForEach ($report in $reports)
{
   Write-RsRestCatalogItem -Path $report.FullName -RsFolder "$targetFolder" -Overwrite -WebSession $session;
} 

Now that we can deploy reports, we are going to modify the first connection in the connection list for each report if the connection string parameter was set.

ForEach ($report in $reports)
{
   #Deploy the report to the server
   Write-RsRestCatalogItem -Path $report.FullName -RsFolder "$targetFolder" -Overwrite -WebSession $session;
   #Change datasource if specified
  if ($connectionString -ne 'Keep') {
    $reportPath = $targetFolder + "/" + $report.BaseName
    $dataSources = Get-RsRestItemDataSource -RsItem "$reportPath" -WebSession $session
    $dataSources[0].ConnectionString = $connectionString
    $dataSources[0].DataModelDataSource.AuthType = 'Windows'
    $dataSources[0].DataModelDataSource.Username = $login
    $dataSources[0].DataModelDataSource.Secret = ConvertFrom-SecureString $password
    Set-RsRestItemDataSource -RsItem "$reportPath" -RsItemType "PowerBIReport" -DataSources $dataSources -WebSession $session
  } 
}

And there we have it, a simple script to copy PowerBI reports from one folder to a PowerBI Report Server.

We chose to only update the first connection string as most of our report only target one database or cube and in those rare other cases, we could do the work manually.

If you want to download the file directly, you’ll find it on my Github BITools repo.