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:
- Create a view pointing to my main data table
- Add a calculated field to the view (eg: load_date_time) which is the RangeStart / RangeEnd filtered column for Incremental Refresh
- Set the view up so that data within my table that has a date 2018 has a load_date_time of "2018-01-01"
- Publish report to web service and refresh (this step is successful - all data for 2018 enters the report)
- Alter view so that data within my table that has a date of 2019 has a load_date_time of today minus seven days
- Refresh data to populate 2019 data (again, this step is successful. I now have 2018 & 2019 data populated)
- 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
- 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
- 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:
- Why does my above hack not work? What am I missing?
- Is there a better alternative way to get data populated into my report without breaching the two hour time limit?
All assistance appreciated.
