Handling Temporal Data in SAP Cloud Application Programming Model with Hana.

In this post I will explain how to handle temporal data in SAP Cloud Application Programming model  with HANA native artifacts.

Temporal data is time dependent. We can say if there is any timestamp associated with any data set it can be called as temporal. SAP Cloud Application Programming model provides, out of the box support to for temporal entities.

Two ways we can declare entities as temporal.

1.  With annotations @cds.valid.from/to

We can just add these annotations to the date fields to in an entity where temporal data needs to be handled.

entity Product { 
  validFrom : Date @cds.valid.from;
  validTo   : Date @cds.valid.to;
}

2. Declare an entity using aspect  temporal

Using this predefined aspect temporal we can record changes in the database with valid from/to boundaries. Deploying this cds artefacts in the database will create validFrom and validTo timestamp fields in the respective tables.

using { temporal } from '@sap/cds/common';

entity Product : temporal {
    key productId : String(20);
    productName : String(20);
    productCategory : String(20);
}

Read requests on these entities will return valid data as of now without specifying any parameter.

With this approach we can see time slices are recorded in a single table. Many change requests with time, data size in single table will keep on increasing and cause performance issues.

We can consider the alternate approach to achieve this is by HANA System Versioned tables.=

System versioned tables with SAP Cloud Application Programming model :

System versioned tables support the tracking of changes on column store tables by capturing the validity period of each record.

System-versioned tables always consist of two physical tables:

1. The main table of records that are currently valid.
2. A corresponding history table (a one-to-one correspondence) of archived records. A naming convention is not required but may be helpful, for example, to append “_history” to the name of the main table.

The valid_from and valid_to columns are timestamps and are maintained by the system.

Below are the steps to achieve this.

Step 1:

Create a devspace in Business Application Studio selecting full stack cloud project including SAP HANA tools extension. Start the devspace.

Step 2:

Open terminal in the development space. Create an application by running the below command  in the directory : /home/user/projects

mvn -B archetype:generate -DarchetypeArtifactId=cds-services-archetype -DarchetypeGroupId=com.sap.cds \
-DarchetypeVersion=RELEASE \
-DgroupId=com.sap.cap -DartifactId=temporal-cap-service -Dpackage=com.sap.cap

It will download required maven libraries and create a project.  Open created project in explorer.

File-> Open Workspace

Step 3:

Create the file schema.cds under db folder. Define two entities Product and Product_hist giving required field data.

Product entity is for original data where Product_hist is to capture the data which has been modified.

We can mark the fields validFrom and validTo with the annotations – @cds.api.ignore and @assert.notNull in order to make sure these fields are not appeared in the metadata of entity’s odata and we don’t need to pass these fields in the payload while creating.

namespace rdp;

entity Product  {
    key productId : String(20);
    productName : String(20);
    productCategory : String(20);
    productGroupId : String(20);
    @cds.api.ignore
    @assert.notNull: false
    validFrom : Timestamp not null; 
    @cds.api.ignore
    @assert.notNull: false
    validTo : Timestamp not null;
}

entity Product_History  {
    productId : String(20);
    productName : String(20);
    productCategory : String(20);
    productGroupId : String(20);
    validFrom : Timestamp;
    validTo : Timestamp;
}

Step 4:

Create a HANA artifact for systemversioning.

ProductTableVersioning.hdbsystemversioning

SYSTEM VERSIONING "RDP_PRODUCT"("VALIDFROM", "VALIDTO") HISTORY TABLE "RDP_PRODUCT_HISTORY" NOT VALIDATED

The history entity has essentially the same structure as the main entity but must also meet a number of consistency requirements, specifically: the history entity does not have the key.

We need to make sure that in System Versioned HANA artifact file, the database tables and the columns we refer are in Uppercase.

Step 5:

Create corresponding service for the entity.

using rdp from '../db/schema';

service TemporalCapService {
  entity Product as projection on rdp.Product;
}

Deploy the code using MTA configurations giving HANA service instance details.

Once the code is deployed we could see that the  tables are created in HANA DB. And we can have OData services to perform CRUD operations as well.

CRUD Operations on temporal entities:

Create Request: POST

https://www.example.com/v4/TemporalCapService/Product

Request Payload:

{"productId":"test1","productName":"testName1","productCategory":"testCat1","productGroupId":"123"}

Object is created with ValidFrom and ValidTo columns populated time details.

Update Request :PATCH

https://www.example.com/v4/TemporalCapService/Product(‘test1’)

Request Payload:

{"productName":"testName123","productCategory":"testCat","productGroupId":"123"}

We can see that, RDP_PRODUCT table is updated with new productname, where as RDP_PRODUCT_HISTORY has a record which was changed in the original table.

Delete Request:

Deletes the data from DB but the history table will get updated with the data present in the original table.

https://www.example.com/v4/TemporalCapService/Product(‘test1’)

This is all from my side. I hope this article is useful. Thank you.