Enabling Change Tracking¶

Change Tracking and Delta Links¶

Client applications may utilize OData change tracking with delta links to restrict downloads to data that has changed since a previous download.

If the generated OData service is to be consumed by occasionally-connected mobile clients, together with CloudSyncProvider or OfflineODataProvider in one of the SAP Business Technology Platform client SDKs, then it is highly beneficial for data synchronization performance to enable change tracking.

In particular, the performance of delta downloads, where the client wishes to download only the back-end data changes since the previous download, can be expected to be achieved in seconds if change tracking is enabled, compared with minutes to hours if change tracking is not enabled (assuming there was a high volume of data in the initial download).

You can enable change tracking for all entity sets in an entity container or for individual entity types or entity sets. Use the SQL.TrackChanges annotation term within the appropriate element, such as EntityContainer, EntityType, or EntitySet, to achieve this.

Example:

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://docs.oasis-open.org/odata/ns/edmx http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edmx.xsd http://docs.oasis-open.org/odata/ns/edm http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edm.xsd">
    <edmx:Reference Uri="vocabularies/com.sap.cloud.server.odata.sql.v1.xml">
        <edmx:Include Namespace="com.sap.cloud.server.odata.sql.v1" Alias="SQL"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema Namespace="example" xmlns="http://docs.oasis-open.org/odata/ns/edm" Alias="Self">
            <!-- entity types omitted for brevity ... -->
            <EntityContainer Name="ExampleService">
                <Annotation Term="SQL.TrackChanges"/>
                <!-- entity sets omitted for brevity ... -->
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Note

For OData version 4.0, enabling change tracking at the server is not sufficient. Clients should also specify the Prefer: odata.track-changes header to ensure that the server will perform change tracking for individual requests.

Tip

If all entity sets within a schema should be enabled for change tracking, it is recommended to apply the SQL.TrackChanges annotation term to the EntityContainer element.

Change Tracking With `$filter`¶

When a client's queries specify the $filter System Query Option, especially for a complex $filter expression, it can sometimes be extremely difficult for an OData service to correctly respond with a minimal set of changes in response to a delta link query.

In particular, if the server does not know whether a client has previously downloaded some entities, and those entities do not currently match the client's selection criteria (as expressed in the $filter), upon the client's next download request the server may decide to return deleted-entity objects (also known as tombstones) in the delta response to ensure that the client (after download) will not retain any reference to those entities. Some of the deleted-entity objects may be unnecessary from the client's point of view. As a result the network utilization for transmission of the delta response, or the subsequent client-side resource utilization for processing of the delta response, may be greater than is desired.

Filtering on Immutable Properties¶

If a client specifies $filter where all referenced properties are immutable, and the filter does not use the OData now() function, then the client's downloads should be optimal.

When a client specifies $filter and some referenced properties are immutable, the efficiency of client downloads depends on whether the "main" filtering properties are immutable. For instance, filtering user-specific tasks with Tasks?$filter=UserID eq 123 and Status eq 'Open' can be quite optimal if UserID (the "main" filtering property) is immutable and Status is mutable. However, if UserID is mutable and there are numerous tasks and users in the database, downloads might be less efficient.

All key properties are treated as immutable.

Any properties annotated with the OData standard Core.Immutable annotation term are treated as immutable. For example, the StoreID property for an inventory entity might be immutable.

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://docs.oasis-open.org/odata/ns/edmx http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edmx.xsd http://docs.oasis-open.org/odata/ns/edm http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edm.xsd">
    <edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
        <edmx:Include Namespace="Org.OData.Core.V1" Alias="Core"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema Namespace="example" xmlns="http://docs.oasis-open.org/odata/ns/edm" Alias="Self">
            <EntityType Name="Inventory">
                <Key>
                    <PropertyRef Name="ClientID"/>
                </Key>
                <Property Name="InventoryID" Type="Edm.Int64" Nullable="false"/>
                <Property Name="StoreID" Type="Edm.Int64" Nullable="false">
                    <Annotation Term="Core.Immutable"/>
                </Property>
                ...
            </EntityType>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Tip

It is recommended to use the Core.Immutable annotation term wherever applicable to immutable properties that are used in $filter queries with change tracking, since it enables optimal server-side processing of change tracking queries with minimal cost.

Caution

Mistakenly using the Core.Immutable annotation term on mutable properties may result in incorrect delta responses, where necessary deleted-entity objects are omitted.

