Power BI REST API using Service Principal – Part 2

In this blog post, I will be showing the Power BI web service settings for implementing Power BI REST API using Service Principal and Sample Powershell script. This is a continuation of previous blog post “Power BI REST API using Service Principal – Part 1“.

Power BI web portal settings

In order to perform these settings, you will need admin portal access to Power BI web service. Below are the steps

• Click on “Settings” (Gear icon) at the top and go to Admin portal.
• Click on “Tenant settings” and scroll down to “Developer settings”.
• Enable “Allow service principals to use Power BI APIs” and specify security groups that should have access. The security group can contain Service principals who should have access.

• In the next step, you just need to add the Service principal to the Workspaces with Admin or Member level access. Go to the Workspace and click on Access to add Service Principal as an Admin or Member. This access will allow the Service Principal to invoke different Power BI REST API calls like get datasets from that Workspace, update parameters of the dataset in the Workspace, update refresh schedule of the dataset, etc. that can help in automation of Power BI dataset and report publishing in the Workspace.

Sample Powershell script

Once all the above settings are completed you will need Powershell script and Power BI REST API to automate various tasks. In below, Powershell script we will implement a simple update parameter of a dataset using Power BI REST API with Service Principal authentication. You will need JSON body with update parameter details. The content from the JSON file will be read in the Powershell and used as JSON body required for updating parameters of the dataset.

JSON file

{
  "updateDetails": [
   {
      "name": "SQLDataSource",
      "newValue": "PROD_SQLDB"
   },
   {
      "name": "OracleDataSource",
      "newValue": "PROD_OracleDB"
   }
}

Powershell script – Update Parameter

[cmdletBinding()]
param (
# Service Principal details
 [Parameter(Mandatory=$true)]
 [string]$thmbprnt,
 [Parameter(Mandatory=$true)]
 [string]$AppId,
 [Parameter(Mandatory=$true)]
 [string]$tenant,
# JSON file path
 [Parameter(Mandatory=$true)]
 [string]$updParameterJSONPath,
# Workspace Name
 [Parameter(Mandatory=$true)]
 [string]$workspace,
# Dataset Name
 [Parameter(Mandatory=$true)]
 [string]$dataset
)

function init-connection() {
 try {
#Using Service Principal with certificate type authentication
     Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint $thmbprnt -ApplicationId $AppId -Tenant $tenant
 }
 catch {
     Write-Host "Failed to connect to Power BI"
 }
}

function end-connection() {
 try {

     Disconnect-PowerBIServiceAccount
 }
 catch {

     Write-Host "Failed to disconnect from Power BI"
 }
}

function get-workspace($workspace) {
 try {
# Get Workspace ID (GUID) from Workspace Name
     $wrkspc = Get-PowerBIWorkspace -Name $workspace
     return $wrkspc.Id.Guid
 }
 catch {
     Write-Host "Failed to get workspace ID from Power BI: $workspace"
 }
}

function get-dataset($dataset) {
 try {

# Get Dataset ID (GUID) from Dataset Name


     $workspaceId = get-workspace -workspace $workspace
     $url_getds = "https://api.powerbi.com/v1.0/myorg/groups/"$workspaceId
     $url_getds += "/datasets"
     $getds = Invoke-PowerBIRestMethod -Url $url_getds -Method GET
     $getds = $getds | ConvertFrom-Json
     $ds = $getds.value | where {$_.name -eq $dataset}
     $datasetId = $ds.id
     return $datasetId
 }
 catch {

     Write-Host "Failed to get dataset ID from Power BI: $dataset"
 }
}

function update-parameter($updParameterJSONPath) {
 try {
     $dsId = get-dataset -dataset $dataset

     if(test-path $updParameterJSONPath) {
      
      $updpara_jsonbody = Get-Content $updParameterJSONPath | Out-String
      $url_updParameter = "https://api.powerbi.com/v1.0/myorg/groups/"$workspaceId
      $url_updParameter += "/datasets/" + $dsId + "/Default.UpdateParameters"
      Invoke-PowerBIRestMethod -Url $url_updParameter -Method Post -Body $updpara_jsonbody -ContentType application/json
     }
     else {
      Write-Host "Invalid File Path"
     }
 }
 catch {

     Write-Host "Error while updating dataset parameters"
 }
}

try {
    update-parameter -updParameterJSONPath $updParameterJSONPath
}
catch {
    Write-Host "Failed to update parameters of the dataset"
}
finally {
    end-connection
}

Above Powershell script can further be expanded to automatically publish dataset, publish report, takeover dataset, bind gateway (i.e. map data sources) and update refresh schedules of a dataset.

Please note

• “Take over dataset” is necessary to avoid 403 forbidden error. This can happen if some other account or user has taken over the dataset.
• Once Service Principal takes over the dataset it needs to bind/ map the Gateway data sources using “Bind to Gateway in Group“.
• Only after binding Gateway data sources the Service principal will be able to “Update Refresh schedule”

Hope this helps if you are trying to automate Power BI dataset configuration.