Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Incremental Refresh fails at around 2 AM

Hello,

 

I have set up incremental refresh on my Power BI report for 3 tables with the correct set up of my parameters RangeStart and RangeEnd. I'm working with the Flow Power Automate to refresh my dataset every 10 minutes and my workspace is on a premium capacity.

I have been testing my incremental refresh 2 days and during the day it works perfectly but I've seen that at around 02 AM at night it crashes... stating that there's a duplicate value in my table (the typical error that everybody has with incremental refresh)

 

 

When I do the full refresh of my tables in SSMS via de XMLA-endpoints it works and then I can reuse my incremental refresh again...

 

 

Someone who had the same problem like me and can help me with this?

 

Kind regards

 

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Is the file date meaningful?  Can it be used as part of the PK ?

8 Replies

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

    "stating that there's a duplicate value in my table (the typical error that everybody has with incremental refresh)"

     

    i would very much hope that this is the exception.  Likely caused by incorrectly including both RangeStart and RangeEnd.  One of them must be inclusive, the other must be exclusive.

     

    Are you in CEST time zone?  Power BI service runs on UTC.

     

  • naelske_cronos's avatar
    naelske_cronos
    Icon for Advocate II rankAdvocate II
    4 years ago

    Hello lbendlin 

     

    Thank you for your answer but the RangeStart and RangeEnd (see code underneath) parameters are set correctly because my incremental refresh runs during the whole day and the right partitions are created with the correct amount of rows for each partition. I've tested it for 3 days straight and each time past midnight it fails. If I want to run it again, I need to run a full load via the XMLA endpoints and then the incremental refresh works again till past midnight...

     

    The incremental refresh fails on the table with the code underneath. Could it be possible that the problem is caused by the second line 'ReplaceNullByDate'? I have some fields that don't have a datetime but they need to be included anyway so I'm using the current datetime.localnow value so the incremental refresh includes it during the refresh.

     

    let
    Source = Table.Combine({ Exercises, Stations, Areas, VPEs_RWYs }),
    ReplaceNullByDate = Table.ReplaceValue(Source, null, DateTime.From(Date.From(DateTime.LocalNow())), Replacer.ReplaceValue, { "Date" }),
    SelectRowsIncrementalRefresh = Table.SelectRows(ReplaceNullByDate, each [Date] >= RangeStart and [Date] < RangeEnd)
in
    SelectRowsIncrementalRefresh

     

     

    I'm indeed in CEST time zone so I presume that the refresh fails at 00:05 UTC time.

     

    Kind regards

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

      Oh, you are skating on thin ice here. Why do you have null date values in your source tables? Are they consistently null, or randomly?  Will they be replaced by other values later or are they immutable?

       

      Let's say on monday you have null values.  You use daily partitions.  Then on tuesday you are running the same refresh again.  Now your tuesday partition carries the rows, but worst case your monday partition does not get updated and now you ended up with duplicate data...

      • naelske_cronos's avatar
        naelske_cronos
        Icon for Advocate II rankAdvocate II
        4 years ago

        Hello lbendlin 

         

        Well the tables I combine into one dataset for the incremental refresh have some empty datetime values. That's why I use the 'ReplaceNullByDate' to fill in those null values by the 'DateTime.LocalNow()' before using the incremental refresh. So during each refresh the null values are replaced by local datetime and then the incremental refresh does its work by putting those values into the right partition. During day, those values are stored into the correct partition as I have the same amount of rows.

        let
    Source = Table.Combine({ Exercises, Stations, Areas, VPEs_RWYs }),
    ReplaceNullByDate = Table.ReplaceValue(Source, null, DateTime.From(Date.From(DateTime.LocalNow())), Replacer.ReplaceValue, { "Date" }),
    SelectRowsIncrementalRefresh = Table.SelectRows(ReplaceNullByDate, each [Date] >= RangeStart and [Date] < RangeEnd)
in
    SelectRowsIncrementalRefresh

        Those rows that had null values are consistent, like the stations, areas and VPEs_RWYS stay the same each time the refresh happens.

         

        I don't really understand your last sentence? How can my values be null if I fill them with 'DateTime.LocalNow()'?

         

        Thanks for your time to figure this out with me but it's really difficult to understand what's happening if the refresh works the whole day but fails when jumping on a new day.

         

        Kind regards