How to Use Delta Synchronization with OData API

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:

https://demo.acumatica.com/odata/BI-Customers('ABARTENDE')/tstamp/$value

After that, you can simply convert it to the hex representation and use in the query filter.

Possible Pitfalls

Deleted Records

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.

Performance Considerations

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.

7 Replies to “How to Use Delta Synchronization with OData API”

  1. Is it possible to use this method for Delta Synchronization with OData v4?

    ex: https://demo.acumatica.com/ODatav4/Tenant/PX_Objects_GL_Account?$filter=tstamp gt binary’AAAAAAAHW6M=’

    While testing with the 2023R1 release, this error comes back:
    “The query specified in the URI is not valid. A binary operator with incompatible types was detected. Found operand types ‘Edm.Binary’ and ‘Edm.Binary’ for operator kind ‘GreaterThan’.”
    If I use the following filter, a single record comes back:
    https://localacumaticademo/ODatav4/localtenant/PX_Objects_GL_Account?$filter=tstamp eq binary’AAAAAAAHW6M=’

    Thank you

      1. Thanks for the reply.

        Unfortunately we do not have an active support subscription so I can’t submit a support case at this time.

        We have GIs built and exposed via a Web Service that are currently helping us do this type of Delta Synchronization but I wanted to see if re-implementing them as oData v4 queries would be quicker.

        I’ll keep an eye out on future releases to see if anything changes.

        This was an excellent post btw.

  2. Now that Acumatica includes a “Show Deleted Records” checkbox in the GI setup, and you can add the “DeletedDatabaseRecord” field, the GI can now present deleted records as well as “live” records.

    However, in my experience, querying that GI via OData only returns the “live” records.

    Do you know of a way to also retrieve the deleted records via OData?

    1. It is actually a bug that was fixed somewhere in 2020R1 Updates (Update 10 or so). Please try it on the latest version – OData should return deleted records as well.

Leave a Reply

Your email address will not be published. Required fields are marked *