Forum Discussion

raghav20's avatar
raghav20
Frequent Visitor
2 years ago

Automate Dataset Refresh with PowerShell Scripts

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

Need Help

15 Replies

  • ducky's avatar
    ducky
    Icon for Advocate I rankAdvocate I
    2 years ago

    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. 
     
     
  • raghav20's avatar
    raghav20
    Frequent Visitor
    2 years ago

    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,

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        raghav20
        Frequent Visitor
        1 year ago

        Hi Anonymous , 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!

  • SaiTejaTalasila's avatar
    SaiTejaTalasila
    Icon for Super User rankSuper User
    1 year ago

    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