Forum Discussion
Incremental refresh of a dataset utilizing incrementally refreshed Dataflow
- 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 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).
Thank you for your input.
The report has to be in working condition sooner or later so if your suggestion is the only thing I can do I will (or just reverse to just using pbix file for reporting and ETL.
As a next step I am thinking to raise a support ticket and get an official view from a Microsoft team. I wonder what their take would be on this - and I will post it here for others that may experience same issue.
Secondary step is to try to test it on our PBI Premium setup and see whether the behaviour is the same (out of curiosity).
- lbendlin1 year ago
Super User
One thing to keep in mind: Dataflow incremental refresh is a horrible black box. You have no control over anything.
Dataset incremental refresh follows the general SSAS partition refresh pattern and is highly controllable via XMLA.
- Oliveti1 year agoFrequent Visitor
Interesting, that is a good point. Thank you.