Filtering on Mutable Properties¶

When clients specify $filter and some referenced properties are mutable, or the "main" filtering properties are mutable, or the filter uses the OData now() function, servers might return unnecessary deleted-entity objects in delta responses.

This is actually the main reason why implementing correct and efficient change tracking is challenging.

To understand the process, imagine a system that assigns tasks to users daily. Each user receives 1,000 tasks weekly, which breaks down to 200 tasks per day over five business days. When a task is deleted or no longer matches some users' filters due to reassignment, a deleted-entity object must be sent to every user who might have previously downloaded that task. If the server doesn't track which rows were sent to which clients, it can't determine whether the deleted-entity object needs to be sent.

If 200 tasks are deleted each business day for each of 1000 users, then 200 * 1,000 = 200,000 deleted-entity objects must be sent to every client daily. Across all users, this results in 200 million deleted-entity objects being sent to clients each day.

To reduce or eliminate unnecessary deleted-entity objects in delta responses, which significantly decreases network utilization and client-side processing, consider using one of the following techniques.

Tracking on Individual Properties.
Optimizing With Download Tracking.

Tracking on Individual Properties¶

In some scenarios you can improve the accuracy of delta calculation by tracking changes on individual properties. This is specified by annotating a Property with SQL.TrackChanges.

Individual property change tracking can reduce download sizes when the following two conditions are met.

The property is updated less frequently than other properties in the entity.
You query a different entity set using a filter that navigates to the property.

For example, if you have the a query like the following then adding individual tracking to the Name property would provide a benefit.

Orders?$filter=Customer/Name eq 'Bob'

Delta responses will be calculated more efficiently for the above query if you apply SQL.TrackChanges to the Name property on the Customer entity type. Otherwise any change to a customer entity could cause unnecessary re-downloading of orders related to the customer. The Name column would be rarely changed so adding tracking to it could reduce download times.

Note

It is not necessary to enable change tracking on individual properties if you will use Download Tracking (described below).

Optimizing With Download Tracking¶

Use the SQL.TrackDownloads annotation term to enable the download tracking optimization. This allows the server to remember the keys of all entities sent to each client.

Download tracking can give more precise delta responses (avoid unnecessary deleted-entity objects), although it also requires the use of Client Registrations.
Download tracking results in per-client retention of downloaded primary keys in the database (using a compressed encoding for efficiency), which will be automatically cleaned up when appropriate by Registration Expiry.
You can enable download tracking for all entity sets in an entity container or for individual entity types or entity sets. To do this, use the SQL.TrackDownloads annotation term within the appropriate element, such as EntityContainer, EntityType, or EntitySet.

Example:

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://docs.oasis-open.org/odata/ns/edmx http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edmx.xsd http://docs.oasis-open.org/odata/ns/edm http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edm.xsd">
    <edmx:Reference Uri="vocabularies/com.sap.cloud.server.odata.sql.v1.xml">
        <edmx:Include Namespace="com.sap.cloud.server.odata.sql.v1" Alias="SQL"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema Namespace="my.schema" Alias="Self" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            ...
            <EntityContainer Name="MyService">
                <Annotation Term="SQL.TrackChanges"/>
                <Annotation Term="SQL.TrackDownloads"/>
                ...
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Complex Selection With Download Queries¶

The SQL.DownloadQuery term allows for complex selection criteria to be expressed at the server without clients having to use $filter in their queries. This is valuable if the desired selection criteria are too complex to be expressed using $filter. It also allows the server to restrict visibility of entities to clients for security purposes, whereas clients using $filter may be able to access any data in the entity set.

The simplest form of SQL.DownloadQuery is a query only involving the entity type within which the annotation is placed. A query is expressed using the form select entity.* from EntityType entity. Although this query is expressed using SQL syntax, the "table names" should actually be entity type names, and any "column names" should be entity property names. Thus it is a SQL query expressed using the CSDL entity schema, rather than the underlying SQL table schema.

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String>
            select entity.* from Customer entity
        </String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

Note

The SQL.DownloadQuery term can only be used if Download Tracking is enabled.

Note

For a given entity set (or its entity type), either the server does filtering using SQL.DownloadQuery (when specified in the metadata), or the client can use $filter, but both cannot be used together.

Download Queries Using Filter Entities¶

