cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Agreenwood
Helper I
Helper I

Incremental Refresh Unexpectedly Truncating Data

Hi all,

 

I have a fairly large dataset, +60m rows of data going back 5 years, which I intend to utilise incremental refresh on to be able to efficiently build and populate reports. The technology used is an ODBC connection on a Power BI Pro license, therefore I'm restricted in what I can do backend with partitions and the web services 2 hour time limit.

 

The incremental refresh settings I have are:

  • Archieve Data going back 7 years
  • Incremental Refresh data going back 7 days
  • Unticked "Only Refresh Complete Days"
  • Unticked "Detect Data Changes"

 

My main challenge is being able to load the historical data into the data model. The maximum time window that I can get squeezed into a 2 hour limit is one years worth of data. So, bareing in mind that I have a further 4 years of data that I want to populate, I am attempting to get creative by introducing the following logic:

 

  1. Create a view pointing to my main data table
  2. Add a calculated field to the view (eg: load_date_time) which is the RangeStart / RangeEnd filtered column for Incremental Refresh
  3. Set the view up so that data within my table that has a date 2018 has a load_date_time of "2018-01-01"
  4. Publish report to web service and refresh (this step is successful - all data for 2018 enters the report)
  5. Alter view so that data within my table that has a date of 2019 has a load_date_time of today minus seven days
  6. Refresh data to populate 2019 data (again, this step is successful. I now have 2018 & 2019 data populated)
  7. Wait one day so that the 2019 data loaded with a refresh date of today minus 7 days falls outside of the "incremental refresh" policy of 7 days
  8. Alter view so that data within my table that has a date of 2020 has a load_date_time of today minus seven days and refresh
  9. Repeat steps 5 - 8 until data is fully populated and then point my load_date_time to the genuine date so that incremental refresh continues to work as intended

My issue with the above is that when I get to step 8, the data refreshes and populates but at the same time it truncates all the data that was loaded in step 7, even though the "load_date_time" is now day 8 and therefore outside of the 7 days refresh policy.

 

I attempted to wait two days between refreshes, yet I experienced the same behaviour. ie - step 7 data is truncated.

 

So, recognising that the above is a bit of a hack of the system in how it was designed to work, my two questions are:

 

  1. Why does my above hack not work? What am I missing?
  2. Is there a better alternative way to get data populated into my report without breaching the two hour time limit?

All assistance appreciated.

 

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @Agreenwood ;

You can check out the following official website documents to help you.

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla

 


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Agreenwood
Helper I
Helper I

Any help / advice / thoughts would be greatly appreciated on this.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors