Finance | Project Operations, Human Resources, ...

Delta Extract from AX 2012 when ModifiedDate and CreatedDate are not enabled

(0) Share

Report

Posted on by Community Member

Hi All,

We have AX 2012 as ERP system and what I found that ModifiedDate and CreatedDate are not enabled for most of the tables and due to this We Data Warehouse not able to extract delta data from AX. I had a discussion with AX team and seems like Enabling ModifiedDate and CreatedDate can create performance issue with AX. Ax team argument is enabling ModifiedDate and CreatedDate are not recommend from Microsoft. Also If ModifiedDate and CreatedDate need to be enabled on all the tables then AX team has to modify all custom applications modifying data within ax to include these 2 columns as part of custom applications. Seems like enabling ModifiedDate and CreatedDate on each and every table now is a big development work.

AX team recommendation is to go with change tracking feature of SQL Database instead enabling ModifiedDate and CreatedDate.

https://www.timmitchell.net/post/2016/01/18/getting-started-with-change-tracking-in-sql-server/

Just wanted to check with AX Gurus. What is best option here out of 2.

Also there is any other way to capture the delta from AX.

Thank you in advance.

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Ehab_altammam 150 on at

Like (0)

Report

Hi Shivendoo ,

just some comments

not sure about MS Recommendation , but i had large scale implementation where we enabled the Modified/created by,Date time for all tables in the system ( apart from Temp table, and disabled table ) and we never get an a performance issue ... and as i understand such information is very helpful for the business not to mention the auditing side

its not big development work , you can enable these properties by code ( i have an x++ script , loop over the table to set these properties in , and it takes 20-30 mins to run and 20 mins to Sync the DB ) it its couple of hours work ( let me know if you need it :) )

about the custom applications , we cant evaluate the risk until we know the details of each app . (tech , Data set , data size , Recurrence .. )

about the change tracking could do the same ... but i suggest to keep the changes from AX side , so you are maintaining the application tier not the DB Tier ,

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Ehab,

Depending on the number of records which will be added or changed in a table, you can have a small performance penalty. Can you tell which exact changes you need? I have seen performance issues due to ModifiedDate in the past, but that was back in time with AX2009. I believe it was related to a missing index on the RecId field for that table.

If you are able to work with SQL change tracking, then it might be performance wise the better option. That is also the direction Microsoft is using in the current cloud version for the database logging.

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,407 Super User 2025 Season 2 on at

Like (0)

Report

Hello Shivendoo,

Based on my experience on an Ax2012 project, Change tracking is a better option (especially when you have multiple interfaces) rather than enabling CreatedDateTime and ModifiedDateTime on each and every table. Change tracking uses the CRUD mechanism and references an ID field.

CreatedDateTime and ModifiedDateTime is fine when it comes to building reports, where we need to pull out a small sub-set of data. Otherwise, it does have performance issues. If its a long list of tables, then its hard to enable CreatedDateTime and ModfiedDateTime fields on each of those tables. The behaviour is strange if there are more than 1000 records to be pulled out.

Thanks,
Navneeth Nagrajan | Dynamics 365 Technical Consultant

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

Note that ModifiedDateTime field won't help you with deleted records. SQL Server Change Tracking could, but the existing interface to Change Tracking inside AX 2012 induces extra limitations.

I can't remember details; I suspect it works only for tables with RecId as their primary key. I worked on extending the Change Tracking inside AX 2012, but it's quite a few years ago.

You might also decide that you won't use the interface inside AX at all and you'll work directly on DB level. But then would loose access to the application logic, including tables relations.

By the way, I'm 100% sure that you don't need every single table in AX. I think it would be rather a small fraction.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ehab,

Thanks for your input. If this is going to work then It would be big relief for us.

It would be great if you can share the script.

Thank you!

Was this reply helpful? Yes No