For various reporting, BI, or data warehousing scenarios you often need a way to export and synchronize data between an Acumatica instance and your solution. OData API is a good fit for read-only scenarios, and it is widely used by different ISV solutions and external BI services such as Power BI. But reading the whole entity set every time when you need fresh data is very resource-consuming, so the preferred way is to use delta sync.
What Is Delta Synchronization
Delta (Differential) sync is a type of synchronization technology that only synchronizes newly created or updated records. It drastically reduces the amount of data passed between an Acumatica instance and your solution. In a typical scenario, you initially export all the data to your system and after that use delta sync to synchronize the changes.
How to Use It
In Acumatica, the primary field that can be used for delta synchronization is LastModifiedDateTime. It exists in most of the DACs. To perform delta sync, add this field to the corresponding Generic Inquiry and query the data using a condition on this field. In OData, it will be named LastModifiedOn.
https://demo.acumatica.com/odata/BI-Customers?$filter=LastModifiedOn gt datetime'2019-01-01T00:00:00.000'
Some DACs (e.g. GLHistory) don’t have LastModifiedDateTime field. In this case, you can use a timestamp field called tstamp. Basically, it is an incremental 8-byte value that represents a version of the record. This field is used for optimistic transaction locking model, but we can also use it for delta sync.
The timestamp field is not shown in the drop-down list of fields on the Results tab in Generic Inquiry Designer, so you need to add it to your Generic Inquiry by manually typing tstamp as a data field name.
Let’s query the data and see what we’ve got:
As you can see, the timestamp field has been returned from the server, but, according to the OData specification, it is Base64-encoded.
To use these values in the filter, you need to decode it back to 8-byte form and convert to the hex representation:
https://demo.acumatica.com/odata/BI-GLHistory?$filter=tstamp gt binary'0000000000075BA3'
Another option that allows you to avoid Base64-related operations is to retrieve timestamp value from a single entity as raw bytes by using $value syntax:
After that, you can simply convert it to the hex representation and use in the query filter.
LastModifiedDateTime and tstamp fields only make sense for tracking inserted / updated records. Right now, the only way to track record deletion is by using Push Notifications. Please see I300 Data Retrieval: Basic training course for more details.
There is an item on feedback.acumatica.com about adding a possibility to retrieve deleted records using Generic Inquiries and OData, please vote for it if you need this functionality.
As you may now, LastModifiedDateTime and tstamp fields are not indexed by SQL, so making queries with a filter on these two fields can result in high CPU load on SQL server and performance degradation. If you’re planning to use delta synchronization, consider adding a custom index for each of these fields (but, like any other index, it affects the performance for insert / update / delete operations).
Another option is to use Push Notification. For some scenarios, it will be much faster, especially if you need near-real-time synchronization, and amount of inserted / updated / deleted records per day is not too large.