Refresh Multiple Dataflows and One Dataset Sequentially

[DataSundowner]

Hello everyone. I have a Dashboard/Dataset that connects to 3 dataflows. I've set up the refresh schedule for dataflows. What I want to achieve is to refresh the Dashbaord/Dataset after all those 3 schduled dataflows successfully finish refreshing. I've followed the documentation in the link below and had some sucess with Dataset that has only one Dataflow. However, it doesn't always work as expected for Dataset that has more than 1 Dataflows. Sometimes, it runs forever and sometimes it refreshes Dataset without waiting for all Dataflows finishes refreshing. Any advice and idea would be appreciated. Thank you. 

https://docs.microsoft.com/en-us/power-query/dataflows/trigger-dataflows-and-power-bi-dataset-sequen...

[mbbozzuto]

@lbendlin  If I understand correctly, this approach runs the refreshes of the dataflows in sequence but it seems that the more ideal approach is to run all of the data flows in parallel - this is the value of cloud architecture - and then refresh the dataset only after all of the dataflows have refreshed successfully.  Running them in sequence will cause delays in processing the dataset and then in turn delay presenting data to end users.  Is there an approach in dataflows or Power Automate to run them in parallel?

[lbendlin]

It depends.  Most of all it depends on the performance of your data source.  If you bombard a single processor SQL server with more than a handful of concurrent queries it will fall over, for example.

In Power Automate you can have parallel branches of flows, but you would still need to capture the result of the dataflow refresh somehow - and decide what to do in case of a refresh failure, for example.

I would recommend you write down the process logic you want to implement, have it reviewed/critiqued by a colleague or two (including all crazy scenarios you can think of) , and then implement the flow based on that logic.

[mbbozzuto]

Thanks @lbendlin - yeah, the data source is not compute resource constrained and is scalable for parallel processing.  I will look at the parallel Power Automate flow branching.  Ideally, there will be a Power Automate component that triggers the refresh of the data flow but also waits until it receives a refresh complete response from the data flow (subject to a timeout limit, which should then cause an error in the Power Automate flow and stop processing).

[lbendlin]

Ideally, there will be a Power Automate component that triggers the refresh of the data flow but also waits until it receives a refresh complete response from the data flow

There is not.  You need to write that part yourself.

[lbendlin]

That flow is configured , uhm, interestingly.  You need to handle the "wait until all three dataflows finished"  differently.  Ideally you would only schedule one dataflow, and then sequentially trigger the other dataflow refreshes upon success.  Then you can trigger the dataset refresh when the third dataflow in the chain was refreshed successfully.   (And you want to be alerted about each failure along the chain, so add extra actions for that)

[DataSundowner]

Thank you @lbendlin. I thought about that approach, but my next question is how should I schedule another dataset that also use one or two of these dataflows, do I have to refresh those dataflows again when I set up the flow for the second dataset? Say I have 2 datasets and follow what you said.

Dataset 1: refresh dataflow1, if success, refresh dataflow2, if successs, refresh dataflow3, if success, refresh the dataset 1. 

Dataset 2: refresh dataflow2, if success, refresh dataflow3, if success, refresh dataflow4, if success, refresh the dataset 2 ? 

If I understand corretly, part of the process of refreshing the dataset 2 looks repetitive and I don't want the same dataflow to refresh multiple times during the day. 

[lbendlin · Accepted solution]

Doesn't have to be repetitive.  Each dataflow can be updated as needed.  Technically you can set up the action only for dataflow 3 -> dataset 1 and dataset 4 -> dataset 2,  and then a separate flow that refreshes the dataflows in sequence.   

This is all "event driven" (*)  so no repetition.

*)  Microsoft marketing speak for incessant polling.