History Preserving Transformation in SAP BODS
History preserving transformation is used to maintain all the history of data or records which are updated, inserted and deleted records without changing the existing records.
Mapping tries to capture the changes of a dimension table and store the history of changes in different rows in the target table. We also mark each row with a flag with value ‘Y’ or ‘N’ signifying that row is latest or old.
When loading the target table, we extract only changed record sets from the source so that we do not need to process extra records unnecessarily.
In history preserving we need to populate columns like – active flag, valid from, valid to . While active flag only tells us if the record is the current record or not, valid from and valid to columns tell us the date ranges within which the record was valid.
It takes the input column and in case of an insert of an
In target table “delete data before loading” option must be ‘UNCHECKED’.
For history preserving transformation before table comparison transformation must be used.
In this example we are going to show when ever changes happened in source file how the target table is displayed.
In this figure 1 we will change the ‘LAST_NAME’ column data ‘POLo’ as ‘POL’ so without updating that record it will be inserted as a new row in the target table as below.
Figure 1: Sample Source Data
Below figure 2 we can see that the updated record in row 3
Figure 2: Sample Target Data
Below figure indicates the hierarchy for history preserving transformation
We can observe the flow for history preserving transformation
We can see the source data in below figure 5
We can see the settings for table comparison in figure 6
We can see the settings for history preserving transformations in figure 7
In figure 8, we can see the updated records are inserted
- Use the below files as sources for working out on the transformation
- Import the below .atl file in the Data Services Designer to find the Job for the above transformation.