Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
scabral
Helper IV
Helper IV

incremental refresh query trace

 Hi,

i setup incremental refresh on a power bi file to refresh incrementally using detect data changes option against a SQL Server database.  I ran the initial load and then a refresh and when i do a profile trace i see that for each partition it does a select on the LastUpdateDt that i used to detect data changes  which is fine, but it also does a select top 1000 on every column in the table and also another select top 1000 $Table[column name] for every partition even for the incremental loads.

 

i was expecting it to do the LastUPdateDt check for each partion and then execute the select for the partion that changed, but not to always run a select top 1000 for every partion every time?  Does that seem like it is working correctly or possibly something is wrong with the setup?

thanks

Scott

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @scabral ,

 

For the Incremental refresh, there are two Power Query date/time parameters RangeStart and RangeEnd, and it will only filter the changed data between the  two parameters. So you may check if the datetime that is related to the top 1000 rows data is during the  RangeStart and RangeEnd. Or you may change the values of  parameters RangeStart and RangeEnd to test the returned data rows.

 

For reference:

 

Incremental refresh in Power BI

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I understand the parameters, but let's say i have the incremental refresh setup to look back 10 years for each refresh and I have the detect data changes set to a ModifiedDate,  If I only change something from this year (so only 1 record from 2020 has a different ModifiedDate than last refresh), why would it do a select top 1000 for all 10 years again?  What is the point of doing the select Max(ModifiedDate) for each of the 10 years?  Isn't that what is supposed to drive what gets selected?  I would think in this example it would only need to select the data from 2020 since that is the only ModifiedDate that changed since last refresh.  Why would it do a select top 1000 for all 10 years?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.