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
LikeLike
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.
LikeLike
[…] with all good ideas, I started with a google and found this post by Bill Anton which gave me a good start ( I could not get the connection string change to work in […]
LikeLike
[…] with all good ideas, I started with a google and found this post by Bill Anton which gave me a good start ( I could not get the connection string change to work […]
LikeLike
Were you successfully able to change the connection string? It didn’t work for me.
LikeLike
Yes.
LikeLike
updating the connectionstring works for live SSAS connections, not for import datasets as far as I can tell.
LikeLike
very interesting.
LikeLike
It does not work for normal SQL connections (direct query) either… Did you manage to find a solution/workaround?
LikeLike
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.
LikeLike
Gday, Is there a way to copy the scheduled refresh?
LikeLike
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.
LikeLike