History_Preserving Transform

[Former Member]

Use of History Preserving Transform

Introduction:-

It is used to preserve the history of the source records. If the source row has operation code of Insert/Update then it insert a new record in the target table.

Scenario:-

We are doing a scenario where we want to insert the updated record into target table to preserve the history of the source records.

1) Create project, job, workflow & dataflow as usual.

2) Drag a source table to dataflow. Its contents are as follows.

3) Drag a target table to dataflow. Its contents are as follows.

4) Drag query, Table-Comparison, History_Preserving transform as shown in the figure.

5)  Open Query & do mappings as you do normally.

6) Open Table_Comparison block & enter all the properties.

• Table Name:- Select Target Table from the dropdown box.
• Generated Key Column:- Specify key column
• Select the "EMP_ID" node from the tree on LHS & drag into "Input primary key columns" list box. Now the comparison of the target table will take place based on whether the source EMP_ID is present in the target or not & comparison will be made based on the column s given under "Compare columns"  list box.
• Similarly select the columns that are to be compared while transferring the data & drag it to "Compare Columns" list box.
• Select "Cached comparison table" radio button.

7) Similarly provide details for the History_Preserving block.

• In Compare column select the columns as specified in the Table Compassion transform.
• Specify Date columns as specified.
• Here we are mentioning the valid date as 9000.12.31.
• In target table we have maintained the column as "Flag" & based on the Update operation the original value of the column for that particular record will be replaced from Y to N. And new records will be inserted with the status as 'Y'.

😎 Now after this we have updated 1st 3 rows of source records & 4th row is deleted .

     Fields where changes are made are circled with the red marks as seen in the above figure.

9) Validate & Execute the job.

10) 3 new records got added in the target table as shown below.

  

You can see that new entry for updated record is made in the target table along with the  'Y' flag & new END_DATE as '9000.12.31'

& the flag of the original records are changed to 'N'.

Summary:-

So in this way History Preserving  block is useful in preserving the History if the  source records.

Thanks & Regards,

Rahul S. More

(Technical Lead)

IGATE Global Solutions Pvt. Ltd.