I'm building a data warehouse for a client where one of the sources of data is a Dynamics AX database. For a given AX table I need to determine if a row has been inserted, modified or deleted, so it can be inserted, updated or deleted in the DW.
With regards to detecting a row having been modified, there appears to be two columns in most AX tables I could use for this purpose, ModifiedDateTime and RecVersion. I have two questions related to these columns:
1) Is the ModifiedDateTime column updated each time a row is updated? If so, is this value updated even if someone updates a row directly in the table, outside of an AX application (such as from a tool like SSMS)?
2) Same questions as above for RecVersion. Also, is the number globally unique across the entire database or just unique for the table?
Is there another alternative I'm not aware of to do what I want to do?
You want to use fields RecId and RecVersion, which are available in all business data tables of AX 2012. RecId is a unique value for each record of a table. RecVersion is 1 after a record has been created and is changed to some number other than 1 everytime the record is changed.
ModifiedDateTime is not a good choice, because it is not available on all tables.
Regarding you question if one of those fields is updated when someone modifies data directly in the AX business database: The answer is no and this is one of the reasons why you absolutely never should do such a thing. Honestly, I've seen several customers crash that did this against the explicit recommendations of their implementation partners. Dynamics AX offers several options to integrate data from other systems, use them and don't try to invent your own.
Regarding your question if RecVersion is globally unique: No. You can use a combination of TableId and RecId to uniquelly identify a record in the AX business database.
As I mentioned in my answer to your question How do I get the location for a row in dbo.CUSTTRANS?, AX comes with a data mart and also has some logic on how to update that based on changes in the business data. I'm not familiar with the details of those updates, but it might be worthwhile investigating them to see if you could reuse them for your purposes.