Transformation Summary in SAP data service – BODS – Part 4 – Table Comparison and History preservation and Row Generation Transformation

[pallab_haldar]

Today I will discuss on three useful Data Integration Transformation. Together if they used they can perform a number of transformation output required from business. These transformations are Table Comparison and History preservation and Row Generation Transformation

 

These major transformations  are given below –

1. Table Comparison : Used to compare data between Source and Target Table/Flat files. Others and  return opcode for Insert or update based on the expected changed column you defined in the transformation. It is not mandatory but good practice to use Query transform before table comparison to project/select the specific data. Key Generation is not mandatory or map function is not mandatory with Table comparison but in different scenario where insert required for update operation i.e. historical preservation needed they Key Generation and map Operation is useful.

 

 

 

2. History Preservation :  This transformation is used to keep historical data which is updated as a separate row and set the flag to ‘N’ and for new data row, set the flag to ‘Y’. With Key_Generation transform history preservation transform gives better result. When source table row has operation code of Insert/Update then it insert a new record in the target table.

The below 4 field is preferable in Target Table structure to for History Preservation –

• S-key (surrogate key) will act as the primary key as you will get duplicate records


• STRT_DATE – have the valid from date


• END_DATE – have the change date


• Flag Indicating  the current record or the old record.

Also preferable to have start and end date in the source column as well. Using History preservation transform with Table comparison and Key generation is preferable.

 

 

 

 

3.Key Generation :  This transformation generate and extra key in incremental order to identify the old and new record inserted.

Using History preservation transform with Table comparison and Key generation is preferable.

 

 

Now we will discuss different delta mechanism implementation in BODS using those above transformation.

Most common and popular types of delta mechanism are SCD Types and CDC types (source and Target Based) which we are going to discuss.

The three transformation used in different scenario together to achieve different goal.

 

 

In the next session I will discuss about some of the Data Quality Transformation that will required day to day basis as a BOS+DS developer.