Programmatically Deploying Power BI Reports to Power BI Report Server and change Connection String

Question

Is there any method to deploy Power BI reports to Power BI Report Server without having to manually copy these files, upload them to the server and finally change the data source connectivity information for each report on a report by report basis which is not practical in each customer sites.

Eg. PowerBI Report File - 'Report_1' need to Deploy on Customer server S1, S2, S3, & so on.

Now we doing manually copy these files, upload them to the server and finally change the data source connectivity information for each report on a report by report basis which is not practical in each customer sites.

How we can automate the deployment of PBIX reports to Power BI Report Server and changing Datasource connection string Pro-grammatically.?

Microsoft releasing feature in 2020 Jan to update connection string using API.

Microsoft releasing feature in 2020 Jan. But There is any way in 2019 ? any other way for update connection string ?

Microsoft Link

Answer 1

Finally invented one trick to update Connection String in PowerBI.

First Install PowerBI API in Powershell. Microsoft API don’t give ability to update connection string but give permission to update username. Both username and connection string are stored in encrypted format in database. So logic is pass connection string to username and then copy encrypted string to connection string column in database. Just check below example I have written and invented this trick. Thank you.

# Code By SB 2019
$ReportServerURI = 'http://localhost/PowerBIReports' # Input Local path of powerbi file
$filePath = "C:\12.pbix"                                # Input Local path of powerbi file
$PBIxfileName = "12"                                    # INput your Powerbi File Name
$FolderName ='NewDataset'                               # Input PowerBI server Folder Name Where you wann to deploy
$Username ='admin'
$password ='password'                          
$ReportServerName ='localhost\SQl2017'                  #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportServerPowerBI'           #input PowerBi Database Name 

$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString

$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName

write-host "Deployment Started ..." -ForeGroundColor Yellow 
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-RsRestCatalogItem -WebSession $session -Path $filePath -RsFolder $folderPath -Description $Description -Overwrite
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.AuthType = ‘Windows'
$dataSources[0].DataModelDataSource.Username = $ConnectionString 
$dataSources[0].DataModelDataSource.Secret = $password

Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

$ID =  $dataSources[0].Id
$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "

Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.Username = $Username
$dataSources[0].DataModelDataSource.Secret = $password
Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

write-host "Deployment Done . . ." -ForeGroundColor Green