SAP BODS SCD2 LOADING ISSUES

[Former Member]

Hello All,

We are extracting data from Legacy to SQL server and in future will move data to SAP.

Initially we are loading Master data from Legacy in this process we are implementing SCD2.

When I am using CUST_ID, CUST_DRI_ID as PK as suggested by our Business analysts, But not declared as PK in database.

I am using below logic to implement SCD2

For this we have created ROW_ID, FLAG, STARTDATE, ENDDATE columns in Target Tables.

Source—QT- TABLECOMPARISION- HP-KEY GEN

TABLE COMPARISON- CUST_ID,CUST_DRI_ID (Input primary key columns) and rest of columns in COMAPRE COLUMNS , I am excluded Date,datetime in compare columns

HP- STARTDATE( system date),ENDATE(12.31.999) and FLAY-Y,N

In compare columns I am taking all columns.

Key_Gen- ROWID

1)When I execute the job for first time it is loading all records i.e. 100 records.

When I run job for second time without changing any source data, records are getting doubled.

100 records-with today date( Aged Records), 100 records with 12.31.999( Valid records).

2) Suppose if I update THRJYJ to ABCD (CUST_NAME) column all the records are being Aged( Today date) where CUST_DRI_ID = 4901

3) I doubt my BA has provided wrong keys, But as per my BA all the above records are valid.

4) when I declare PK job is failing due to PK violation.

Can anyone let me know how to solve this problem?

[former_member198401]

You need to use History Preserving transform to preserver the history in the form of Flag(Y = Active and N= Inactive)

Use

CUST_ID, CUST_DRI_ID, INSURANCE_POLICY as Input primary key columns as they are unique and CUST_CODE and CUST_NAME as Compare columns

**CUST_ID, CUST_DRI_ID cannot be Primary Key as they are not unique.

So your data flow will be

Source>Query_Transform>Table_Comparison(

CUST_ID, CUST_DRI_ID, INSURANCE_POLICY as Primary Key Columns)>History_Preserving_Transform(Use Current Flag as FLAG)>Key_generation(ROWID)>Target Table

Refer below link

https://dwbi.org/etl/sap-data-services/104-how-to-implement-scd-type-2-using-history-preserve-transf...

EnjoY!!!

Regards

Arun Sasi