Forum Discussion
Can't select Maps To in Gateway setting via API/Powershell
Hello - I have a dataset which successfully connects to an azure DB via gateway when configured manually. However, when trying to configure the dataset via powershell, unable to select the gateway in 'Maps To' dropdown under the Gateway connection section of the DataSet resulting in the refresh failing with this error "It looks like scheduled refresh failed because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources.".
Note that the credentials are configured in gateway and did not have to be specified in each dataset.
If anyone have used API/Powershell script to configure the 'maps to' successfully please share your thoughts. Have used below API's to configure dataset so far.
Update DataSource - https://api.powerbi.com/v1.0/$groupsPath/datasets/$sourceDatasetId/Default.UpdateDatasources
Bind to Gateway - https://api.powerbi.com/v1.0/$groupsPath/datasets/$sourceDatasetId/BindToGateway
Refresh DataSet - https://api.powerbi.com/v1.0/$groupsPath/datasets/$sourceDatasetId/refreshes
6 Replies
I was able to select the Map To field with the following:
# TRY TO BIND$bindUrl = 'datasets/<ReplaceWithYourGatewayId>/Default.BindToGateway'$postBindParams = @{gatewayObjectId = "yourGatewayId"datasourceObjectIds =@("yourDataSourceId")} | ConvertTo-Jsonwrite-host $postBindParamsInvoke-PowerBIRestMethod -Url $bindUrl -Method Post -Body $postBindParams -ContentType $content -VerboseIts frustrating to say the least but i havent been able to use bind to gateway api via App Id either...
Did you manage to do this?
I have the same problem.
I publish our "Golden Data Set" to a workspace.
I am able to update the report Data Set Parameters "dbName" and "dbServer" using the API.
This enables the Gateway, but the "Map To" field does not select the server by default.
You have to manually select the server after the dbName and dbServer fields are updated.
I need a way to select the Data Source in the Map To dropdown using the API.
Having the same issue, anyone else able to solve this?
DevOps deploys the "Report" Dataset successfully.
DevOps updates the datasource in the "Report" Dataset successfully.The Gateway Connection in the "Report" Dataset loses it's mapping to a 'datasource' in the On-Premise Gateway.
Hi mbutler71,
I was able to select the Map To field with the following:
# TRY TO BIND$bindUrl = 'datasets/<ReplaceWithYourGatewayId>/Default.BindToGateway'$postBindParams = @{gatewayObjectId = "yourGatewayId"datasourceObjectIds =@("yourDataSourceId")} | ConvertTo-Jsonwrite-host $postBindParamsInvoke-PowerBIRestMethod -Url $bindUrl -Method Post -Body $postBindParams -ContentType $content -Verbose
If using Azure DevOps for deployment, in addition to using BindToGateway API, you would want to use a service account (an Azure 360 account and NOT Service Principal) that is a user on the gateway for bind and refresh to work. You need bearer token of that account to be able to accomplish the steps. This is because ServicePrincipals can not be added to powerbi gateways.
Copy below snippet to a PS file and pass in required parameters to bind gateway and refresh dataset. clientId is the Azure ServicePrincipal associated with your PowerBi workspace and pbiUserName is the sevice account that's a user on the gateway. Hope this helps!
param([Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $tenantId,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $clientId,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $pbiUsername,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $pbiPassword,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $groupID,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][String] $sourceDatasetName,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $gatewayId,[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][string] $dataSourceId)Install-Module -Name MicrosoftPowerBIMgmt.Profile -Scope CurrentUser -ForceImport-Module AzureRm.Profile##################################################################################################### Get Auth Token and build Rest API header with authorization token$body = @{"client_id" = $clientId;"grant_type" = "password";"username" = $pbiUsername;"password" = $pbiPassword;"scope" = "openid"}Write-Host $body$authResponse = Invoke-RestMethod -Uri $authUrl -Method POST -Body $bodyWrite-Host "authResponse = $authResponse"$Token = $authResponse.access_tokenWrite-Host "Token = $Token"$AuthHeader = @{"Authorization" = "Bearer $Token"}Write-Host "AuthHeader = $AuthHeader"##################################################################################################### Get list of datasets from the workspace\groupWrite-Host "sourceDatasetName = " $sourceDatasetName$datasets = Invoke-RestMethod -Headers $AuthHeader -Method 'GET' -Uri $datasetsUri -Verbose$datasetsresponse = $response | ConvertTo-JSONWrite-Host $datasetsresponseforeach ($dataset in $datasets.value){if ($dataset.name -eq $sourceDatasetName){$sourceDatasetId = $dataset.id}}Write-Host "dataset id to be used = " $sourceDatasetId##################################################################################################### Take Over DatasetWrite-Host "Start - Take over dataset"$takeOverUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets/$sourceDatasetId/Default.TakeOver"Invoke-RestMethod -Headers $AuthHeader -Method 'POST' -Uri $takeOverUri -VerboseWrite-Host "End - Take over dataset"##################################################################################################### Bind to GatewayWrite-Host "Start - Bind to Gateway"$bindBody =@"{"gatewayObjectId" : "$gatewayId","datasourceObjectIds" : ["$dataSourceId"]}"@Write-Host $bindBody$bindUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets/$sourceDatasetId/Default.BindToGateway"Invoke-RestMethod -Headers $AuthHeader -Method 'POST' -Body $bindBody -Uri $bindUri -ContentType 'application/json' -VerboseWrite-Host "End - Bind to Gateway"##################################################################################################### Refresh datasetWrite-Host "Start - Refresh Dataset"$refreshBody = @{"notifyOption" = "MailOnFailure"}$refreshUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/datasets/$sourceDatasetId/refreshes"Write-Host $refreshUriInvoke-RestMethod -Headers $AuthHeader -Method 'POST' -Body $refreshBody -Uri $refreshUri -ContentType 'application/json' -VerboseWrite-Host "End - Refresh Dataset"
