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

Table Comparison Transformation

Table Comparison Transformation in SAP BODSSummary:

Table comparison transformation is used to compare the two data sets and   check what are the records were changed and updates, inserted and deleted.

Comparing two data sets, this transform can generate the difference between them as a resultant dataset with each row of the result flagged as insert, update, or delete.

While loading data to a target table, this transform can be used to ensure a row are not duplicated in a target table and hence is very helpful to load a dimension table.

The transform will take in records from a query- compare them with the target table.

First is will identify incoming records that match target records based on the key columns you select.
Any records that do not match will come out of the transform as inserts.
Records that match on the key columns will then be compared based on the selected compare columns.

Records that match exactly on the key and compare columns will be ignored – i.e. not output by the table compare transform.
Records that match on the key columns but differ on the compare columns will be output as update records.

Example scenario:-

The use of table comparison is used to update, insert and delete the records.

Now in the figure 1 the source data for FIRST_NAME column we are modifying the record ‘TOM’ as ‘TOMY’ so this record needs to be updated which means in the place of ‘TOM’ the record ’TOMY’ must be placed and now   we are inserting new row that has to inserted after the row number 3.Now we can see by looking at the below target table

 

KEY_COLUMN CUSTOMER_NUMBER FIRST_NAME LAST_NAME
1 C111 TOM HANKS
2 C222 RAGHU RAM
3 C333 MACRO POLY

 Figure 1: Sample Source Data

In figure 2 we can observe that the updated inserted records are in target table

KEY_COLUMN CUSTOMER_NUMBER FIRST_NAME LAST_NAME
1 C111 TOMY HANKS
2 C222 RAGHU RAM
3 C333 MACRO POLY
4 C444 DAVID UGANDA

Figure 2: Sample Target Data

By analyzing the above table the updated record in first row and inserted records in fourth row.

what are the  changes happened in source file that need to be reflected  in target table ,which means updated, Inserted, deleted  records must  be  known

NOTE: – In table comparison transformation we can’t use generated key column as VARCHAR it always must be INT

Below figure 3 indicates the flow of table comparison transformation hierarchy

Table Comparison Transformation in BODS
Figure 4: ETL flow for Table comparison transformation

We can see the source data in below figure 5

Table Comparison Transformation in SAP BODS
Figure 5: Source Data

we can see the settings for table comparison transformation as shown in figure 6.if you want to detect the deleted record you have to enable the “ DETECT DELETED ROW(S) FROM COMPARISON TABLE” and if you want to allow the duplicates for primary key you have to enable the ”INPUT CONTAINS THE DUPLICATES KEYS”

Table Comparison Transform in BODS
Figure 6: Table comparison transformation settings

The inserted ,updated  and deleted records indicates as” I” and “U”  and “D” flags .This flags will be seen in debug mode only.

Table Comparison Transformation in SAP BODS 3
Figure 7: Target table

That’s it.

Enjoy 🙂

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