如何自动更改连接字符串中的服务器 [英] How to automatically change server in connection string

问题描述

我希望能够通过不同的环境来升级报告的版本。我想自动执行此过程（使用章鱼或天蓝色devops），但是我找不到如何为每个环境注入源连接设置。

我知道.pbix文件实际上是包含源的zip文件夹，但是更改文件并重新压缩会导致错误。

编辑：
在我的特定情况下，我正在使用Analysis Services，这很重要，因为并非所有数据源都被平等对待。

解决方案

您可以通过允许报表本身使用

设置其当前值以指向您的数据源之一，例如 SQLSERVER2016 和 AdventureWorks2016 。然后右键单击报表中的查询，然后打开高级编辑器。在M代码中找到服务器名称和数据库名称：

并将它们替换为上面定义的参数，因此M代码将如下所示：

现在，您可以关闭并应用更改，您的报告应该像以前一样工作。但是现在，当您想更改数据源时，请使用 Edit Parameters ：

，并更改服务器和/或数据库名称以指向要用于报表的其他数据源：

更改参数值后，Power BI Desktop将询问您可以应用更改并从新数据源重新加载数据。要更改在Power BI Service中发布的报表的参数值（即数据源），请转到数据集的设置并输入新的服务器和/或数据库名称：

< a href = https://i.stack.imgur.com/5Dkm8.png rel = nofollow noreferrer>

更改数据源后，刷新数据集以从新数据源获取数据。使用Power BI Pro帐户，您可以每24小时执行8次此操作，而如果数据集处于专用容量，则该限制将提高到每24小时48次。

要以编程方式执行此操作，请使用 更新参数 / 组中的更新参数 和 刷新数据集 / 刷新组中的数据集 REST API调用。例如，您可以使用PowerShell这样执行此操作：

 导入模块MicrosoftPowerBIMgmt 
导入模块MicrosoftPowerBIMgmt.Profile 
 
 $ password = xxxxx | ConvertTo-SecureString -asPlainText -Force 
 $ username = [e-mail] 
 $ credential =新对象System.Management.Automation.PSCredential（$ username，$ password）
 
 Connect-PowerBIServiceAccount-凭据$ credential 
 
 Invoke-PowerBIRestMethod -Url'groups / xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx / datasets / xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx / UpdateParameters' -方法发布-Body'{
 updateDetails：[
 {
 name： ServerName，
 newValue： SQLSERVER2019 
}， 
 {
 name： DatabaseName，
 newValue： AdventureWorks2019 
} 
] 
}'
调用- PowerBIRestMethod -URL'groups / xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx / datasets / xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx / refreshes'-方法发布
 
 Disconnect-PowerBIServiceAccount 
  

更新：对于与SSAS的实时连接，无法使用参数。在这种情况下，可以使用更新数据源来更改连接字符串在组中 REST API调用。在PowerShell中，可以这样操作：

 导入模块MicrosoftPowerBIMgmt 
导入模块MicrosoftPowerBIMgmt.Profile 
 
 $ password = xxxxx | ConvertTo-SecureString -asPlainText -Force 
 $ username = [e-mail] 
 $ credential =新对象System.Management.Automation.PSCredential（$ username，$ password）
 
 Connect-PowerBIServiceAccount-凭据$ credential 
 
 Invoke-PowerBIRestMethod -Url'groups / xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx / datasets / xxxxxxxx-xxxx-xxxx-xxxx-xxxxxx-xxxxxx /默认。 UpdateDatasources'-Method Post -Body'{
 updateDetails：[
 {
 datasourceSelector：{
 datasourceType： AnalysisServices，
 connectionDetails ：{{
服务器：我的服务器，
数据库：我的数据库 
} 
}，
 connectionDetails：{
服务器：新服务器，
数据库：新数据库 
} 
} 
] 
}'
 
 Disconnect-PowerBIServiceAccount 
  

请注意，您需要提供新旧服务器和数据库名称

I'd like to be able to promote versions of my reports through different environments. I would like to automate this process (using octopus or azure devops), however I cannot find how to inject source connection settings for each enviornment.

I know that .pbix files are really zip folders containing the source, but changing the file and re-zipping it results in an error.

Edit: In my specific case I'm using Analysis Services, which unfortunatly matters as not all data sources are treated equal.

解决方案

You can achieve this by allowing the report itself to switch it's data source by using connection specific parameters in the report. To do this, open Power Query Editor by clicking Edit Queries and in Manage Parameters define two new text parameters, lets name them ServerName and DatabaseName:

Set their current values to point to one of your data sources, e.g. SQLSERVER2016 and AdventureWorks2016. Then right click your query in the report and open Advanced Editor. Find the server name and database name in the M code:

and replace them with the parameters defined above, so the M code will look like this:

Now you can close and apply changes and your report should work as before. But now when you want to change the data source, do it using Edit Parameters:

and change the server and/or database name to point to the other data source, that you want to use for your report:

After changing parameter values, Power BI Desktop will ask you to apply the changes and reload the data from the new data source. To change the parameter values (i.e. the data source) of a report published in Power BI Service, go to dataset's settings and enter new server and/or database name:

After changing the data source, refresh your dataset to get the data from the new data source. With Power BI Pro account you can do this 8 times per 24 hours, while if the dataset is in a dedicated capacity, this limit is raised to 48 times per 24 hours.

To do this programatically, use Update Parameters / Update Parameters In Group and Refresh Dataset / Refresh Dataset In Group REST API calls. For example, you can do this with PowerShell like this:

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[e-mail]" 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential

Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/UpdateParameters' -Method Post -Body '{
  "updateDetails": [
    {
      "name": "ServerName",
      "newValue": "SQLSERVER2019"
    },
    {
      "name": "DatabaseName",
      "newValue": "AdventureWorks2019"
    }
  ]
}'
Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/refreshes' -Method Post

Disconnect-PowerBIServiceAccount

UPDATE: For Live connection to SSAS, parameters cannot be used. In this case the connection string could be changed using Update Datasources In Group REST API call. In PowerShell this could be done like this:

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[e-mail]" 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential

Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/Default.UpdateDatasources' -Method Post -Body '{
  "updateDetails": [
    {
      "datasourceSelector": {
        "datasourceType": "AnalysisServices",
        "connectionDetails": {
          "server": "My-As-Server",
          "database": "My-As-Database"
        }
      },
      "connectionDetails": {
        "server": "New-As-Server",
        "database": "New-As-Database"
      }
    }
  ]
}'

Disconnect-PowerBIServiceAccount

Note, that you need to provide both old and new server and database names.

这篇关于如何自动更改连接字符串中的服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！