How to automatically change server in connection string

Question

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.

Answer 1

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 = "xxxxx@yyyyy.com" 
$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 = "xxxxx@yyyyy.com" 
$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.