Forum Discussion

Sadulabhanu's avatar
Sadulabhanu
Regular Visitor
2 years ago

Dataflow Timeout error

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 ca...
Dataflow
pmay's avatar
pmay
Resolver I
2 years ago

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.