Dynamics AX change tracking

April 17, 2015

Its quite common for businesses to want to synchronize data between different applications.  One method is to periodically do bulk data loads where the entire table is exported from AX and then imported into the other application, this is quite inefficient as usually not much of the data has changed and changes in AX are not available in the other application until the batch job has run.  

 

A better method is to track changes to records (inserts, updates & deletes), then only export what has changed. There are a few different methods of doing this in AX which have different advantages and disadvantages.

 

For these examples im going to assume that we want to export customer information including the primary address.  Changes to Customers (CustTable) and also changes to the primary address of the customer (LogisticsPostalAddress) will need to be tracked.

 

 

Using database change tracking  (AX2012 R3)

Make sure change tracking is enabled for the database in SQL server.  You can configure how long the changes are stored for.  The default is 2 days.

 

 

 

Run a job in AX to enable change tracking for the CustTable.  The script also creates SQL triggers on the LogisticsPostalAddress table that mark the CustTable record as being modified whenever an address is updated.  The job needs to run on the server side. If you are getting permission errors then create a menuItem linking to this Job and set the "RunOn" property to "Server".

 

When making changes to changeTracking you need to provide a string field called "Scope", if you need to disable change tracking later and delete the triggers then you can call the disableChangeTracking() and deleteTouchTriggers() methods passing in the same scope.

 

If you already have enabled change tracking on some tables through other methods then it is important to be aware that the AifChangeTrackingConfiguration::enableChangeTrackingForQuery() command will disable change tracking on all tables that have not been enabled through the AIF classes.  This includes the tables that have had change tracking enabled for the AX Retail Sync so it looks like a bug from Microsoft.  When change tracking is enabled through the AIF class it records the change tracking enabled tables in the AifSqlCdcEnabledTables table.  It then calls the SP_ConfigureTablesForChangeTracking stored procedure which synchronizes the change tracking property of all tables with the configuration stored in the AifSqlCdcEnabledTables table.  If you want to prevent ChangeTracking from being disabled on tables then comment out the following lines in the SP_ConfigureTablesForChangeTracking stored procedure:

        --IF (@CTENABLED = 1 AND @CTREQUIRED = 0)

        --BEGIN

        --   EXECUTE ('ALTER TABLE ' + @TABLENAME + ' DISABLE CHANGE_TRACKING;');

        --END

        --ELSE

 

 

The below job enables change tracking and creates the touch triggers:

 

server static void enableChangeTracking(Args _args)

{

    Query                   query;

    QueryBuildDataSource    qbds;

    AifChangeTracking       changeTracking;

    AifChangeTrackingTable  ctTable;

    AifChangeTrackingScope  scope = "CustTableExport";

    ;

 

    //Need to be run on server side

    // If you are getting a error "Permission failed" then create a menuItem linking to this Job and

    // set the "RunOn" property to "Server"

    new AifChangeTrackingPermission().assert();

   

    // Build the query, linking from custTable to address. Filters are not important at this stage

    query = new Query();

    query.name(scope);

    qbds = query.addDataSource(tableNum(CustTable));

    qbds = qbds.addDataSource(tableNum(DirPartyTable));

    qbds.relations(true);

    qbds = qbds.addDataSource(tableNum(DirPartyLocation));

    qbds.relations(true);

    qbds = qbds.addDataSource(tableNum(LogisticsLocation));

    qbds.relations(true);

    qbds = qbds.addDataSource(tableNum(LogisticsPostalAddress));

    qbds.relations(true);

  

    // enable change tracking on CustTable

    AifChangeTrackingConfiguration::enableChangeTrackingForQuery(scope, query);

   

    // Create triggers on child tables to mark records on the parent table as touched

    AifChangeTrackingConfiguration::createTouchTriggersForQuery(scope, AifChangeTrackingTriggerType::AfterInsert, query);

    AifChangeTrackingConfiguration::createTouchTriggersForQuery(scope, AifChangeTrackingTriggerType::AfterUpdate, query);

 

    // Revert the permission assert

    CodeAccessPermission::revertAssert();

}

 

 

Triggers on address table that mark custTable as being touched

 

 

When change tracking is enabled, SQL server will record any inserts, updates or deletes to records in CT enabled tables. Because SQL server is not aware of companies or partitions all changes to the tables are recorded.  Each time a record is changed in AX, on the ttscommit SQL server will store the changeOperation, changeVersion and the primary key for that record.  When ax retrieves the changed records it will join the changeTracking table to the physical table using the primary key, then it will only return the RecId for records that have changed.

 

