Forum Discussion

Oliveti's avatar
Oliveti
Frequent Visitor
1 year ago
Solved

Incremental refresh of a dataset utilizing incrementally refreshed Dataflow

Hi,   I have set a dataflow that is refreshed incrementally due to the size of data (many millions rows of data). That refreshes in 30seconds up to a 1minute every day (very happy). Now I would ...
  • Oliveti's avatar
    1 year ago

    After a week of trial and errors I finially figured it out. I am hoping that this will help other people in a similar situation...

    Issue: Incrementally refreshed dataset against a dataflow is either slow or is not working at all on a larger models

    Cause: Query folding is unavailble by default when refreshing against dataflow

    Fix: In the dataflow setting that you use in your dataset, Go to Settings ->Enhanced compute engine settings -> select "On" option ("Optimized" is selected by default)

    Details: Query folding is normally available when you import tables from a sql database into your dataset. This is also true when you import tables into your dataflow. However when you connect a dataset to a dataflow the "view native query" is always greyed out.
    Query folding is switched off by default. 
    You have to tick "Turn on the enhanced compute engine for this dataflow" in each dataflow against which you are planning to run an incremental refresh. This will make the query folding, correct utilization of StartDate, EndDate parameters in your dataset, work and dataflow now understands what the dataset actually wants and can target import only selected rows. 

    In my case a dataset (quite large) that was not able to refresh within 5hours, is now able to refresh within 40seconds (Incremenatl refresh on TransactionDate+ LMDT changes are both utilized).