以编程方式将 Power BI 报表部署到 Power BI 报表服务器并更改连接字符串 [英] Programmatically Deploying Power BI Reports to Power BI Report Server and change Connection String

问题描述

是否有任何方法可以将 Power BI 报表部署到 Power BI 报表服务器，而无需手动复制这些文件，将它们上传到服务器，最后逐个报表更改每个报表的数据源连接信息，这不是在每个客户站点都实用.

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.

例如.PowerBI 报告文件 - 'Report_1' 需要部署在客户服务器 S1、S2、S3 和 &以此类推.

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.

我们如何自动将 PBIX 报告部署到 Power BI 报告服务器并以编程方式更改数据源连接字符串?

微软将于 2020 年 1 月发布使用 API 更新连接字符串的功能.

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

微软在 2020 年 1 月发布功能.但是 2019 年有什么办法吗?更新连接字符串的任何其他方式?

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

微软链接

推荐答案

终于发明了一个技巧来更新PowerBI中的连接字符串.

Finally invented one trick to update Connection String in PowerBI.

首先在 Powershell 中安装 PowerBI API.Microsoft API 不提供更新连接字符串的功能，但提供更新用户名的权限.用户名和连接字符串都以加密格式存储在数据库中.所以逻辑是将连接字符串传递给用户名，然后将加密字符串复制到数据库中的连接字符串列.只需检查下面我编写并发明了这个技巧的例子.谢谢.

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 ='localhostSQl2017'                  #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportServerPowerBI'           #input PowerBi Database Name 

$ConnectionString ='data source=Client01SQL2019;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 CPMSUNRSQL17CPMSRINST17 -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 

这篇关于以编程方式将 Power BI 报表部署到 Power BI 报表服务器并更改连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！