Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dataflow - Status Failed - Timeout Error

Hi all,   I'm hoping anyone have a good idea on the issue I'm currently facing with my Dataflow   My dataflow is currently loading data from 2 views (let's call it Query 1 and Query 2) where the ...
Dataflow
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    Please add the parameter of timeout into PQ query to have a try.

    [Query="< query is here >", CommandTimeout=#duration(0, 1, 0, 0)]

     

    A user encounter the issue while editing dataflow without doing any changes.  And his problem hasn't fixed for now. I don't know if your situation is same as his.  Please try the code above firstly. If it still don't work,  please create a support ticket to get the dedicated support.

    How to create a support ticket in Power BI  

     

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

 

Please add the parameter of timeout into PQ query to have a try.

[Query="< query is here >", CommandTimeout=#duration(0, 1, 0, 0)]

 

A user encounter the issue while editing dataflow without doing any changes.  And his problem hasn't fixed for now. I don't know if your situation is same as his.  Please try the code above firstly. If it still don't work,  please create a support ticket to get the dedicated support.

How to create a support ticket in Power BI  

 

  • hagbergHCC's avatar
    hagbergHCC
    New Member
    6 years ago

    For those that are having the same issue of a dataflow incremental refresh failing due to a timeout, adding this parameter also solved it for me. However, it was unclear exactly how to accomplish this with the dataflow as the query syntax was not what I could see in Power Query. After further research I found that the specifics are to add the CommandTimeout to the Source line at the top of the M code in the Advanced editor for the dataflow entity that is being set up for incremental refresh:

    Source = Sql.Database("server", "database", [CommandTimeout=#duration(0, 1, 0, 0)])

    The format of the duration is (day, hour, minute, second) so the above line sets the CommandTimeout to 1 hour.

     

    Here are the details of my issue and results:

    • Dataflow from Azure SQL database pulling a single table (~43.5M rows) for data ingestion only (i.e., no transformations)
    • non-incremental refresh: 53 minutes
    • incremental refresh for 6 years total and 7-day incremental refresh
    • many failed incremental refreshes: ~10 minutes
    • added index to SQL table on date field: failed refreshes after ~15 minutes (held out slightly longer)
    • Added CommandTimeout to Source line: initial refresh 1 hr 42 min to pull in and set up all the partitions
    • Successive incremental refreshes: ~20 seconds -- even better than I had hoped for with incremental refresh on this entity!