Search

Temporal Data

Get support for date-effective entities with application-controlled validity time slices.

Temporal data allow maintaining 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 staying conceptual, concise, and comprehensive, and minimize pollution by technical artifacts.

For an introduction to this topic, please refer to the articles Temporal database (Wikipedia) and Temporal features in SQL:2011.

Content

Starting with ‘Timeless’ Models

For the following explanation, let’s start with a base model to manage employees and their work assignments, which are 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 many WorkAssignments on jobs.empl=$self;
  job1 : Association to one /*of*/ WorkAssignments;
}

entity WorkAssignments {
  key ID  : UUID;
  role    : String(111);
  empl    : Association to Employees;
  dept    : Association to Departments;
}

entity Departments {
  key ID  : UUID;
  name    : String(111);
  head    : Association to Employees;
  members : Association to many Employees 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 the previous mentioned model, which captures the latest state only can look like this:

Italic titles indicate to-one associations; actual names of the respective foreign key columns in SQL are job1_ID, empl_ID, and dept_ID.

Declaring Temporal Entities

Temporal Entities represent logical records of information for which we track changes over time by recording each change as individual time slices in the database with valid from/to boundaries. For example, we could track the changes of Alice’s primary work assignment WA1 over time:

Validity periods are expected to be nonoverlapping and closed-open intervals; same as in to SQL:2011.

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;
}

The annotation pair @cds.valid.from/to actually triggers the built-in mechanisms for serving temporal data. It specifies which elements form the application-time period, similar to SQL:2011.

Using Common Aspect temporal

Alternatively, use the predefined aspect temporal to declare temporal entities:

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

Aspect temporal 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 whole WorkAssignment 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 nontemporal ones:

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

The data situation would change as follows:

Serving Temporal Data

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

service.cds

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 like WorkAssignments from the service, as they would get auto-exposed automatically.

Reading Temporal Data

As-of-now Queries

READ requests without specifying any temporal query parameter will automatically return data valid as 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 hence the respective session variables would be set to, for example:

$at.from = session_context(‘valid-from’) = 2019-03-08T22:11:00Z
$at.to = session_context(‘valid-to’) = 2019-03-08T22:11:00.001Z

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

Time-travel queries aren’t supported on SQLite due to the lack of session_context variables.

We can run the same OData query as in the prvious sample to read a snapshot data as valid on January 1, 2018 using the sap-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:

$at.from = session_context(‘valid-from’) = 2017-01-01T00:00:00Z
$at.to = session_context(‘valid-to’) = 2017-01-01T00:00:00.001Z

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

Time-period queries aren’t supported on SQLite due to the lack of session_context variables.

We can run the same OData query as in the previous sample to read all history of data as valid since 2017 using the sap-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 add validFrom 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 expanding across two or more temporal data entities.

As an example, assumed both, WorkAssignments as well as Departments shall be temporal:

using { temporal } from '@sap/cds/common';
entity WorkAssignments : temporal {/*...*/
  dept : Association to Departments;
}
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 for Alice 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 timeframe, 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 to Departments;
  dept1 : Association to Departments on dept1.id = dept.id
    and dept1.validFrom <= validFrom and validFrom < dept1.validTo;
}
entity Departments : temporal {/*...*/}

Primary Keys of Time Slices

While the timeless entities are uniquely identified by the declared primary key — we call that the conceptual key in CDS — time slices are uniquely identified by the conceptual key + validFrom.

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

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

The same would apply for exposed entity types OData EDMX models:

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

Reading an explicit time slice can look like this:

SELECT from WorkAssignments WHERE ID='WA1' and validFrom='2017-01-01'

Similarly, referring to individual time slices by an association:

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

Adding Time Slice IDs

In alternative of using enhanced keys, you can introduce a new element to uniquely identify time slices and use that in references:

entity WorkAssignments : temporal { //...
  key ID : UUID;                // logical record ID
  slID : UUID @cds.valid.key;   // time slice ID
}

The annotation @cds.valid.key has precedence over standard keys with respect to generation of SQL and OData output:

CREATE TABLE com_acme_hr_WorkAssignments (
  ...
  PRIMARY KEY ( slID )
  UNIQUE ( ID, validFrom )
)

OData EDMX:

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

In addition, we would add the ID + validFrom combination as alternate keys to the OData model:

<Annotations Target="wbs.WorkAssignments">
  <Annotation Term="Core.AlternateKeys">
    <Collection>
      <Record Type="Core.AlternateKey">
        <PropertyValue Property="Key">
          <Collection>
            <Record Type="Core.PropertyRef">
              <PropertyValue Property="Name" PropertyPath="ID" />
              <PropertyValue Property="Alias" String="ID" />
            </Record>
            <Record Type="Core.PropertyRef">
              <PropertyValue Property="Name" PropertyPath="validFrom" />
              <PropertyValue Property="Alias" String="validFrom" />
            </Record>
          </Collection>
        </PropertyValue>
      </Record>
    </Collection>
  </Annotation>
</Annotations>