To get a list of recId's that have been touched we need to pass in a from DateTime telling ax how far to look back.  You cannot specify a DateTime earlier than when change tracking was enabled, so be careful if you have only just enabled change tracking. For a batch job that exports changes you will need to keep a track of the last export datetime so you can use it as the from datetime during the next run.

 

The changeTrackingTable will contain recId's for all companies and all partitions so its important to join it back to the source table.

 

The below job will output customer id's and addresses that have changed.

 

 

static void getCustomerChanges(Args _args)

{

    CustTable                   custTable;

    LogisticsPostalAddress      logisticsPostalAddress;

    

    Query                   q;

    QueryBuildDataSource    qbds;

    QueryRun                qr;

 

    

    Query                   queryCustTable;

    AifChangeTracking       aifChangeTracking;

    utcDateTime             dateTimeFrom;

    AifChangeTrackingTable  changeTrackingTable;

    ;

 

    // Change the date based on what is needed

    // Currently -10 mins - you cannot specify a range earlier than when you switched on change tracking

    dateTimeFrom = DateTimeUtil::addMinutes(DateTimeUtil::getSystemDateTime(), -10);

 

    new AifChangeTrackingPermission().assert();

 

    queryCustTable = new Query();

    queryCustTable.addDataSource(tableNum(CustTable));

    aifChangeTracking = AifChangeTracking::construct(queryCustTable);    

    aifChangeTracking.getChanges(dateTimeFrom, changeTrackingTable);

 

    

    q    = new Query();

 

    qbds = q.addDataSource(tableNum(AifChangeTrackingTable)); 

    qbds = qbds.addDataSource(tableNum(CustTable));

    qbds.addLink(fieldNum(AifChangeTrackingTable, KeyField_RecId), fieldNum(CustTable, RecId));

    qbds = qbds.addDataSource(tableNum(DirPartyTable));

    qbds.relations(true);

    qbds = qbds.addDataSource(tableNum(DirPartyLocation));

    qbds.addRange(fieldNum(DirPartyLocation, IsPrimary)).value(SysQuery::value(NoYes::Yes));

    qbds.relations(true);

    qbds = qbds.addDataSource(tableNum(LogisticsLocation));

    qbds.relations(true);

    qbds = qbds.addDataSource(tableNum(LogisticsPostalAddress));

    qbds.relations(true);    

    

    qr      = new QueryRun(q);

    qr.setCursor(changeTrackingTable);

    while(qr.next()){

        changeTrackingTable     = qr.get(tableNum(AifChangeTrackingTable)); 

        custTable               = qr.get(tableNum(CustTable)); 

        logisticsPostalAddress  = qr.get(tableNum(LogisticsPostalAddress)); 

        info(strFmt("%1, %2, %3", changeTrackingTable.KeyField_RecId, custTable.AccountNum, logisticsPostalAddress.Address));

    }

    CodeAccessPermission::revertAssert();

}

 

 

Modify some customer information and run the job:

 

 

 

Note that it is also possible to query change tracking using the changeVersion instead of passing in a fromDateTime.  The changeVersion is a global number in sql which is incremented every time a record is modified for a change tracked table.  Using the Master Data Management class in ax, MdmChangeTracking.getChanges(changeVersion) you can pass in the from changeVersion and all changes since that change version will be returned in a tempDB table,  the getCurrentVersion() method will return the changeVersion of the most recent change that was fetched and this can be recorded to be used during the next call to getChanges().  If you pass in a changeVersion of -1 then the entire table will be returned.

 

 

 

Using ModifiedDateTime field  (All versions of AX)

 

Enable the modifiedDateTime property of all the tables you want to track changes to.  This is usually already set to enabled for common tables.

 

 

 

Create an index for each table that you will be filtering the ModifiedDateTime by.  The index should have the ModifiedDateTime as its first field.

 

 

 

