Scheduled refresh dataset in Power Bi from Power Automate (Microsoft Flow)

[henryco88]

Hi,

I have a Power BI project where the data is stored in sql server through Direct Query. I would like to schedule the update of the report and therefore of the data, through Microsoft Automate (Flow).

When I run the schedule, however, I get this error message "Invalid dataset. This API can only be called on a Model-based dataset power bi". Why?

[Burningsuit]

If you use direct query to access the SQL database, no data is stored in the datamodel, and the Report calls data from the SQL database whenever it is run. In this scenario you do not need to refresh the dataset, you have no data held in the dataset to refresh. You are directly querying the SQL database whenever you run a report, or click on a slicer, data filter etc. hence the Report is always up-to-date.  So this error message is telling you you can't refesh a Direct Query Dataset, as you have no data and no need.

If you build an "Import Model" dataset, when you import (copy) data from your SQL Database into the Power BI Datamodel,  then you need to refresh that at regular intervals to update the datamodel with changes in the SQL Database. Then you can use Power Automate (or standard Power BI) to refresh the Dataset, subject to the normal limitations on refresh - with a Pro Licence 8 refreshes per day with minimum 30 minutes between refresh.

Hope this helps.

Stuart

[henryco88]

Hi,

thanks for the replies. I saw that on power BI service I can schedule the refresh. Is there a way to schedule the refresh less than 15 minutes in Direct Query?

[Burningsuit · Accepted solution]

HI @henryco88 

That refresh only applies to refreshing Dashboards, if you have a Dashboard created from your Report it caches the data from the Report to avoid continually querying the SQL Database. The best you can do is set it to 15 minutes, then it will regularly query the SQL every 15 minutes and update the Dashboard. Alternativly users can select the three dots on the Dashboard menu and select "Refresh" to refresh the Dashboard. This may be better than the Dashboard querying the SQL Database to refresh itself even if no-one is looking at it every 15 minutes 24 hours a day 7 days a week.  

Reports do not have this issue as, with Direct Connection, they query the SQL database each time they're opened.  So when you open a Report you always see the up-to-date data. Dashboards work differently, and cache the tile data, so need refreshing.

You may find that Dashboards are not a good fit with "Direct Query" datasets.

Hope this helps

Stuart

[amitchandak]

@henryco88 ,refer if this can help

https://blog.crossjoin.co.uk/2018/10/21/calling-the-power-bi-rest-api-from-microsoft-flow-part-2-ref...