Scheduled refresh question

[heidibb]

Hi Community,

I have a question about how power bi runs through a refresh.

Here is the general issue -- our data warehouse refreshes overnight and usually finishes before 7am. There is one job that is often delayed for a variety of reasons and might complete well into the late morning. Most of our critical power bi reports start refreshing around 8:00 am. The issue is, when the data warehouse is running long, our reports start refreshing against the data warehouse at 8:00, which is causing the data warehouse refresh to slow down even further because queries are being run. so we're causing some of our own issues. 

We are trying to find a way to ensure our pbi refreshes fail immediately if a certain job isn't complete to ensure we don't further slow down our data warehouse refresh.

I do have a stored procedure that will look for this job's completion and throw an "OK" if it's done and it will be blank if not, which in theory will fail the pbi refresh.

My question is, does power bi refresh in the order I load the data connections in? or is there any rhym or reason to how it runs through the connections. For example, if I have six connections in a report... i load this "job check" stored procedure first... and the others after that, will my refresh hit that "job check" first because I loaded it into the pbix first? Is there anything else we should take into account when setting up our data model and refresh settings to ensure it fails if the job is not yet complete?  

Thanks in advance!

[v-luwang-msft]

Hi @heidibb ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[speedramps · Accepted solution]

There is a seperate Power Automate forum and lots of youtube videos.

I suggest you start by creating a simple  Power Automate flow to do a Power BI REST API refresh then run it.

That will trigger a dataset refresh on demand  (no need to schedule it).

Then you can start adding fancy triggers to the flow. 

I use Power Automate flows when users send daily email files I want to include in the ETL, but I dobt know what tiime it will arrive. The flow triggers when an email arrives when an agreed codeword is in the subject header, it extracts the file, does some etl, triggers the Power BI REST API refesh, copys the email to a done folder, even sends a email alert if everything was succesful or failed.   

Power Automate flow  with Power BI REST API refresh are easy, dont be affraid of them. Do the free online training.

start here https://powerautomate.microsoft.com/en-us/

Please click thumbs up and accept solution buttons. Thank you 

[speedramps]

Consider using Power Automate with the PBI Rest API to trogger the refresh.

It may sound really complicated but it is really easy. lost of trainign videos on youtube.

Watch this video 

Please clcik thumbs up and accept solution buttons. Thank you 😎

[heidibb]

@speedramps - thank you!! the part about MS Flow might really work for our situation -- I'm sending this to someone that is much smarter than me. We have a lot of critical reports that take a long time to refresh, and if our data warehouse is running long for whatever reason, we're contributing to the slowness of an already tricky process. One question -- I saw that he(you?) went back to flow and manually hit refresh there. I'm going to explore more about this, but do you schedule flow jobs to run similarly to in the PBI service? Is it automatic?