Forum Discussion
Incremental refresh of a dataset utilizing incrementally refreshed Dataflow
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 modelsCause: 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).
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).