History Preserving Transform in BODS

 This Transformation is used to preserve the history of the Data.

Suppose there is a customer, whose address is changed from NYK to LA. Then, after some time he moved to Texas. If the table is updated with new location or address, all the customer's  past locations will be overwritten. There is a need to preserve the history of customer's location to analyze the old data.

Using the History preserving transformation all the history data can be saved.

To apply the it, you need to have a table comparison done prior to this transformation-

In the above example, first you need to have a target structure with the below specified fields-

S-key (surrogate key) will act as the primary key as you will get duplicate records (saving the past record)

Valid from field will have the valid from date

Valid to field will have the change date

Column_flag will have the indication saying if it’s the current record or the old record.

You have to truncate the target table and make skey (surrogate key) as primary key using the below SQL codes-

truncate table TRGT_CUSTOMER

alter table dbo.TRGT_CUSTOMER add  primary key (skey)

Now the target table is empty and having a surrogate key (Skey) as a primary key.

The query transform is used to add a new field (Valid_from) and populate it with sysdate() function.

Using the table comparison you have to perform the comparison on both the source and the target tables-

Using the History preserving transform, perform the below tasks-

Under the Date Columns section, select the valid from field from the source (added in the Query), and select Valid to from the target.

Under valid to date fields, select 'Use valid from date of the new record'

Under Current Flag, select the column flag from the target structure. This will hold the status of the record which says if it’s a modified record or an old record.

Specify set value and rest value as shown above.

Using the Key generation, you specify the Surrogate key to maintain the primary key records in the table.

Now you need to run the job to see the output-

If you notice the first record , the city is Berlin. Now you will update the city to BLR using the following SQL code-

UPDATE dbo.CUSTOMERS1 SET CITY='BLR' WHERE CUSTOMERID='ALFKI'

See the updated record using the following SQL command.

SELECT * FROM dbo.CUSTOMERS1

So now, you have a changed record, and you can re-run the job to see the functionality of the History preserving transform-

If you see the Column_flag, you will notice that for all the old records it is 'Y' and for the changed record it is 'N'. Also notice the change in the valid to date-