Forum Discussion
Helper IRecommended Incremental refresh settings for BC365 fact tables (like GL Entry)?
I have tried out so many settings for incremental refresh on large transaction tables from BC365 (like general ledger entries, project ledger entries), etc.
Nothing works - it only makes the dataflow 2x slower or makes it completely unable to run. Two questions:
1) Any advise on changing settings? These are my current GL entry settings:
2): I have an existing gl entries table in my Fabric Warehouse. As I understand it, I am supposed to use "existing table" setting like this -->
And then pick the existing gl entries table, and check "replace":
Am I doing something wrong? I read the documentation also. I also tried using "new table" instead of "existing table", but this overwrites my gl entries table only with the changed data it found..
Thanks.
13 Replies
LuitwielerMSFTMicrosoft Employee
Hey useruserhi91 !
My name is Jeroen Luitwieler PM for Incremental refresh in dataflows gen2. Super unfortunate to hear your experience is not as expected.
Something I noticed in your screenshots is that you have "requiere incremental refresh query to fully fold" turned off. Is there any reason why you have turned off this setting? Also, which connector are you using? keen to learn more of your end to end situation.
useruserhi91Thanks for replying, I am using the standard Business Central connector. I am turning off query folding option as it says that my query could not work if it is enabled. Any ideas on what to do? Thanks 🙂
- LuitwielerMSFT
I see, I believe that your query uses odata in de backgrounds which should support some kind of folding. Would you mind sharing a screenshots of your applied steps with the folding indicator? there may be a step that causes folding to break.
When folding breaks and you allow incremental refresh to work anyways; the dataflow will pull all data times the number of buckets (3 times in your case) and only apply filters after data is pulled. This will cause huge slowdowns as you experience right now.
- lbendlin
Super User
If these data points are not immutable then incremental refresh is not the right tool for what you are trying to achieve.
Custom partition management is not (yet) available for dataflows.
Consider loading your data directly into a semantic model. There you have fine grain partition control.
Or consider loading your data into a data warehouse or data lake with pipelines.
- useruserhi91
Thanks for your reply. However, of course "systemModifiedAt" is NOT immutable - it changes. The whole point of an incremental refresh is to identify rows that have a dateTime value changed recently, indicating that the row has indeed changed, and must be updated. If this is not possible, what's the point of this incremental refresh feature? Can you give a concrete example of which columns i can use as input for grouping and column changes, and how these columns ought to behave? Thanks a lot!
- lbendlin
The whole point of an incremental refresh is to identify rows that have a dateTime value changed recently, indicating that the row has indeed changed, and must be updated. If this is not possible, what's the point of this incremental refresh feature?What you are looking for is differential refresh, something Power BI is incapable of. Incremental refresh can only go down to partition level for semantic models and bucket/file level for dataflows.
Any changes to past data will require a full or manual refresh of all affected partitions. You would have to create and maintain that process yourself. Unfortunately that is not currently possible with dataflows. LuitwielerMSFT when can we refresh individual buckets?
