Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Update dataset after every 5 mins

Hi,

I am pretty new to Power BI, I am using SharePoint list as my data source and I need to refresh it every 5 mins.
I have tried using MS Flow, where I was triggering the workflow when a new item was created but since the number of entries are very high the workflow is failing.

Please help.

General Comment
Need Help
Tips and Tricks

7 Replies

  • NandanHegde's avatar
    NandanHegde
    Super User
    6 years ago

    Hi,

    You can have a powershell job scheduled every 5 mins running in the backend which would update the dataset. It can either be VM job/ Azure automation/Azure function etc.

     

    $datasetname="<<DatasetNm>>"
$workspacename="<<WorkspaceNm>>"


$clientsec = "$(ClientSecret)" | ConvertTo-SecureString -AsPlainText -Force

$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "<<ClientID>>", $clientsec 
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "<<TenantID>>"

$workspace =Get-PowerBIWorkspace -Name $workspacename

$DatasetResponse=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets" -Method Get | ConvertFrom-Json


$datasets = $DatasetResponse.value

     foreach($dataset in $datasets){
                if($dataset.name -eq $datasetname){
                $datasetid= $dataset.id;
                break;
                }

            }

$body= @{ }

$DatasetResponse=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/refreshes" -Method POST -Body $body | ConvertFrom-Json

    Hope this would resolve your issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi NandanHegde ,

      Thanks for your quick response.
      Where can I find the client ID and the tenant ID?

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        When using the refresh API solution posted by the NandanHegde , Please also notice the limitation : 

         

        In Shared capacities this call is limited to eight times per day (including refreshes executed via Scheduled Refresh)
        In Premium capacities this call is not limited in number of times per day, but only by the available resources in the capacity, hence if overloaded, the refresh execution may be throttled until the load is reduced. If this throttling exceeds 1 hour, the refresh will fail.

         

        Please refer to this document about how to register an application to get the needed information: https://docs.microsoft.com/en-us/power-bi/developer/embedded/register-app

         

        Or you can try to create a scheduled Automated flow to scheduled, but it still have 8 time limit per day (test in the shared capacity)

         


        Best regards,