Update dataset after every 5 mins

[Anonymous]

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.

[NandanHegde]

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]

Hi @NandanHegde ,

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

[v-lid-msft]

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,

[Anonymous]

Hi @v-lid-msft ,

Thanks for bringing the limitation to notice. This is exactly the problem I am facing.
I myself have created the Flow, but due t0 the 8 runs per day limitation I am unable to use this solution.

Is there any way to bypas this without using Premium Capacity?

Even with Premium capacity I understand that the limit is 48 calls per day, which still falls short of my requirement. My list gets around 2000 entries per day and I would like to make my dashboard as close to real time as possible.
Any suggestions are welcome.

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my previous post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Anonymous]

 Hi @v-lid-msft ,

My data set could not be transformed into PowerBi Streaming data set.

We decided to go with premium capacity.

[v-lid-msft]

Hi @Anonymous ,

Firstly, the api call under premium capacity has no times limit. But we think we can try some other solution without using premium capacity. If your data model does not contain other source and the item of list is plain text, number or other, we can try to use the Power BI Streaming dataset (with Historic data option enable), we can use the flow "when an item is created or modify" then "add a row to power bi dataset” ， it is nearly real-time. Then you can connect the streaming dataset in desktop and create complex report. 

Option 2, the sharepoint list source essentially is a http api call, if you can make a program which call the api to get data from sharepoint list and then store it into sql server, then we can connect the sql server by using DirectQuery Mode, it retrive the latest data from sql server when report refresh (not dataset refresh), then the only thing is scheduled the program to call api every serveral minutes, it can achieve  semi-real time experience. Please refer to this documents about the share point list api: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest

Best regards,