SCD Type - 1 without the use of in-built SCD component
Hi,
I m trying to perform SCD type - 1 scenario without use of any DB-SCD component.
My source is -
Dim_emp -
emp_id,emp_name,emp_location
1,'abc','london'
2,'def','boston'
As per SCD - 1, any changes it will be overwriting the existing record on an update.... so for instance emp_id =2 has shifted his/her location to paris...How should i proceed and how should the job design be constructed??
Should i first insert all the records and then write a simple update statement??
Assuming your input has all the cols of table
1) Simply by puting tdboutput with action on data “update or insert”. But it will perform update the records even record if ni change in data.
To avoid that , you could remove those records if all the fileds are same in input compare to database table :
Using tmap , keep your dbinput in lookup and the input in main. Compare each col of input and databae , if not same put send it to tmap output for update.
You could also use bulk component but in that case update and insert needs to be done as two flow.
You could go with upload in temp table and use databse engine to do update or insert.
Delete temp table after.
With the incoming data, you should ascertain what rows already exist in the DB, this will allow you to control seperate flows of either Insert or Update.
Load the Business Keys into a temp table in the DB and then inner join them to the SCD1 dimension - only returning the business key, this forms a lookup feed into a tMAP where you join the complete incoming data and pass the rows down an update output (inner join) or insert (inner join reject). Be careful that you only have 1 row per business key, otherwise you could mis-interpret 2 new rows for the same business key as inserts.
