Automate Dataset Refresh with PowerShell Scripts

[raghav20]

Dear Members,

I am trying to Automate Dataset refersh using Power shell script, which is failing and throwing following error.

Invoke-PowerBIRestMethod : One or more errors occurred.

At line:22 char:1

+ Invoke-PowerBIRestMethod -Url $pbiURL -Headers $headers -Method POST  ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMe

   thod], AggregateException

    + FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

Below is the script which calls REST API to refresh Dataset 

# Define your credentials

$tenantId = "***********"

$clientId = "************"

$clientSecret = "***********"

# Define the variables

$groupId = "*******************"

$datasetId = "********************"

#Connect the Service Principal

Invoke-PowerBIRestMethod -Url $pbiURL -Headers $headers -Method POST -Verbose

$password = ConvertTo-SecureString $clientSecret -AsPlainText -Force

$Creds = New-Object PSCredential $clientId, $password

Connect-PowerBIServiceAccount   -ServicePrincipal -Credential $Creds -Tenant $tenantId

$headers = Get-PowerBIAccessToken

$pbiURL = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes"

Invoke-PowerBIRestMethod -Url $pbiURL -Headers $headers -Method POST -Verbose

Not sure, what  i'm missing here. Request your help in fixing the issue.

Thanks

[SaiTejaTalasila]

Hi @raghav20 ,

You can use power automate flows instead of power shell.Why you are going with power shell ? Like -any specific requirement.

Thanks,

Sai Teja 

[raghav20]

Hi @SaiTejaTalasila Our customer wants the soultion to be implemented with PowerShell only.

[raghav20]

Thanks for response @lbendlin , @ducky  Actually, i came to know that we have some permissions issues.

APP is not enabled with API permissions to invoke script currently.

Kindly pls help me with what permissions should i be requesting for it at APP end & PBI service side. (I'm not Admin)

I have couple of Dataflows and Datasets which are exist in workspace i would be required to automate them with Powershell. 

The Source is snowflake and i need to integrate PBI with UC4, where the actual Data jobs are running/monitoring. As soon as the job get completes in UC4, i should be connecting thru Powershell scripts and refresh related Datasets in PBI service. - Does anyone have implemented this scenario ?

Do we have any connector to check with UC4 jobs completetion status and inovoke powershell scripts.  I really appreciate if we have any scripts/documentation to refer it.

Thanks,

[v-denglli-msft]

Hi @raghav20 ,

You are refreshing the Power BI dataset by invoking the REST API through the service principal.

The service principal needs to be added with the Dataset.ReadWrite.All API permission.

Then, the /Service principals can use Fabric APIs/ feature needs to be enabled in the admin portal.

Finally, as this superuser @lbendlin said, the notifyOption body may needs to be specified when using POST.
Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Best Regards,

Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[raghav20]

Hi @v-denglli-msft , Thanks for the response after i have received appropriate permissions from App, I was able run the scripts and Dataset refresh is happening for few reports. 

I have some challenge here, i was unable to refresh couple of Datasets.  These datasets were connected with another semantic model as Direct Query. Connection flow as below

Source -> Dataflow ->Semantic Model 1 (Import mode) ->Semantic Model 2 (Direct Query)->Report

Semantic Model1 is a Just model it has custom fields and actual columns from the source

Semantic Model 2 -- Report All visuals are avilable here.

In order to see the latest Data in Report i need to refersh Semantic Model 1 & Semantic Model 2.

I was able to refresh Semantic Model 1 without any issues but, when i try to refersh Semantic Model 2 with same PowerShell script, i am getting below error.

C:\Data\Files\PShell\Refresh_Dataset.ps1 : Failed to initiate dataset refresh: The remote server returned an
error: (415) Unsupported Media Type..Exception.Message
At line:1 char:1
+ .\Refresh_Dataset.ps1 -workspace POC -dataset Report_Model
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Refresh_Dataset.ps1

Response Body: {"error":{"code":"InvalidRequest","message":"Invalid dataset. This API
can only be called on a Model-based dataset"}}

How can i make my script to work for other Dataset as well, is there any way to make it work ? Thanks in advance!

[lbendlin]

You are only allowed to request refreshes for semantic models that you own.

[raghav20]

Hey @lbendlin  Thanks for quick response! Above case both the models I own.

[lbendlin]

Do you get an error message when you try this in the API sandbox?

Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

[raghav20]

Yes @lbendlin i'm getting same error pls find the error message below.

Response Code: 415

{
"error": {
"code": "InvalidRequest",
"message": "Invalid dataset. This API can only be called on a Model-based dataset"
}
}

[lbendlin]

If you get that error in the sandbox then you have bigger problems.  Fix these first before spending time on powershell.

[raghav20]

@lbendlin  Out of 65 reports having below issue only for 2 reports as of now (that are important) that to the design of the reports are done differently than other reports which is causing the issue.

All other Report refreshes are working fine, without any issues with the same powershell script.

I would like to hear, if any other developer came across my scenario/usecase and how they fixed it. 

[lbendlin]

reports have nothing to do with this. This isssue is about semantic models.

[raghav20]

Yes @lbendlin nothing to do with the reports, i mean semantic models of those reports (Models) i was able to do the refresh with same script.

[ducky]

hi @raghav20 

If you connect with the SPN you dont need to get a token. at least in my case i dont have to.

Is your SPN using a certificate or a secret? if its a certificate then its a bit different call. 

Are you using SPN Profiles in each Workspace? if thats the case then in your headers you will need to put this:

$SPHeader = @{'X-PowerBI-profile-id' = $profileId}

    $url = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetid/refreshes"

    # Make the API call

    Invoke-PowerBIRestMethod -Url $url -Method POST -Headers $SPHeader

where $profileId is returned by calling the api to get your profile id. 

Profiles - Get Profiles - REST API (Power BI Power BI REST APIs) | Microsoft Learn

[lbendlin]

You are mixing standard powershell modules and Power BI specific modules. If you use Invoke-PowerBIRestMethod then the -Url parameter must be relative.  You may want to specify a body when using POST (for failure notification).

teddata.github.io/_posts/2023-11-10-AutomatingPowerBIDatasetRefreshWithPowerShell.md at main · TedDa...