will powerBI reports need to be republished as part of migrating on prem data warehouse to Azure

Question

My organisation will be moving from a on premise sql server data warehouse to azure sql database.?

Currently, we use a gateway to allow connectivity to on premise Data warehouse

The azure database will be renamed as part of the migration.?

Does this mean that all PowerBI reports will need to be republised pointing to the new azure DWH?

question from:https://stackoverflow.com/questions/66060470/will-powerbi-reports-need-to-be-republished-as-part-of-migrating-on-prem-data-wa

Answer 1

If the database schema remains the same, then you can simply use Power BI REST API to change the datasource and patch the credentials. This can be done for example with PowerShell using Power BI Management CmdLets. First, start PowerShell as administrator and install the CmdLets by executing the following script:

Install-Module MicrosoftPowerBIMgmt

If you don't have admin rights on the computer, or for some other reason do not want to install these for all users, you can install them for the current user only:

Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser

Update the values in the first few lines to point the Azure SQL database and published report, then execute the script to change the data source:

# Fill these ###################################################
$workspaceName = "The name of the workspace where the report is published"
$datasetName = "The name of the report"
$sqlDatabaseServer = "servername.database.windows.net"
$sqlDatabaseName = "Database name"
$username = "[email protected]"
$password = "the password of the power bi user" | ConvertTo-SecureString -asPlainText -Force
################################################################

Import-Module MicrosoftPowerBIMgmt

Clear-Host

$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential | Out-Null

$workspace = Get-PowerBIWorkspace -Name $workspaceName

$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id -Name $datasetName

$datasource = Get-PowerBIDatasource -WorkspaceId $workspace.Id -DatasetId $dataset.Id

# Construct url
$workspaceId = $workspace.Id
$datasetId = $dataset.Id
$datasourceUrl = "groups/$workspaceId/datasets/$datasetId/datasources"

# Call the REST API to get gateway Id, datasource Id and current connection details
$datasourcesResult = Invoke-PowerBIRestMethod -Method Get -Url $datasourceUrl | ConvertFrom-Json

# Parse the response
$datasource = $datasourcesResult.value[0]
$gatewayId = $datasource.gatewayId
$datasourceId = $datasource.datasourceId
$sqlDatabaseServerCurrent = $datasource.connectionDetails.server
$sqlDatabaseNameCurrent = $datasource.connectionDetails.database

# Construct url for update
$datasourePatchUrl = "groups/$workspaceId/datasets/$datasetId/Default.UpdateDatasources"

# create HTTP request body to update datasource connection details
$postBody = @{
  "updateDetails" = @(
   @{
    "connectionDetails" = @{
      "server" = "$sqlDatabaseServer"
      "database" = "$sqlDatabaseName"
    }
    "datasourceSelector" = @{
      "datasourceType" = "Sql"
      "connectionDetails" = @{
        "server" = "$sqlDatabaseServerCurrent"
        "database" = "$sqlDatabaseNameCurrent"
      }
      "gatewayId" = "$gatewayId"
      "datasourceId" = "$datasourceId"
    }
  })
}

$postBodyJson = ConvertTo-Json -InputObject $postBody -Depth 6 -Compress

# Execute POST operation to update datasource connection details
Invoke-PowerBIRestMethod -Method Post -Url $datasourePatchUrl -Body $postBodyJson

# NOTE: dataset credentials must be reset after updating connection details

Now update the values in the beginning of the following script and execute it to patch the credentials for your production database:

# Fill these ###################################################
$workspaceName = "The name of the workspace where the report is published"
$reportName = "The name of the report"
$sqlUserName = "user name"
$sqlUserPassword = "password"
$username = "[email protected]"
$password = "the password of the power bi user" | ConvertTo-SecureString -asPlainText -Force
################################################################

Import-Module MicrosoftPowerBIMgmt

Clear-Host

$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential | Out-Null

$workspace = Get-PowerBIWorkspace -Name $workspaceName

$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id -Name $reportName

$workspaceId = $workspace.Id
$datasetId = $dataset.Id

$datasources = Get-PowerBIDatasource -WorkspaceId $workspaceId -DatasetId $datasetId

foreach($datasource in $datasources) {

  $gatewayId = $datasource.gatewayId
  $datasourceId = $datasource.datasourceId
  $datasourePatchUrl = "gateways/$gatewayId/datasources/$datasourceId"

  Write-Host "Patching credentials for $datasourceId"

  # HTTP request body to patch datasource credentials
  $userNameJson = "{""name"":""username"",""value"":""$sqlUserName""}"
  $passwordJson = "{""name"":""password"",""value"":""$sqlUserPassword""}"

  $patchBody = @{
    "credentialDetails" = @{
      "credentials" = "{""credentialData"":[ $userNameJson, $passwordJson ]}"
      "credentialType" = "Basic"
      "encryptedConnection" =  "NotEncrypted"
      "encryptionAlgorithm" = "None"
      "privacyLevel" = "Organizational"
    }
  }

  # Convert body contents to JSON
  $patchBodyJson = ConvertTo-Json -InputObject $patchBody -Depth 6 -Compress

  # Execute PATCH operation to set datasource credentials
  Invoke-PowerBIRestMethod -Method Patch -Url $datasourePatchUrl -Body $patchBodyJson
}

$datasetRefreshUrl = "groups/$workspaceId/datasets/$datasetId/refreshes"

Write-Host "Refreshing..."

Invoke-PowerBIRestMethod -Method Post -Url $datasetRefreshUrl 

Of course, these scripts can be extended to automatically process all reports in a workspace, or to enumerate all workspaces, etc. but this depends on the deployment in your organization, for which we do not know the details.