Is it possible create or link a report to an Azure Data Storage programatically

Question

I would like to use PowerBI Rest API to connect a report to an azure table storage from my NodeJS backend.

Let's take a basic example:

I have already created a report and connected it to Azure Table manually using Power BI Desktop.

Now I would like to duplicate this report but change it's source with another Azure Table Storage ( same data structure, only values change ).

How I think to proceed:

1. Clone report.
2. Clone dataset.
3. Update dataset parameters ( Put new Azure Table credentials ).
4. Update cloned report parameters to new updated dataset.

For 1. I can use Clone Report.

For 2 : I don't know how to duplicate a dataset using Power Rest API ( should I create new dataset? ).

For 3 : I have found this Update API but the is no much details on how to format the body request.

For 4: Update report datasource.

Is it so possible to make connected PBI Datasets-Azure Table storage automatically ?

Is there another way to do it ?

Answer 1

Clone Report API will create new report, but it will be over the same dataset. To make a new report with it's own dataset, you must publish it again using Post Import API. You can also download the original report using Export Report API. Creating dataset from scratch is way more difficult - creating all the tables, their schemas, the relations, data sources and a lot of other details. You can do that using Post Dataset API. If you do that, then you can use Rebind API to rebind the report to the new dataset.

I do not know the exact schema for the body request when the data source is Azure Table. Here is an example with PowerShell how to do that for a SQL Server data source:

Import-Module MicrosoftPowerBIMgmt

# Fill these ###################################################
$workspaceName = "Awesome Reports"
$datasetName = "Awesome Report"
$sqlDatabaseServer = "mycompany.database.windows.net"
$sqlDatabaseName = "CompanyData"
$username = "user@example.com"
$password = "strong password" | ConvertTo-SecureString -asPlainText -Force
################################################################


$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

Probably you can use the Get Datasources API to see how it looks like for your original dataset and update the example above. But instead of using this API, you can specify the data source using parameters, so you will be able to redirect the data source with the same Update Parameters API.

In case the credentials for the data sources are stored in the service, you may need to patch them, like this for example:

Import-Module MicrosoftPowerBIMgmt

# Fill these ###################################################
$workspaceName = "Awesome Reports"
$datasetName = "Awesome Report"
$sqlDatabaseServer = "mycompany.database.windows.net"
$sqlDatabaseName = "CompanyData"
$username = "user@example.com"
$password = "strong password" | ConvertTo-SecureString -asPlainText -Force
################################################################

$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
}