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).
Super User I don't see any valid reason why would I need to pull the entiry 100mil rows every day. That is very wastefull.Sort of. Consider eliminating the dataflow and only using the semantic model.
However if I do that the refresh in the power bi service times out after 5 hoursThe limit for a partition refresh is 5 hours or 10 GB, whatever comes first. Use bootstrapping to create the partitions without filling them and then refresh them individually, one by one.
Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn
Thank you for your quick response.
Regarding partitions - The incremental refreshes are set in both dataset and dataflow in the same way (by month, refresh last 10 days + utilize LMDT on top to grab just the changes.
The total data size is around 4.5GB.
The reason why I try to go via dataflow, is to limit the load on the on-premise server (which is a perfomace bottleneck in my case). Ie. Get the data as fast as possible to load of the raw tables/data into the power bi service, and then do the additionall processin utilizing the power bi service + the additional benefit of dataflows being available to other users on the power bi service.
The dataflow import works much faster than original import via fully fledged pbix report file. I was/ still am hoping I can still utilize the dataflow/dataset this way.
So, original question still stands - is it possible to incementally refresh a pbix model that is connected to incremenatlly refreshed dataflow?
Nothing stopping me from setting both part up with incremental refresh, but the dataset refresh behaviour so far seem odd - there is something I am still not understanding here.- lbendlin
My recommendation in that case would be to not use incremental refresh in the dataset, and instead swallow your engineering pride and re-ingest the dataflow each time.