Forum Discussion

scabral's avatar
scabral
Helper IV
6 years ago

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

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.

    • scabral's avatar
      scabral
      Helper IV
      6 years ago

      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?