My transaction journal tables unfortunately don't have a useful update/modified datetime column, only a posting date or similar. I'm looking to incrementally load these tables, since they are my biggest tables.
I can do this partially based on the Posting Date column which won't change after creation, however I have some columns, e.g. BalanceDue which will mutate once an Invoice has been (partially) paid. Hence, I also can't use an offset incremental loading rule for posting date, since I won't catch these changes.
Anyone got an idea to still minimize loading times on these tables?
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:
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:
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.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
