Dataflow Scheduled Refresh Not Pulling All Data

[calgarygeologis]

Hello everyone, 

I wonder if anyone can provide some helpful suggestions on how to troubleshoot or correct my situation. I had a few reports that were pulling the same data from an SQL database and the datasets had many of the same base transformation steps with some minor differentation specific to any given report. As such I decided to create a dataflow for that source and to put the shared transformation into the dataflow and the individual steps could be handled in the reports. I switched one report over to the dataflow source and set up scheduled refresh for the dataflow and the dataset. 

I've monitored it for the last week and the dataflow refreshes without any error followed by the dataset without error but it seems as though the dataflow is not getting a full refresh when scheduled. My report shows erroneous information. After I check the report I do an on demand refresh of the dataflow and an on demand refresh of the dataset and the report is good. The next morning it is back to missing data and I have to do on demand refreshes to get the report showing the correct information.

This is set up in a shared capacity workspace and the dataflow refresh happens at 5:30. Based on the history it runs for approximately 12 minutes. When I do an on demand refresh it runs for just over 20 minutes. The dataset refresh is scheduled for 6:30.

What do I need to look at to make this work so that I can connect my other reports to this dataflow and have reliable data?

Thanks

[edhans · Accepted solution]

It suggests to me that at 5:30 in the morning when you are doing the scheduled refresh, some process is happening on the server and all of the data isn't there. 

To test this theory, go set the scheduled refresh to run 5min from now, or at the next half hour increment, then wait and see if that has the full data.

[calgarygeologis]

I can't really use that as a check at this point because the data that I am pulling is static throughout the day and comes from a data warehouse which is only updated over night. It is scheduled by our IT guys to run at midnight and I start my report refreshes at 4am to give everything time to finish. I've asked the IT folks to look into any recent logs or history to see if that nightly refresh has started to run long recently. 

[edhans]

ok, let it run again at 4am. It it has garbage data again, then don't do a manual refresh, set the timer for 15min into the future and let that run.

I suspect the DW isn't up to date.

[calgarygeologis]

I pushed back my dataflow and dataset refresh by 30 minutes for this morning and it loaded a full version of my data for it looks like my issue was related to source data being incomplete. 

Now I need to work with the IT folks to figure out why the source data refresh is taking so much longer than previously. 

[edhans]

Glad you found the issue.

What I would recommend is you work with the team to see if they (the system) can send you an email alert when the data warehouse is fully updated.

Then you can use Power Automate to monitor your emails for that alert, and when it comes in, then dataflows and datasets can be triggered.

So some days everything might start at 3am, but if there is an issue, and the email doesn't come in until 9am, well, yeah, reports are late, but at least they aren't loaded with half data.