+++> 12.History Preserving

History Preserving  

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

For example, if we have a customer 1000, whose address is changed from NYK to LA
after some time hez moved to Texas. If we update the table with the new location or address,
all his past records will be affected. So we need something which will preserve the history of his location.

Using the History preserving transformation we can save that type of data.

To apply the history preserving transformation we need to have a table comparison done prior to
this transformation.

In the above example, First we need to have a target structure with the below specified fields.

S-key (surrogate key) will act as the primary key as we 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.

We 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 that with sysdate() function.

Using the table comparison we perform the comparison on both the source and the target table.

Using the History preserving we perform the bellow tasks.

Under the Date Columns section,

Select the valid from field from the source (we 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 above.

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

Now you run the job to see the output.

If you notice the first record , the city is Berlin. Now we 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 we have a changed record, and now we will re-run the job to see the functionality of the History
preserving transform.

Now if you see the Column_flag, we notice that for all the old records its Y and for the changed record
its N and you can also notice the change in the valid to date.

I applied filter on the table and now you can see the same record preserved with the column flag.