You can express complex selection criteria referencing filter entities in the SQL select statement. The idea is to define one or more extra filter entity types that express a client's selection criteria, and define the download query to join the main entity type with the filter entity type(s).

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String>
            select entity.* from Customer entity, RegionFilter filter
            where entity.RegionID = filter.RegionID
        </String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

A filter entity type should define a FilterID key property with type Edm.Int64, as well as using the SQL.ClientFilter term to indicate that the filter is client-specific. The filter entity type should also define one extra property for each value that is required to express the client's selection criteria.

Example:

<EntityType Name="RegionFilter">
    <Annotation Term="SQL.ClientFilter"/>
    <Key>
        <PropertyRef Name="FilterID"/>
    </Key>
    <Property Name="FilterID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    ...
</EntityType>

Note

The SQL where clauses for comparison of main entities with filter entities are not limited to using the SQL equality operator ("="); any SQL operator or function can be used so long as it is supported by the SQL dialect of the database server.

Download Queries Using Other Entities¶

Download queries can also refer to other (non-filter) entity types in the SQL select statement. In the following example, the download query for Order refers to a filter entity type RegionFilter, and also refers to non-filter entity type Customer.

Example:

<EntityType Name="Order">
    <Annotation Term="SQL.DownloadQuery">
        <String>
            select entity.* from Order entity, Customer customer, RegionFilter filter
            where entity.CustomerID = customer.CustomerID
            and customer.RegionID = filter.RegionID
        </String>
    </Annotation>
    <Key>
        <PropertyRef Name="OrderID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="OrderID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    ...
</EntityType>

Download Queries Using Union Clauses¶

For more complex requirements, the download query can be multipart using SQL union clauses.

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String>
            select entity.* from Customer entity, RegionFilter filter where entity.RegionID = filter.RegionID
            union
            select entity.* from Customer entity, NameFilter filter where entity.Name = filter.Name
        </String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

Download Queries Using Correlated Subqueries¶

Download queries can also use SQL correlated subqueries. If a download query can be expressed with SQL union clauses and also with SQL correlated subqueries, then SQL union clauses should be used as a first preference, as it is likely to result in better download performance.

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String>
            select entity.* from Customer entity
            where exists (select filter.RegionID from RegionFilter filter where entity.RegionID = filter.RegionID)
            or exists (select filter.Name from NameFilter filter where entity.Name = filter.Name)
        </String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

Inheriting Download Queries From a Parent Entity¶

For a child entity type in a parent/child relationship where the parent entity type is configured with a download query, the child entity type can be defined to inherit the parent's download query by using the SQL.InheritDownloadQuery term, linking with a navigation property that refers to the parent entity. For example, with a parent Customer entity type and a child Order entity type, if clients wish to download all orders that are related to customers according to the download query for the Customer entity type, then the Order entity type could be defined as follows.

<EntityType Name="Order">
    <Annotation Term="SQL.InheritDownloadQuery" NavigationPropertyPath="Customer"/>
    <Key>
        <PropertyRef Name="OrderID"/>
    </Key>
    <Property Name="OrderID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <NavigationProperty Name="Customer" Type="Self.Customer" Nullable="false" Partner="Orders">
        <ReferentialConstraint Property="CustomerID" ReferencedProperty="CustomerID"/>
    </NavigationProperty>
    ...
</EntityType>

Referencing the Authorized User in Download Queries¶

Download queries can refer to :client.AuthorizedUser to limit matching data to current users. For example, this restricts employees to downloading only their own employee data.

select entity.* from Employee entity
where entity.UserEmail = :client.AuthorizedUser

The generated ClientRegistrationHandler class includes code to populate the registration with its AuthorizedUser. You might need to customize the createEntity method in that handler class if you want to track the authorized user using a method other than HttpServletRequest.getUserPrincipal.

Last update: May 19, 2025

Enabling Change Tracking¶

Change Tracking and Delta Links¶

Change Tracking With $filter¶

Filtering on Immutable Properties¶

Filtering on Mutable Properties¶

Tracking on Individual Properties¶

Optimizing With Download Tracking¶

Complex Selection With Download Queries¶

Download Queries Using Filter Entities¶

Download Queries Using Other Entities¶

Download Queries Using Union Clauses¶

Download Queries Using Correlated Subqueries¶

Inheriting Download Queries From a Parent Entity¶

Referencing the Authorized User in Download Queries¶

Change Tracking With `$filter`¶