Hi,
I have a source table in Navision which contains inventory data. The data is sometimes changed back in time due to miscellaneous reasons. I have so far handled this with change tracking in Sql Server and then I do a merge statement that either inserts new data or does an update on older data already in the fact table.
So my question is how could I handle this in TX and do a incremental load?
Regards Birkir
Best answer by wynkoop
View originalHi Birkir,
Most NAV users find that the NAV field "timestamp" is the best candidate for source-based incremental load. If your technique makes this field unreliable, you might try target-based incremental loading instead.
As an aside, it is often not possible to do an incremental load on the inventory table, at least not into staging. This is because incremental loading only refreshes transformations and lookups on updated records. In denormalized dimensions like Item, it's often necessary to refresh these values every time data is loaded into the DWH.
Does this answer your question?
Well I must follow insert and updates. And by using change data management in SQL Server the sql server tracks changes in the table and collects the records or the primary key of the record changed and whether it was and update, insert or delete. I can then do a merge based on the changed record into the the staging area and again into the fact table.
Does the incremental load in TX support anything similar, i.e updates and inserts on the source side?
Regards
Birkir
Hello again Birkir,
In order to fully answer your question, I've worked to create a knowledge base article on how TX DWA handles incremental loading. You can find the article here:
Please let me know if you have any additional questions on this!
Maybe this can help you: https://legacysupport.timextender.com/hc/en-us/community/posts/205685843-Chained-incremental-loads-cascading-update-?page=1#community_comment_115004310606
This is my attempt on chained incremental loads. These steps will load data incrementally into you inventory table and then also checks if updates occurred on your lookup tables. If values differ between the inventory and lookup table they also get updated in your inventory table.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
