Dataflow Timeout error

[Sadulabhanu]

I have a dataflow which retrieves data from SQL Server. The result data is huge, it sometimes take more than 10minutes.
Even though I changed the Command timeout to 1hr, still the evaluation gets cancelled in 10mins. Any work around for this?

[pmay]

I am currently struggling with this too, dataflows are natively not useful for SQL data manipulation.  I have temporary tables in my query which are populated on the fly, then merged together (in SQL) at the end.

I have found a workaround, which may or may not work for you.

I created an excel file somewhere on Sharepoint and added it to the model.  The excel file has a dropdown from a choice of "1000" or "All".  This excel file is added to the model with the name "Dev_Mode", you can probably figure out the code yourself but the slightly important bit here is to drill down on the actual value:

let
  Source = SharePoint.Files("[Insert SharepointURL]", [ApiVersion = 15]),
  #"Extracted text after delimiter" = Table.TransformColumns(Source, {{"Folder Path", each Text.AfterDelimiter(_, "/", 5), type text}}),
  #"Filtered rows" = Table.SelectRows(#"Extracted text after delimiter", each ([Folder Path] = "[Insert Folderpath]")),
  Navigation = #"Filtered rows"{9}[Content],
  #"Imported Excel workbook" = Excel.Workbook(Navigation, null, true),
  #"Navigation 1" = #"Imported Excel workbook"{[Item = "Sheet1", Kind = "Sheet"]}[Data],
  #"Kept top rows" = Table.FirstN(#"Navigation 1", 1),
  #"Drill down" = #"Kept top rows"{0}[Column2]
in
  #"Drill down"

I created another query with just this code, and this query is called "var_dev"

if Dev_Mode = "All" then "" else "Top " & Text.From(Dev_Mode)

So now, if I want to work on a dataflow that does joins in a SQL statement, I change the value in Excel to "1000".  When refreshed, var_dev will now return "Top 1000".  If I change the value in Excel to "All", var_dev will return "" (so it returns nothing).

Finally, in my SQL statement, I add this into the select part for each batch of data it calls, so the tables are shortened massively and I can get around the evaluation limitation: 

select "&var_dev&" * from table1

There are certainly more elegant ways to achieve this, but I can't find a way at the moment and I'm sick of trying to get around a limitation that should be configurable.  The evaluation timeout will occur if any SQL statement takes longer than 10 minutes to return any rows, regardless of what you set your query timeout value to, and this timeout affects Dataflows only (semantic models are fine). 

There's an idea that needs votes on this topic: Link.

[v-fenling-msft]

Hi,@Sadulabhanu 

I am glad to help you.  

According to your description, you want to know why you changed the Command timeout to 1h, still the evaluation gets cancelled in 10mins? 

If I understand you correctly, then you can refer to my solution. 

According to your description you have a very large amount of data, so you can try to optimize your data structure and also you can optimize your query algorithm. The complexity of the query is also something that can cause the query to timeout. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

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