Change Tracking: a mechanism for applications to efficiently track changes to data in a database. Different types of applications have different requirements for how much information they need about the changes. In general the need for tracking changes becomes clear in answering one of the following two questions about the changes that have been made to a specific table:
- Has a row changed and how did it change?
- Which rows have changed for a specific table?
In the past, to enable applications to query for changes to data in a database and access information about the nature of the changes, application developers had to implement custom mechanisms to track changes. Creating these mechanisms usually was a lot of work and often involved using a combination of triggers, timestamps, new tables to store tracking information, and custom cleanup processes. The good news is that in D365FSCM this feature is available out-of-the-box.
Change Data Capture
We all know for example the Database Logging functionality which already existed in the early versions of Axapta. It is typically used to answer the first question: has a row changed and how did it change exactly? If an application requires historical information about all the changes that were made and the intermediate values of the changed data, you will use this way of tracking changes which is better known as “Change Data Capture”. Database logging is a very powerful security and audit tool and easy to set up. It allows you to determine which tables and fields should be monitored and the events that should trigger the Change Data Capture. Database logging provides a way to capture specific types of changes to tables and fields in D365FSCM, including insert, update, delete and rename key operations. It is for example often used to monitor who made which change to the data and when. Although database logging can be valuable from a business perspective, it can also be expensive as it comes with a price. When you configure logging for a table or field, a record of every change to that table or field is stored in the database log table (SysDatabaseLog) in the environment database. So it is important to keep in mind: there can be large performance implications!
In this blog however, I want to focus on “Change Tracking” in the sense of answering the second question: which rows have changed for a specific table? Here only the fact that rows in a table were changed is required, not how many times the row has changed or the values of any intermediate changes. The latest data can be obtained directly from the tables that are being tracked.
Obviously, this Change Tracking is more limited in the historical questions it can answer compared to Change Data Capture. However, for those scenarios that do not require the historical information, there is far less storage overhead because the changed data is not being captured and there is less overhead to the SQL operations, both resulting in minimal performance impact.
Speaking of SQL, Change Tracking is actually a feature that is provided in SQL Server. It enables the database to track changes, including deletes, that are made to tables as a result of transactions in D365FSCM. Because D365FSCM tracks changes at data entity level, it contains additional logic built on top of the SQL Change Tracking functionality to make things work.
Change Tracking is widely applied at exporting data from D365FSCM using Data Management projects, mainly in Bring Your Own Database (BYOD) scenarios. That is exporting data entities into a Microsoft Azure SQL database as a data warehouse for example to be used for BI reporting purposes.
In the scenario to synchronize your data with external data sources, you can enable specific data entities for Change Tracking. In this way you can export or retrieve a selected set of data, to keep the external data warehouse in sync. By selecting or deselecting Change Tracking for specific data entities you can reduce the load on your server resources and save processing time when extracting data and synchronizing it to an external store. You can enable Change Tracking for both system and custom data entities. Key here is that you create a recurring data job with incremental push. Each time it runs it will export the data that was changed after the last recurrence.
Although it is much easier to export (push) data from D365FSCM, it is also an option to retrieve (pull) record changes through OData virtual entities in a Microsoft Dataverse scenario. In this case changes made in tables can be tracked by sending Web API requests having additionally “odata.track-changes” specified as a preference header. This preference header is used to request that a delta link is being returned which can subsequently be used to retrieve the table changes. I won’t go into details on this, but just want to point out that it is possible.
Configure Change Tracking
To use Change Tracking, you go to the Data entities list page which is accessible via the Data Management workspace. You select a data entity and activate one of the following options to enable the Change Tracking functionality on the selected data entity:
- Enable primary table: Only changes to the primary, top-level table will trigger the data entity change. Note however that the export itself will include all changes, that is both changes to primary and non-primary tables in the data entity.
- Enable entire entity: Any change to any of the tables the data entity consists of will trigger the changes to be applied to the destination database.
- Enable custom query: Only changes to the tables that are added by code to the default Change Tracking query in the data entity will trigger the changed status of the data entity.
After this the data entity is ready to be used in a data export job with the “Incremental push” option activated. When the export job has been scheduled, the first run will automatically export all records of the data entities included in the export job. This automatic initial full push is necessary because SQL needs to know which records have already been tracked in order to be able to track any subsequent changes. The next export runs will all be incremental.
Custom query example
The “Custom query” Change Tracking option allows developers to create a custom query to be run by the system to determine the changes to be tracked. This option is useful when you have a complex requirement to track changes to only a selected set of fields, or when the data entity that will be exported was built by using a hierarchy of nested views. The custom query is defined by code in the data entity, a method named “defaultCTQuery”, and might even include tables which are not part of this data entity.
Let’s take a look at the “Customers V3” entity. Notice that this data entity consists of a lot of joined tables:
You might want that not any change to any of this long list of tables will trigger a change of the data entity as a whole, but for example that only the basic customer details and primary address tables and fields are change tracked. To establish that, you create a custom query in which you only add the tables CustTable, DirPartyTable, DirPartyLocation and LogisticsPostalAddress, making them the ones that are being change tracked. This means that only changes to these specific tables will trigger an export of the “Customers V3” entity records. The custom query method in the data entity extension could then look like this:
As we have seen, the Change Tracking feature in D365FSCM is a very helpful tool to push only the modified data from the tables that are part of the chosen data entities. It provides a reliable way to keep the data in a destination database synchronized in an efficient manner by detecting what data has changed since the data was initially exported or last synchronized. I hope you enjoyed reading this blog and learnt a thing or two. Till next time!