SAP BODS Incremental Load

dataservices job design
1

Hello All,

I am loading data from legacy ssystem and implementing SCD2 .If i run job after Initial load, count is increasing even when there are no changes in Source system. I am suppose to take CUST_ID as primary key , Can anyone tel me how to implement SCD2 for this below table.

If i change any one of CUST_ID eg: C100 -CUST_TEL_NR - 9491959094 to -1234567890

when i run job for this changed records all the records of C100 are getting updated END-DATE (Today date ) and new records are getting updated with 12.31.999 .

Regards
Vinay
SDN.JPG

vinay.sapds (BOB member since 2011-05-16)

2

How have you structured your dataflow to achieve SCD2 processing?

How have you defined the keys and compare columns?

johnb (BOB member since 2006-07-05)

3

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.
Source Table
AGENT_ID ,CUST_DRI_ID, INSURANCE_POLICY ,CUST_CODE ,CUST_NAME
C100, 4801, 8507666777 ,US ,ABV
C100, 4801, 8507555666 ,US ,SFSF
C100, 4901 , 8804777888 ,US ,SFSFF
C100, 4901, 9397609999 ,US ,HTHTH
C100, 4901, 9348059999 ,US ,JTHJJ
C100, 4901, 9391079999 ,US ,FHHTYJ
C100 , 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?

vinay.sapds (BOB member since 2011-05-16)

4

Your problem is that your PK needs to be unique and it’s not. A Primary Key (PK) needs to uniquely identify each row and if it doesn’t, as you have here, then the SCD2 processing can’t work and will give wrong results (as you are getting).

John

johnb (BOB member since 2006-07-05)

5

ok, Is there any way to resolve the issue…

vinay.sapds (BOB member since 2011-05-16)