Dataflow never finishes loading

[cpeavyhouse]

I'm working on a dataflow to pull AD data.  The dataflow is running from an on-prem gateway, and should be a relatively quick query (5-10 minutes). Some times it runs and finishes in that timeframe, and other times it doesn't finish.  I let one run for two days before I killed it.

I turned on the additional logging feature on the gateway and watched the activity on the gateway machine while it was refreshing.  I could see the query activity on the CPU monitor for the time length I was expecting, (about 8 minutes), after which, the CPU activity dropped to nothing.

Examining the log files (the mashup files), I can see the gateway hitting AD, pulling the data I'm expecting.  Towards the end of the logs, I see several actions for Document Evaluation (evaluation started, get result, completed, etc.), followed by an error: 

Exception:
ExceptionType: System.IO.IOException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message: The process cannot access the file '0c9cf6ec-3f6f-48d9-9171-d8a36c96c1f9' because it is being used by another process.
StackTrace:
   at System.IO.Directory.DeleteHelper(String fullPath, String userPath, Boolean recursive, Boolean throwOnTopLevelDirectoryNotFound, WIN32_FIND_DATA& data)
   at System.IO.Directory.Delete(String fullPath, String userPath, Boolean recursive, Boolean checkHost)
   at Microsoft.Mashup.EngineHost.Services.EvaluationDirectoryManager.<>c__DisplayClass11_0.<DeleteDirectoryIfNotInUse>b__1()
   at Microsoft.Mashup.EngineHost.Services.FileSystemAccessHelper.<>c__DisplayClass3_0.<IgnoringAccessExceptions>b__0()
   at Microsoft.Mashup.EngineHost.Services.FileSystemAccessHelper.TryIgnoringAccessExceptions[T](Func`1 func, IEvaluationConstants evaluationConstants, T& result, Exception& accessException)

I'm not sure if this is a red herring, but I don't see anything like this in the logs when the data refreshes normally.

I have five queries I'm trying to run, and this happens to them in individual dataflows and grouped within a single dataflow.  We've re-installed the gateway program, restarted the gateway and the host machine.  The issue still persists.

Any help with this would be appreciated.

[v-xicai]

Hi @cpeavyhouse ,

You may need to improve the dataflow performance:

• Use incremental refresh for dataflow, see more detail:https://docs.microsoft.com/en-us/power-bi/service-dataflows-incremental-refresh.

• If you have a Premium license, you will have access to more or fewer functionalities within Dataflow, see more: https://blogs.solidq.com/en/businessanalytics/power-bi-dataflows-how-does-the-new-tool-work/.  (Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cpeavyhouse]

My eventual workaround involved breaking my queries down into several smaller queries that run in parallel.  After all of the queries have finished, I join the results in a second dataflow to get the results I was expecting.  My best guess is that, at some level, my query did not work well with the optimization engine and the dataflow was hitting AD several times for every entity.  I'm still not sure why my original error was occuring, but with my workaround, I haven't run into again.