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

7
votes

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.

enter image description here

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

Microsoft Link

3
deploymentpowerbi
You can deploy using Powershell – Jon

3 Answers

2
votes

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
0
votes

This would only work if the change you need can be driven by a parameter, e.g. for a SQL Server source, can set database, schema or table name (but not server name).

First I would set up the query definitions to use query parameter(s) and test. The specifics of this would depend on your data sources and scenario - you have not provided any info on that.

Then I would call the appropriate REST API Update Parameters method - probably the Group version.

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updateparametersingroup

0
votes

You can deploy using to Power BI Report Server, and change connections and other setting using Powershell using the ReportingServiceTools library, As Power BI Report Service is SSRS you can use the same tools, to load reports, change data connections etc

Example of deploying a file and here

You can also change the connection settings directly in the PBIX file. If you change the extension from pbix to zip you can take a look inside.

Power BI Internal structure If you open the 'Connections' file, it contains the setting via a JSON structured file

{"Version":1,"Connections":[{"Name":"EntityDataSource","ConnectionString":"Data Source=asazure://region.asazure.windows.net/somecubegoes her;Initial Catalog=SmartSpacesAnalysis;Cube=SmartSpacesModel","ConnectionType":"analysisServicesDatabaseLive"}]}

That can be read and changed if needed