History Preserving with precise timestamps

SAP Data Services’ HistoryPreserving transform does a good job to reduce complexity of history preserving when loading data into a data warehouse. However, it has the limitation that ValidFrom and ValidTo columns can only be a date – not a timestamp. So to allow for history preserving of intra-day changes, we need a workaround. In this blog post I’m showing how this can be achieved by using the Map_Operation transform multiple times:

The dataflow shown above loads table CUSTOMER to table CUSTOMERHIST. CUSTOMER has 3 columns (PK INTEGER, FirstName VARCHAR(100), LastName VARCHAR(100)) with PK being the primary key. CUSTOMERHIST has two more columns ValidFrom, ValidTo both of type TIMESTAMP; Its primary key is (PK, ValidFrom). We also need to set a variable $CurDateTime at the beginning of the job to use the exact same timestamp in UPDATEs and INSERTs:

$CurDateTime = concat_date_time(sysdate(), systime());

The TableComparison transform looks up incoming records in CUSTOMERHIST that have the same value in field PK and where ValidTo is null (compare to current records only). In this example we also enable “Detect deleted row(s) from comparison table”.

TableComparison outputs rows of type INSERT, UPDATE and DELETE. We are multiplying this output and send it to Map_Operation transforms: one for INSERTs (I2I), two for UPDATEs (U2U, U2I) and one for DELETEs (D2U). In the “Map Operation” tab of each Map_Operation transform we configure the output type of the record; we discard the records that are handled by the other Map_Operation transforms:

Now we set the column mapping for each case:

I2I:

If TableComparision sends an INSERT, there’s not much todo. We keep the values as they are and just set the ValidFrom column to $CurDateTime

U2U:

If TableComparision sends an UPDATE, the first action is to set ValidTo to $CurDateTime since this record is not the current one anymore. We want to keep FirstName, LastName as it is in CUSTOMERHIST and do not overwrite it with the new values from CUSTOMER, so we need to map FirstName, LastName to before_image(FirstName), before_image(LastName), resp.

U2I:

If TableComparision sends an UPDATE, the second action is to insert a new record with the current values from CUSTOMER. As I2I above, we just need to set ValidFromto $CurDateTime. Note that we need to change the update section (middle), not the insert/normal section (left).

D2U:

If TableComparision sends a DELETE, we need to update the current record by setting ValidTo to $CurDateTime.

With this configuration, the four Map_Operation transforms together replace one HistoryPreserving transform. The example does not include an ISCURRENT column but it should be straight forward to do this enhancement. If there is a generated key column in CUSTOMERHIST, this could be populated using the KeyGeneration transform after merging U2I and I2I. The picture below show the status of both tables after inserting, updating deleting a record in table CUSTOMER:

History preserving after INSERT of record 4711:

History preserving after UPDATE of FirstName to ‘John’:

History preserving after UPDATE of FirstName to ‘John D.’:

History preserving after DELETE of record 4711: