Skip to Content
Technical Articles
Author's profile photo Naveen Taj

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.

 

 

 

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Nicola Feldmer
      Nicola Feldmer

      Hi Naveen,

      thanks for your great blog post!
      Do you know any recommended way to read historical data via an OData service when using system versioned tables?
      For example, i'd like to read the data which was valid at "2021-05-31 09:30:00"

      Thanks in advance and best regards!

      Nicola

      Author's profile photo Tobias Tauchen
      Tobias Tauchen

      Thank you for the create blog. With the later versions of CAP ~5.30 this does not work anymore if draft mode is enabled. Saving a draft will result in a insert statement trying to insert null into VALID_FROM & TO.

       

      Workaround for me is this:

          srv.before("INSERT", "*", async (req) => {
              delete req.data.VALID_FROM;
              delete req.data.VALID_TO;
          });
      
          srv.before("UPDATE", "*", async (req) => {
              delete req.data.VALID_FROM;
              delete req.data.VALID_TO;
          });
      Happy to see a better solution if you find one.
      Author's profile photo Abe Dong
      Abe Dong

      Hi Tobias, since you said you worked on entity with draft, would like to know if you have answer for the question below?

      When you post the request, without any handler logic, the records will be posted to the draft tables instead of active tables? Is there a way to post data to the active table directly from POSTMAN?

      https://cap.cloud.sap/docs/java/fiori-drafts  It looks possible, but need some work.

       

      Thank you.

      Author's profile photo Tobias Tauchen
      Tobias Tauchen

      Hi,

      never done it, and currently i am working with the nodejs version.

      I heard it is very well possible with java and not so much in nodejs from a different project in my company.

      Might have changed by now.

      Sorry for letting you down.

      Cheers.