How to build a ‘Rolling-Delta Database’ for Offline Mobile scenarios
Whilst the SAP HANA ‘columnar store’ represents the database revolution of the 2010s, it is clear that even a column-oriented database is unable to play a significant role in the ever-growing number of Offline Mobile scenarios, which have until now been greatly restrained by the absence of an optimized database platform.
Since the release of SAP Mobile Platform (SMP) 3.x in 2013, it has been for the OData Gateway to provide at-call Delta support to mobile devices via the appropriate ‘GET_ENTITYSET_DELTA’ service – deltas no longer being managed as per the SMP 2.x ‘Cache DB’. According to SAP’s latest documentation on ‘Delta Query Support’: “You can implement [OData’s] delta token in several ways and these options can be grouped into two main approaches. With both approaches, the payload of the response is reduced, but only the first approach is also able to optimize the performance in the backend system.
 The first approach calculates deltas at modification time: The ABAP system [Syclo Exchange Framework] tracks relevant changes when they occur. At request time, the deltas are already prepared and thus available…
 The second approach is based on [delta request log] delta determination at request time where the system compares old and new state to find out which records have been changed/deleted. The implementation effort is rather small but it does not optimize the performance of the backend. That means, the more records you have in the full collection, the longer the response time of the request.”
The second of these two approaches is so completely inadequate for large-scale Offline Mobile scenarios, that we shall skip it directly. The first, which is based upon the Syclo Exchange Framework (part of the SAP Agentry Framework since SMP 3.x), requires for each differing mobile scenario: (i) Exchange Framework Implementation, (ii) Exchange Handler, (iii) Exchange Table, and an (iv) Exchange Object. Business Object change-detection in the backend is always to be built using hooks (e.g. Implicit Enhancements, BAdIs) in the corresponding SAP update program – a program, function module, class, ABAP routine, etc. – and results in the corresponding change-context – but NOT the changed field values – being stored in an ‘Exchange Table’ dedicated to that particular Business Object (e.g. Purchase Order); with the peculiarity that a separate entry will be added for each configured ‘mobile application’.
According to SAP, an “Exchange table is not intended to be a history table. Its table contents should be purged regularly… For optimal runtime performance, a secondary index is recommended when defining a new exchange table.” As such, not only is very significant configuration and development effort required to put in place the Syclo Exchange Framework – the likes of which might well be beyond the means of a typical SMB, even before considering new license fees – additional changes are also required in the backend for each new Business Object that is brought offline; with the associated implications for testing and roll-outs, which includes those Business Objects that were already fully managed.
To return to where we started, although the HANA ‘columnar store’ probably represents the database revolution of the 2010s, it’s ‘column-oriented tables’ – which very obviously provide “Higher performance for column operations” – unfortunately offer little benefit for the ever-growing number of Offline Mobile use cases. As such, what is needed is a new database revolution for the 2020s that provides much higher performance for ‘Delta operations’, like those needed for the implementation of each relevant Business Object’s ‘GET_ENTITYSET_DELTA’ OData service: its ODelta service.
According to SAP, “A database table is conceptually a two-dimensional data structure organized in rows and columns…. A table can be represented in row-order or column-order.” As such, it would appear that all of our options for Delta-management have already been exhausted, but in fact they have not been. We simply need to add a third dimension to a classic row-based table: the dimension of time. Here’s an example of the Transactional ODelta Table (that stores multiple – transactional – Entity Types):
As with any Syclo Exchange Table, there can be a CHANGE_FLAG for (I)nsert, (U)pdate, or (D)elete, the difference being there is only one Transactional ODelta Table; with a separate (technically identical) Table being created for Master-Data, which has a much longer life span and is likely to require support for logical rather than physical Deletes (hence no Deletes at all, only Updates). The two necessary Transactional and Master-Data ODelta Tables could – unlike Syclo Exchange Tables – be filled on the basis of standardized, low-cost, unobtrusive jobs polling the relevant database tables/views every n minutes – as opposed to once every n minutes multiplied by the number of mobile devices in the enterprise. The polling frequency would be configured at the level of the Entity Type, as some Entity Types change very rarely (e.g. Master-Data), and some are only used in low-value scenarios (e.g. employee leave requests); meaning demands on the live database could be tailored individually for each Entity Type – unlike the Syclo Exchange Framework which is triggered at each and every Business Object update.
You will see from the above example that Purchase Order 300000000 Item 10 – highlighted in yellow – was (I)nserted on 11/01/2019, (U)pdated on 12/01/2019, and finally (D)eleted on 13/01/2019. So what would have happened during any calls to the Purchase Order Item ‘GET_ENTITYSET_DELTA’ ODelta service on 12/01/2019 using a Delta Timestamp of 10am? The Purchase Order Item ODelta service would read the underlying Transactional ODelta Table – upon the basis of its Primary Key – using this Delta Timestamp (CHANGED_ON = ‘20190112’ and CHANGED_AT GT ‘1000’ and ENTITY_TYPE = ‘PurchaseOrderItem’), and it would instantly identify a change on a given PurchaseOrderItem that occurred at 11:35am of the same day, at almost no DB-cost.
There is an additional precision to make. These two ODelta Tables should be built on and hosted by the SAP Gateway: (1) this would optimize network performance, (2) there would be no need for a read on the backend database with each call to the ODelta service, (3) neither column-based nor traditional row-based tables are designed for delta-scenarios, but they are still needed, so they should be left alone where they already are, (4) this design would not represent unnecessary data redundancy, as ‘Rolling-Delta’ database tables are in no way comparable to column-based or traditional row-based database tables, (5) depending on the relevant Entity Type, the data in the Transactional ODelta Table can have a very limited lifespan, and could be completely removed after only a few months in many Offline Mobile scenarios, and (6) existing Gateway modelling tools (i.e. Transaction SEGW) could be used to provide the ODelta services on the Gateway with the necessary mapping between the String-type ENTITY_KEY value (e.g. ‘03000000000000000010’) – Entity key/data values necessarily being stored in alphabetical order – and the underlying Entity Type structure (see image from SEGW below).
So why ‘Rolling-Delta’? After a certain number of days – different for each Entity Type and the corresponding use cases – a reorganization job would be run that would replace each Entity’s delta records with only the latest delta record, which would become the new reference point (and should therefore mirror what is found in the backend database). Any mobile device requesting a Delta update the following day for example, would get a Delta based solely upon the last record for the relevant Entity, until such a time as that Entity is again changed. Needless to say, this job could be run far less often on the Entity Types of the Master-Data ODelta Table, which does not host at all the same category of data as the Transactional ODelta Table.
One final point must be made. As stated earlier, “Typically an [Syclo] exchange table uses a minimum standard structure which captures only minimum information about the data object that has been changed, such as mobile application, object key, last changed timestamp, change action (Insert/Update/Delete), user”; and not the data that was actually changed. As such, in the Purchase Order Item example provided earlier, where the PO Item Quantity was changed in the backend database from 1 EA to 10 EAs at 11:35am on 12/01/2019, any attempt by a user to send their (offline) 11:15am change of the PO Item Delivery Date to the backend database when next online/connected (e.g. at 11:55am), will be immediately rejected by the backend database as the e-Tag Timestamp provided for that offline user will reveal that a later change was made to the same PO Item record (at 11:35am). So the question becomes: If only the PO Item’s Delivery Date had been changed by the offline user (at 11:15am), and not at all the PO Item Quantity, why should their earlier offline change to the Delivery Date be rejected out-of-hand? Is it because the standard Syclo Exchange Framework does not permit us to perform Field-level Delta comparisons? If that is the case, it is interesting to note that the ‘Rolling-Delta Database’ can easily support such Field-level Delta comparisons, along with other advanced timestamp calculations at Field-level.
Comparing SAP Mobile Platform Version 2.x and 3.x Delta Tracking
Delta Query Support
How To… Enable Delta Queries using Syclo Exchange Framework and SAP NetWeaver Gateway