Forum Discussion
Incremental Refresh on Dataflow sourced from SharePoint
I have a Power BI dataflow that collects daily Excel files from a SharePoint folder. I recently enabled incremental refresh on it and I'm seeing real performance gains, but i dont understand why it works.
From what I understand, the recommended approach is to place the RangeStart/RangeEnd filter early in the Steps, ideally before the file expansion step, so that the query can skip files outside the refresh window. However, Power BI adds the filter at the end of all steps by default, and I haven't moved it.
Despite the filter being at the end, I'm still seeing noticeably faster refresh times.
Is the dataflow engine handling partitioning at the storage level, so it doesn't rely on query folding the way a dataset would?
Does moving the filter to before the expansion step actually make a meaningful difference for file-based dataflows specifically or is the partitioning doing the heavy lifting regardless?
Are there any known gotchas with leaving the filter at the end for this type of setup as the file count grows?
Any experience with incremental refresh on file-based dataflows would be really helpful as most documentation I've found focuses on SQL/database scenarios.
Hi Odiadri
The reason for putting the range start and range end filter early in the steps is that because all of these subsequent steps thereafter will only be using the filtered data and not the entire data set. The reason for the speed increase is because under the hoods the incremental refresh means it does not have to reload the entire data set every day, but only the new data that is incoming and that is why you are seeing the performance gains.
Ah, we haven't mentioned caches yet, have we? Most likely all of your files are only read once from source, and then fetched from cache for the other partitions.
6 Replies
There are a couple of things to unpack here.
In dataflows you do not even get to choose where to place the filter transform. That step will always be placed at the end (where it makes the least sense as GilbertQ mentioned). Rather infuriating.
Next up - query folding. You will most likely not get that with your data store, so this negates one of the big benefits of incremental refresh. This means the dataflow will read all of your source files, and then throw away the ones it doesn't need
So what's left? Storing the results back into the ADLS and/or your specified destination. That is a surprisingly time-expensive operation, and this is where you see your partial savings, starting from the second refresh.
In general incremental refresh on a dataflow is something that you will want to avoid. Better to do that in a semantic model where you have much better control over all the aspects of the process, including proper partition management.
Thank you very much for your insights.
I understand why incremental refresh is faster than full refresh in general, and SharePoint Excel files do not support query folding, and the filter being late is not ideal (yes infuriating!).
However, I still do not fully understand the source side of the process.
My setup is:
- 1 Excel file per day in SharePoint
- Dataflow Gen1: Incremental refresh policy = refresh last 3 days
If the query still has to read all SharePoint files for each partition because there is no folding, then I would expect: Full refresh = read all files once + Incremental refresh for 3 days = read all files 3 times (once per partition).
That should theoretically be slower, not faster, no?
So is the main performance gain simply that only the last 3 partitions are rewritten into storage, while historical partitions are left untouched?
Thank you all!
Ah, we haven't mentioned caches yet, have we? Most likely all of your files are only read once from source, and then fetched from cache for the other partitions.
Hi Odiadri
The reason for putting the range start and range end filter early in the steps is that because all of these subsequent steps thereafter will only be using the filtered data and not the entire data set. The reason for the speed increase is because under the hoods the incremental refresh means it does not have to reload the entire data set every day, but only the new data that is incoming and that is why you are seeing the performance gains.
Hi Odiadri
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi Odiadri
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
