Forum Discussion

AnandGNR's avatar
AnandGNR
Icon for Helper I rankHelper I
4 years ago

Incremental Refresh takes almost same time as Full refresh in PBI Service

Hi, I have set up incremental refresh on 2 fact tables that are part of my dataset. I have given an active range of 12 months with an archive of 2 years and use LastModified to detect changes. I kno...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

= Table.SelectRows(#"Changed Type", each [TransactionDate] >= RangeStart and [TransactionDate] < RangeEnd)

Prachi0202's avatar
Prachi0202
Frequent Visitor
2 years ago

Hey thanks, it worked. Can you help me with the Detect Data change option, because the refresh is still taking 40 mins. And I need to use UpdateDate for detecting the data chnges, its the requirement.

Thanks and Regards
Prachi

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Disable that, it is useless.

    • Prachi0202's avatar
      Prachi0202
      Frequent Visitor
      2 years ago

      But then how can we keep a control of the data which is updated, as the UpdateDate is keeping track of such records at our DataWarehouse end.
      As the transactions which are updated at a later date for whatever reason, UpdateDate is keeping track of such records.

      For eg. My transaction is on date 10th Feb 2024 and this data is loaded in table. Then later this transaction data is updted on 14th Feb 2024, so I need to make the changes in my data in the warehouse. So in such scenario, is UpdateDate will be needed to keep track of updated records. So its needs to be in the Detect Data Change option?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        There is a fundamental misconception of how incremental refresh works its magic.  Namely - there is no magic. 

         

        Incremental refresh is designed for immutable data, like IOT sensor data that will never change again.

         

        What you are after is Differential Refresh - inserts AND updates AND deletions.  That is not something Incremental Refresh can do.   To mitigate that it is your responsibility to determine if and when to refresh older partitions, or to perform a full refresh across all partitions.

         

        "Detect Data Change"  attempts to do that for you but does so in a desastrous way.  It creates canary partitions to be able to compare with the "live" data for that period.  In doing so it doubles the amount of space required for your table, including partitions that won't ever change again (because there's no magical way to know that).

         

        Let me repeat: it is your responsibility to determine if and when to refresh older partitions, or to perform a full refresh across all partitions.