Temporal Data​

CAP provides out-of-the-box support for declaring and serving date-effective entities with application-controlled validity, in particular to serve as-of-now and time-travel queries.

Temporal data allows you to maintain information relating to past, present, and future application time. Built-in support for temporal data follows the general principle of CDS to capture intent with models while staying conceptual, concise, and comprehensive, and minimizing pollution by technical artifacts.

For an introduction to this topic, seeTemporal database (Wikipedia) andTemporal features in SQL:2011.

Starting with 'Timeless' Models​

For the following explanation, let's start with a base model to manage employees and their work assignments, which is free of any traces of temporal data management.

Timeless Model​

namespace com.acme.hr;using {com.acme.common.Persons }from './common';entity Employees : Persons {  jobs : Composition of manyWorkAssignments on jobs.empl=$self;  job1 : Association toone /*of*/ WorkAssignments;}entity WorkAssignments {  keyID  : UUID;  role    : String(111);  empl    : Association toEmployees;  dept    : Association toDepartments;}entity Departments {  keyID  : UUID;  name    : String(111);  head    : Association toEmployees;  members : Association to manyEmployees on members.jobs.dept = $self;}

An employee can have several work assignments at the same time. Each work assignment links to one department.

Timeless Data​

A set of sample data entries for this model, which only captures the latest state, can look like this:

Italic titles indicate to-one associations; actual names of the respective foreign key columns in SQL arejob1_ID,empl_ID, anddept_ID.

Declaring Temporal Entities​

Temporal Entities representlogical records of information for which we track changes over time by recording each change as individualtime slices in the database with valid from/to boundaries. For example, we could track the changes of Alice's primary work assignmentWA1 over time:

Using Annotations@cds.valid.from/to​

To track temporal data, just add a pair of date/time elements to the respective entities annotated with@cds.valid.from/to, as follows:

entity WorkAssignments {//...  start : Date@cds.valid.from;  end   : Date@cds.valid.to;}

Using Common Aspecttemporal​

Alternatively, use the predefined aspecttemporal to declare temporal entities:

using {temporal }from '@sap/cds/common';entity WorkAssignments : temporal {/*...*/}

Aspecttemporal is defined in@sap/cds/common as follows:

aspect temporal {  validFrom : Timestamp@cds.valid.from;  validTo   : Timestamp@cds.valid.to;}

Separate Temporal Details​

The previous samples would turn the wholeWorkAssignment entity into a temporal one. Frequently though, only some parts of an entity are temporal, while others stay timeless. You can reflect this by separating temporal elements from non-temporal ones:

entity WorkAssignments {// non-temporal head entity  key ID  : UUID;  empl    : Association toEmployees;  details : Composition ofWorkDetails on details.ID = $self.ID;}entity WorkDetails : temporal {// temporal details entity  key ID  : UUID;// logical record ID  role    : String(111);  dept    : Association toDepartments;}

The data situation would change as follows:

Serving Temporal Data​

We expose the entities from the following timeless model in a service as follows:

using {com.acme.hr }from './temporal-model';service HRService {  entity Employees as projection on hr.Employees;  entity WorkAssignments as projection on hr.WorkAssignments;  entity Departments as projection on hr.Departments;}

You can omit composed entities likeWorkAssignments from the service, as they would getauto-exposed automatically.

Reading Temporal Data​

As-of-now Queries​

READ requests without specifying any temporal query parameter will automatically return data validas of now.

For example, assumed the following OData query to read all employees with their current work assignments is processed on March 2019:

GET Employees?$expand=jobs($select=role&$expand=dept($select=name))

The values of$at, and so also the respective session variables, would be set to, for example:

The result set would be:

[  {"ID":"E1","name":"Alice","jobs": [    {"role":"Architect","dept": {"name":"Core Development"}},    {"role":"Consultant","dept": {"name":"App Development"}}  ]},  {"ID":"E2","name":"Bob","jobs": [    {"role":"Builder","dept": {"name":"Construction"}}  ]}]

Time-Travel Queries​

We can run the same OData query as in the previous sample to read a snapshot data as valid on January 1, 2017 using thesap-valid-at query parameter:

GET Employees?sap-valid-at=date'2017-01-01'$expand=jobs($select=role&$expand=dept($select=name))

The values of$at and hence the respective session variables would be set to, for example:

The result set would be:

[    {"ID":"E1","name":"Alice","jobs": [      {"role":"Developer","dept": {"name":"Core Development"}},      {"role":"Consultant","dept": {"name":"App Development"}}    ]},...]

Time-Period Queries​

We can run the same OData query as in the previous sample to read all history of data as valid since 2016 using thesap-valid-from query parameter:

GET Employees?sap-valid-from=date'2016-01-01'$expand=jobs($select=role&$expand=dept($select=name))

The result set would be:

[  {"ID":"E1","name":"Alice","jobs": [    {"role":"Developer","dept": {"name":"App Development"}},    {"role":"Developer","dept": {"name":"Core Development"}},    {"role":"Senior Developer","dept": {"name":"Core Development"}},    {"role":"Consultant","dept": {"name":"App Development"}}  ]},...]

You would addvalidFrom in such time-period queries, for example:

GET Employees?sap-valid-from=date'2016-01-01'$expand=jobs($select=validFrom,role,dept/name)

Transitive Temporal Data​

The basic techniques and built-in support for reading temporal data serves all possible use cases with respect to as-of-now and time-travel queries. Special care has to be taken though if time-period queries transitively expand across two or more temporal data entities.

As an example, assume that both,WorkAssignments andDepartments are temporal:

using {temporal }from '@sap/cds/common';entity WorkAssignments : temporal {/*...*/  dept : Association toDepartments;}entity Departments : temporal {/*...*/}

When reading employees with all history since 2016, for example:

GET Employees?sap-valid-from=date'2016-01-01'$expand=jobs(  $select=validFrom,role&$expand=dept(    $select=validFrom,name  ))

The results forAlice would be:

[  {"ID":"E1","name":"Alice","jobs": [    {"validFrom":"2014-01-01","role":"Developer","dept": [      {"validFrom":"2013-04-01","name":"App Development"}    ]},    {"validFrom":"2017-01-01","role":"Consultant","dept": [      {"validFrom":"2013-04-01","name":"App Development"}    ]},    {"validFrom":"2017-01-01","role":"Developer","dept": [      {"validFrom":"2014-01-01","name":"Tech Platform Dev"},      {"validFrom":"2017-07-01","name":"Core Development"}    ]},    {"validFrom":"2017-04-01","role":"Senior Developer","dept": [      {"validFrom":"2014-01-01","name":"Tech Platform Dev"},      {"validFrom":"2017-07-01","name":"Core Development"}    ]},    {"validFrom":"2018-09-15","role":"Architect","dept": [      {"validFrom":"2014-01-01","name":"Tech Platform Dev"},      {"validFrom":"2017-07-01","name":"Core Development"}    ]}  ]},...]

That is, all-time slices for changes to departments since 2016 are repeated for each time slice of work assignments in that time frame, which is a confusing and redundant piece of information. You can fix this by adding an alternative association to departments as follows:

using {temporal }from '@sap/cds/common';entity WorkAssignments : temporal {/*...*/  dept : Association toDepartments;  dept1 : Association toDepartments on dept1.id = dept.id    and dept1.validFrom <= validFrom and validFrom < dept1.validTo;}entity Departments : temporal {/*...*/}

Primary Keys of Time Slices​

While timeless entities are uniquely identified by the declared primarykey — we call that theconceptual key in CDS — time slices are uniquely identified bythe conceptualkey+validFrom.

In effect the SQL DDL statement for theWorkAssignments would look like this:

CREATE TABLE com_acme_hr_WorkAssignments (    ID :nvarchar(36),    validFrom :timestamp,    validTo :timestamp,    -- ...    PRIMARY KEY ( ID, validFrom ))

In contrast to that, the exposed API preserves the timeless view, to easily serve as-of-now and time-travel queries out of the boxas described above:

<EntityType Name="WorkAssignments">  <Key>    <PropertyRef Name="ID"/>  </Key>  ...</EntityType>

Reading an explicit time slice can look like this:

SELECT from WorkAssignmentsWHERE ID='WA1' and validFrom='2017-01-01'

Similarly, referring to individual time slices by an association:

entity SomeSnapshotEntity {  //...  workAssignment : Association toWorkAssignments {ID,validFrom }}