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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
scabral
Helper III
Helper III

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.