Categories
Uncategorized

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 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. 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 be automated! 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:

# https://github.com/Microsoft/ReportingServicesTools
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

12 replies on “Programmatically Deploy Power BI Reports to Power BI Report Server”

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

Like

hopefully you’ve conquered this step already but for future readers… the tabular instance is on-prem so from an automation standpoint we just needed to deploy a new copy of the tabular model. We did this by executing a (parameterized) TMSL script via SSIS but it could be done just as easily w/ a PowerShell script or .Net app.

Like

updating the connectionstring works for live SSAS connections, not for import datasets as far as I can tell.

Like

It does not work for normal SQL connections (direct query) either… Did you manage to find a solution/workaround?

Like

Its not work for PowerBi Report Server using Direct query mode.
How to change connection string of powerbi direct query mode in Report server on premises.

Like

Hi Chris – last I checked (several months ago – summer 2019) there was not a good way using the APIs to do this.. things may have changed since then.

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s