0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Summary:-

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

Prerequisite:-

In target table “delete data before loading” option must be ‘UNCHECKED’.

For history preserving transformation before table comparison transformation must be used.

Example scenario:-

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.

CUSTOMER_NUMBER FIRST_NAME LAST_NAME CITY START DATE
111 TOMY HANKS CALFORNIA 2012.09.20
333 MARCO POLo SINGAPORE 2012.09.22

Figure 1: Sample Source Data

Below figure 2 we can see that the updated record in row 3

CUSTOMER_NUMBER FIRST_NAME LAST_NAME CITY START DATE END_DATE
111 TOMY HANKS CALFORNIA 2012.09.20 2012.09.20
333 MARCO POLo SINGAPORE 2012.09.22 2012.09.22
333 MARCO POL SINGAPORE 2012.09.22 9999.12.31

Figure 2: Sample Target Data

Below figure indicates the hierarchy for history preserving transformation

History Preserving Transformation in BODS
Figure 3:- History preserving transformation hierarchy

We can observe the flow for history preserving transformation

History Preserving Transform in BODS
Figure4:- ETL flow for History preserving transformation

We can see the source data in below figure 5

History Preserving Transform in SAP BODS
Figure 5:-Source data

We can see the settings for table comparison in figure 6

History Preserving Transform in SAP BODS 1
Figure 6:- Table comparison transformation settings

We can see the settings for history preserving transformations in figure 7

History Preserving Transformation in SAP BODS
Figure 7:- History preserving transformation settings

In figure 8, we can see the updated records are inserted

History Preserving Transform in SAP BODS 2
Figure 8:- Target data

Source Files:

  • Use the below files as sources for working out on the transformation

Click Here to Download Source Data.

Attachment:

  • Import the below .atl file in the Data Services Designer to find the Job for the above transformation.

Click Here to Download ATL File.

That’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×