Incremental Refresh Questions

[Macc-PA]

Hi,

I am trying to implement Incremental refresh and not having much success with .. a couple of questions

1. MUST the data type for RangeStart and RangeEnd be date/time and not date or date/time/timezone?
2. my filter column (CreateDate) is obtained via an ODATA call and is date/time/timezone ... how do i deal with this if #1 above is true
3. in the profile I need to use 'Detect data Changes' using the LastUpdate column which also date/time/timezone.. does this column need to be date/time.. if so how do i convert it
4. my data to keep is 5 years but the data can get updated in the last 24 months ... so should my 'Store Rows' be 24 months to ensure updates to these rows are captured
5. even though I have 24 months as my window will PBI create partitions which are smaller than the 24 months
6. is it correct that any data change older than 24 months wont get updated in PBI

Many thanks

[Macc-PA]

Thanks for quick reply...
what the best method to convert our datetimezone col to dattime?
what would the reason be not to use detect changes ?
how does the partition refresh work if detect change isnt used
so if we have a window of 24 mths .. any changes within the 24 mths will get picked up.. is that correct?

thanks

[lbendlin]

1. DateTime.From()

2. it doubles the dataset size

3. refresh will be done based on the refresh rules you specified (ie the top 24 partitions)

4. yes

[Macc-PA]

hi,

re #3 ... presume it will only refresh the partitions it needs to within the 24 mths ... is that correct ? We have lots of data in our 24mth window and would not want whole 24mth to get pulled in every time.

Thanks

[lbendlin]

if you specify 24 months then it will refresh all 24 partitions.  If you don't want that, specify a smaller window and refresh the older partitions on a manual basis (every quarter for example).  If you really must, read about the "Detect Data Changes"  process again, identify a suitable column, and try it out. (Not endorsing that, but your situation might require it)

[lbendlin · Accepted solution]

1. datetime or dateinteger

2. convert/cast to datetime

3. don't use that unless absolutely necessary

4. store 5 years, refresh 24 months

5. no. If you specify months then the smallest partition will be month

6. only if you sit on your hands.  Older partitions will be recreated when you make meta data changes and republish the pbix, and you can also manually update those partitions.