A select query can get you all the records that have changed within a DateTime range.

 

 

 

    CustTable               custTable;   

    DirPartyLocation        dirPartyLocation;

    LogisticsLocation       logisticsLocation;

    LogisticsPostalAddress  logisticsPostalAddress;

    FromDateTime            fromDateTime = datetimeUtil::addDays(datetimeUtil::getSystemDateTime(), -1);

    ;

    

    while select custTable 

        join dirPartyLocation where 

            dirPartyLocation.Party                  == custTable.Party &&

            dirPartyLocation.IsPrimary              == NoYes::Yes        

        join logisticsPostalAddress where 

            logisticsPostalAddress.Location         == dirPartyLocation.Location &&

           (custTable.modifiedDateTime              >= fromDateTime ||

            dirPartyLocation.modifiedDateTime       >= fromDateTime ||

            logisticsPostalAddress.modifiedDateTime >= fromDateTime)

    {        

        info(strFmt("%1, %2", custTable.AccountNum, logisticsPostalAddress.Address));

    }

 

 

 

 

This only tracks Inserts and Updates,  Deletes will need a different method to track changes.

 

 

 

 

Using ax table triggers  (All versions of AX)

 

Another approach is to use the table methods to log changes into a queue that can be later exported. Keep in mind that overriding the insert and update methods on tables will cause any set based operations to failover to record by record operations.  

 

 

First create a new table to keep a track of changed records.  The example below shows a generic one that can be used to track changes to any table in the system.

 

 

 

Next, override the Insert, Update and Delete methods for the tables that need to be tracked.

 

 

For example the following code is added to the CustTable.Update() method to log changes to the custtable into the queue table.

 

    exportQueue.clear();

    exportQueue.Action      = AifDocumentOperationType::Update;

    exportQueue.RefRecID    = this.RecId;

    exportQueue.RefTableId  = this.TableId;

    exportQueue.Identifier  = this.AccountNum;

    exportQueue.insert();

 

 

For child tables that link back to the main table, the method should only insert records linked to the main table as shown int the logisticsPostalAddress.update() method below.  The "action" that is updated on queue for the parent table should always be "Updated" if called from the child tables "Insert" or "Delete" methods.

 

 

    while select dirPartyLocation where

        dirPartyLocation.Location == this.Location &&

        dirPartyLocation.IsPrimary == NoYes::Yes

      join custTable where

        custTable.Party == dirPartyLocation.Party

    {        

        exportQueue.clear();

        exportQueue.Action      = AifDocumentOperationType::Update;

        exportQueue.RefRecID    = custTable.RecId;

        exportQueue.RefTableId  = custTable.TableId;

        exportQueue.Identifier  = custTable.AccountNum;

        exportQueue.insert();

    }

 

 

 

 

To get the changed records, join the Queue table back into the query for customer and address, while the records are being read they can also be deleted from the queue.

 

static void getCustomerChangesTRG(Args _args)

{

    SWM_ExportQueue         exportQueue;

    CustTable               custTable;

    DirPartyLocation        dirPartyLocation;

    LogisticsLocation       logisticsLocation;

    LogisticsPostalAddress  logisticsPostalAddress;

    FromDateTime            fromDateTime = datetimeUtil::addDays(datetimeUtil::getSystemDateTime(), -1);

    ;

 

    

    ttsbegin;

    while select forUpdate exportQueue 

        join custTable where

            custTable.RecId == exportQueue.RefRecID &&

            custTable.TableId == exportQueue.TableId

        join dirPartyLocation where

            dirPartyLocation.Party                  == custTable.Party &&

            dirPartyLocation.IsPrimary              == NoYes::Yes

        join logisticsPostalAddress where

            logisticsPostalAddress.Location         == dirPartyLocation.Location

    {

        info(strFmt("%1, %2", custTable.AccountNum, logisticsPostalAddress.Address));

        exportQueue.delete();

    }

 

// Additional query to export any records that have been deleted:

 

    while select forUpdate exportQueue where    

        exportQueue.TableId == tableNum(custTable) &&

        exportQueue.Action == AifDocumentOperationType::Delete

    {

        info(strFmt("%1, %2", exportQueue.Identifier, "Deleted"));

        exportQueue.delete();

    }

    ttscommit;

 

}

 

 

 

Conclusion

There are a few other methods that can be used that I have not mentioned here.  The final question though is which method is best?  I prefer the ModifiedDateTime tracking method because it requires the least amount of changes to the system.  

 

 

Please reload

RECENT POSTS:
Please reload

© 2015 by Stephen Mann

  • LinkedIn Black Round
  • Facebook Black Round
  • emailicon.png