Forum Discussion
Incremental Refresh with Elastic Tables and Data Detection
Hi jdc2019,
Thank you for your follow-up! You’ve raised an excellent question regarding how Power BI’s incremental refresh engine determines partition updates.
Yes, the Power BI refresh engine evaluates partitions using the modifiedon field, but for it to work optimally, the query must still be folding at the point where modifiedon is treated as a DateTime.
- The refresh engine assesses partitions using the RangeStart and RangeEnd parameters.
- If Power BI can apply the modifiedon filter while query folding is still intact, it will evaluate which partitions need refreshing based on whether the modifiedon value is greater than the last refresh timestamp.
- If query folding breaks before modifiedon is converted to a DateTime, Power BI cannot efficiently determine changes at the partition level, which may lead to unexpected behavior like full partition replacement.
I trust this information proves useful. If it does, kindly Accept it as a solution and give it a 'Kudos' to help others locate it easily.
Thank you.
Community SupportHi jdc2019,
Thanks for sharing your scenario in the community! Also, great insights from lbendlin Power BI’s Incremental Refresh indeed operates at the partition level, meaning that when changes occur within a partition, the entire partition is refreshed rather than individual rows being updated.
Since your query filters records based on modifiedon > LastRefreshTimestamp, it might be causing entire partitions to be excluded from the dataset on subsequent refreshes. When Power BI refreshes a partition, it does not merge with previously loaded records it replaces the entire partition with the new filtered dataset. This could explain why records that did not meet the modified date condition are disappearing.
Consider the below Steps:
- Please ensure modifiedon is correctly recognized as a DateTime before query folding breaks.
- Remove the filter on modifiedon in your query and let Power BI handle change detection at the partition level. Instead of pre-filtering in the query, ensure that incremental refresh settings are correctly applied in Power BI Service.
- Ensure that the incremental refresh window aligns with your expected data retention. Power BI retains only the most recent partitions based on the defined archival period.
If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Thank you.
First - Thank you Ibendlin for the information about data change detection and refresh working only at the partition level. That is not clear from the Microsoft documentation or from any queries to CoPilot. As for the proposed solution, it is not possible to get the modifiedon date in date/time format when extracting the field from a cosmos endpoint. The act of expanding the query to access the columns breaks folding. I needed to understand the algorithm to address this, so is it true that the incremental refresh engine is sampling each partition using the modifiedon date field to determine whether that date is greater than the last refresh date? If so, then I can't use the built in refresh engine to test since I can't access the date/time value of modifiedon prior to the break in query folding. I can probably mimic this behavior by manually partitioning and running the algorithm myself with the powerbi api. I'm using that already to find the last refresh date. Can you confirm that the refresh engine is sampling each partition, and the query must fold at the point where the modifiedon date is a date/time value in order for it to optimize the refresh?
- v-ssriganesh
Hi jdc2019,
Thank you for your follow-up! You’ve raised an excellent question regarding how Power BI’s incremental refresh engine determines partition updates.
Yes, the Power BI refresh engine evaluates partitions using the modifiedon field, but for it to work optimally, the query must still be folding at the point where modifiedon is treated as a DateTime.
- The refresh engine assesses partitions using the RangeStart and RangeEnd parameters.
- If Power BI can apply the modifiedon filter while query folding is still intact, it will evaluate which partitions need refreshing based on whether the modifiedon value is greater than the last refresh timestamp.
- If query folding breaks before modifiedon is converted to a DateTime, Power BI cannot efficiently determine changes at the partition level, which may lead to unexpected behavior like full partition replacement.
I trust this information proves useful. If it does, kindly Accept it as a solution and give it a 'Kudos' to help others locate it easily.
Thank you.Thank you for confirming the algorithm, I've marked your post as a solution since it answers the question clearly. I'll proceed with my approach to manually implement incremental refresh using the XMLA endpoint to partition and the powerbi api to trigger. If I get it working, I'll come back and update this post. Please note - if you are using elastic tables, incremental refresh will not work with data change detection, although you can convert your range parameters to strings and incorporate them into the where clause of the NoSQL query as I showed in my initial post. Just remove the test for last refresh. In my scenario, I have a need to refresh in real time after the user makes changes so I'm looking to get the refresh time down to the minimum for the user experience.
- lbendlin
Super User
You can designate the "today" partition to be in direct query mode.
- lbendlin
It's actually much worse than that. Enabling data change detection will create canary partitions, effectively doubling your storage needs.
Doing this analysis of which partitions need refresh further upstream is a good approach. Then you can use XMLA scripts to selectively refresh impacted partitions, and can basically bypass most of the incremental refresh process. Partition consolidation is something you probably want to keep letting the Power BI service do.
