One of the main innovations of the SQL:2011 standards was the broader support for temporal databases. Some of these concepts were adopted peu à peu into the functional scope of the SAP HANA database.
Since SPS 03 HANA 2 supports “system-versioned tables”. With SPS 04 the feature for “application-versioned tables” is added. With these two concepts, bi-temporal versioning can be implemented at the database level without extensive application logic.
The best way to illustrate the new functionality is with an example.
First, we create a table to historize the changes to the data.
CREATE TABLE PartnerHistory(
ID INTEGER,
FirstName VARCHAR(64),
LastName VARCHAR(64),
City VARCHAR(64),
BusinessValidFrom DATE CS_DAYDATE NOT NULL,
BusinessValidTo DATE CS_DAYDATE NOT NULL,
SystemValidFrom LONGDATE CS_LONGDATE NOT NULL,
SystemValidTo LONGDATE CS_LONGDATE NOT Null
);After that, we create the actual database table that holds the most recent records.
CREATE TABLE Partner(
ID INTEGER,
FirstName VARCHAR(64),
LastName VARCHAR(64),
City VARCHAR(64),
BusinessValidFrom DATE CS_DAYDATE NOT NULL,
BusinessValidTo DATE CS_DAYDATE NOT NULL,
SystemValidFrom LONGDATE CS_LONGDATE NOT NULL GENERATED ALWAYS AS ROW START,
SystemValidTo LONGDATE CS_LONGDATE NOT Null GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME ( SystemValidFrom, SystemValidTo ),
PERIOD FOR APPLICATION_TIME ( BusinessValidFrom, BusinessValidTo ),
PRIMARY KEY( ID, BusinessValidFrom, BusinessValidTo )
)
WITH SYSTEM VERSIONING HISTORY TABLE "PARTNERHISTORY"
;Let’s take a closer look at some of the declarations:
- PERIOD FOR SYSTEM_TIME : Specifying the fields for the system time
- FOR PORTION OF APPLICATION_TIME: Specify the fields for the business validity of a table entry.
- WITH SYSTEM VERSIONING HISTORY TABLE “PARTNERHISTORY
- GENERATED ALWAYS AS ROW START: the system automatically updates the timestamp
- GENERATED ALWAYS AS ROW END: the system automatically updates the timestamp
- WITH SYSTEM VERSIONING HISTORY TABLE xxx: Linking the history table
In the next step, we fill the table with two sample data sets.
INSERT INTO PARTNER SELECT 1, 'Jon', 'Doe', 'San Francisco', TO_DATE('2012-04-23') , TO_DATE('9999-12-31') FROM DUMMY; INSERT INTO PARTNER SELECT 2, 'Max', 'Mustermann', 'Berlin', TO_DATE('2014-03-01') , TO_DATE('9999-12-31') FROM DUMMY;
Partner” table after the first INSERT
We then test what happens during a standard UPDATE.
UPDATE PARTNER SET CITY = 'Stuttgart' WHERE ID = 2;
Partner” table after the first update
PartnerHistory” table after the first update
The actual database table was updated in the “City” column. Automatically the old entry was moved to the history table with updated system timestamp.
Next, we test what happens during an update for a given business period. We achieve this with the addition “FOR PORTION OF APPLICATION_TIME”.
UPDATE PARTNER FOR PORTION OF APPLICATION_TIME FROM '2015-08-20' TO CURRENT_DATE SET CITY = 'Hamburg' WHERE ID = 2;
Partner” table after APPLICATION_PERIOD UPDATE
PartnerHistory” table after APPLICATION_PERIOD UPDATE
As you can see, the “Hamburg” entry is added to the data table in the correct time period. Three entries have been generated from one entry for Max Mustermann. After the end of the validity for the temporary change of location to Hamburg, the entry for Stuttgart applies again.
Of course not only the update or insert works, also a SELECT can be extended by temporal additions. For example, if we look at a specific system stamp period, the correct entries are read from the database table and the history table. In the following case the original entries are reconstructed after the first INSERT.
SELECT * FROM PARTNER FOR SYSTEM_TIME FROM '2010-04-17 19:48:09.853765500' TO '2019-04-17 19:48:09.853765500';
Corresponding SELECT * FROM <table> FOR APPLICATION_TIME AS OF ‘<timestamp> or a combination with the system time are also possible. Here we refer to the HANA SQL Reference for further details.
In HANA XSA HDI containers, the corresponding features can be represented by the CDS files with the extensions .hdbapplicationtime and .hdbsystemversioning can be used.
With regard to SAP Financial Services Data Management (FSDM), it will be exciting to see whether SAP development will adapt the read/write interfaces to the new SPS 04 functions.
More references about temporal tables in HANA can be found at the given link.
One thought on “SAP HANA 2.0 SPS 04 – Bi-temporal Versioning”.
Pingback: FSDM Data Management: SAP PowerDesigner and SAP EAD as a Duo
0 Comments