Programmatically Deploy Power BI Reports to Power BI Report Server

Quick post today showing how to programmatically deploy a Power BI report to an PBI On-Premise Report Server.

Over the past few years, I’ve had the pleasure of helping a client build a business-user-driven fully automated analytics solution involving the SQL Server, Tabular, and now Power BI. The solution starts with a business analyst logging into a web application, uploading a raw dataset, configuring some parameters, and hitting the “go” button. From there, the rest is magic… but essentially the raw dataset gets parsed, validated, and loaded into a generic SQL Server data model. From there a new tabular model is deployed, processed, and notification is sent to the business analyst informing them that the dataset is ready for analysis.

This client is now ready to hop on the Power BI wagon via the on-premise Power BI Report Server (PBIRS) …and they want it to get part of the automation! So now, after the tabular model is deployed, a standard set of PBIX reports will need to be copied to the PBIRS and updated to point at the tabular model.

Below is a PowerShell snippet used to prove this functionality:

Install-Module -Name ReportingServicesTools

# establish session w/ Report Server
$session = New-RsRestSession -ReportPortalUri http://sql-dev-02/Reports

# create folder (optional)
New-RsRestFolder -WebSession $session -RsFolder "NewDataset" -FolderName "NewDataset"

# upload copy of PBIX to new folder
Write-RsRestCatalogItem -WebSession $session -Path "C:\PBIRS_DeployUpdate_PoC\pbi_reports.pbix" -RsFolder "/NewDataset"

# get data source object
$dataSources = Get-RsRestItemDataSource -WebSession $session -RsItem '/NewDataset/pbi_reports'

# change connection string (to point at new source)
$dataSources[0].ConnectionString = "Data Source=localhost\tab2017;Initial Catalog=NewTabularModel;Cube=Model"

# update data source object on server
Set-RsRestItemDataSource -WebSession $session -RsItem '/NewDataset/pbi_reports' -RsItemType PowerBIReport -DataSources $datasources

3 thoughts on “Programmatically Deploy Power BI Reports to Power BI Report Server

  1. Hey excellent solution. I understand that the above script is automating the process of pointing to the tabular instance that has be spin up before automatically I assume. Can you please share the script that spin up tabular instance automatically by extracting dataset from power bi report server as you mentioned in the blog. We are going power bi report server path and would be grateful to autamate the process of any. Thanks

Leave a Reply