Dear @KCMT ,

What I did for some clients is do some kind of ‘ archiving’.
I’ve made several query tables with date filters on the posting date and loaded years or months of that data in a ‘ archived’  table.

So I would have 3 query tables which output 3 tables for the data for 2022 (static, will never change), 2023 (static, will never change) and 2024 which changes daily. but now I only have to load the 2024 data every day. Later in the DSA or even later in the MDW or even later combined the tables with a view in the MDW to Power BI combined the data again so I only load, transform and put it in my MDW for 2024 while the 2022 and 2023 data stays as is (Archived).

The main issue here are year crossings (2023-2024) so when can 2023 be archived. If you want smaller increments like months, which make the loading even faster, you have more ‘ issues’ when doing this.

In the idea section here i’ve opted for a functionality in TX which can do the archiving and partition of this data per year / month automatically (so each month it will create a new table and will add them together with an automatic mapping rule or something). So if you like that funcionality, please give it an upvote:

 

Hope this helps

= Daniel

Hi,

in some ERPs it is possible to build views that do have mutation dates so that you can more effectively filter. As long as you know there are no changes to old posting dates you can do something like @daniel suggests. If you have bookings with old posting dates that get mutated, you might not be able to avoid loading large amounts of data.