In this session I am going to discuss about the implementation of delta mechanism in SAP BODS. But before that we will go through few Transformations which is required for implement Delta Mechanism both for master data and transaction data flow. 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. Map_Operation : Modify Opcodes (I/U/D) operations. Allows conversion between opcodes provided as a input from previous transformation. After map operation use can only use transformation that understand opcode or a permanent table. Query transformation is optional to use.
The ‘Map_Operation’ transform changes the operation codes on the input data sets.
3. 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 -
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.
4. 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.
A. SCD (Slow Changing Dimension) Types :
1. SCD1 : In this techniques, we not save any historical data. If there is any changes on source data or row then it override the old data with new data in the target table row. No Opcodes(I/U/D) conversion performed.
This scenario is needed when organization do not requirement to keep historical data.
Data flow will looks like below -
In a better way you can do the bellow flow also for SCD1 -
2. SCD2 Type : Using SCD2 mechanism for each updated record a new row inserted and set flag to 'Y' and the Old record row set flag to 'N'. we are keeping both the old and the new data using separate row. Using this technique we can keep unlimited old record i,e. history preservation.
Below mandatory Opcodes(I/U/D) conversion performed -
U->I i.e. UPDATE--> INSERT
I -> I i.e. INSERT--> INSERT
3. SCD Type 3 :
In this type we store the last updated or modified record along with current record. An additional column added to capture the change column last change value. We only consider the current and the last changed value to be store as a column in the database.
For our case we changing EMAIL_ID so in our case there will be an additional column will be OLD_EMAIL_ID.
Query Node :
Inset Query Node :
Update Query node :
Map Operation :
3. SCD Type 4 : Function wise SCD Type 4 is similar to SCD Type 2 but when changes made rapidly to the source data then volume increate and need to implement SCD Type where the change data store in another target table. This is not frequently use.
In my next blog I will discuss about the Source base CDC and Target Based CDC and Between CDC and SCD which we will use in which scenario.
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. Map_Operation : Modify Opcodes (I/U/D) operations. Allows conversion between opcodes provided as a input from previous transformation. After map operation use can only use transformation that understand opcode or a permanent table. Query transformation is optional to use.
The ‘Map_Operation’ transform changes the operation codes on the input data sets.
3. 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.
4. 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.
A. SCD (Slow Changing Dimension) Types :
1. SCD1 : In this techniques, we not save any historical data. If there is any changes on source data or row then it override the old data with new data in the target table row. No Opcodes(I/U/D) conversion performed.
This scenario is needed when organization do not requirement to keep historical data.
Data flow will looks like below -
In a better way you can do the bellow flow also for SCD1 -
2. SCD2 Type : Using SCD2 mechanism for each updated record a new row inserted and set flag to 'Y' and the Old record row set flag to 'N'. we are keeping both the old and the new data using separate row. Using this technique we can keep unlimited old record i,e. history preservation.
Below mandatory Opcodes(I/U/D) conversion performed -
U->I i.e. UPDATE--> INSERT
I -> I i.e. INSERT--> INSERT
3. SCD Type 3 :
In this type we store the last updated or modified record along with current record. An additional column added to capture the change column last change value. We only consider the current and the last changed value to be store as a column in the database.
For our case we changing EMAIL_ID so in our case there will be an additional column will be OLD_EMAIL_ID.
Query Node :
Inset Query Node :
Update Query node :
Map Operation :
3. SCD Type 4 : Function wise SCD Type 4 is similar to SCD Type 2 but when changes made rapidly to the source data then volume increate and need to implement SCD Type where the change data store in another target table. This is not frequently use.
In my next blog I will discuss about the Source base CDC and Target Based CDC and Between CDC and SCD which we will use in which scenario.
- SAP Managed Tags:
