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:
# 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.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