TX version 17.1.5.64 SQL 2016 Standard.
We have a situation where a large table in AX (INVENTTRANSORIGIN) does not have a MODIFIEDDATETIME field available to execute incremental loads off of. Our customer is experiencing performance degradation issues with the load due to the large volume of data it's having to reload daily.
This table is also feeding a dimension, so we have history enabled. We were going to attempt to execute target based incremental data loads, but you cannot apply that setting to a history table.
One option would be to replace this table with a query table and have that do the incremental load logic from the source, but this is going to require a TON of rework as the table's surrogate key is referenced in dozens of other tables.
Is there a way to either modify the staging table to be a custom table so that it's 'disconnected' from the source? This would allow me to create a separate incremental query table and have that load our existing staging table via a custom table insert.
Or is there a way to convert an existing staging table to a query table without having to start from scratch?
Thanks!
