how to implement typ2 SCD in PX

[vijaya]

Please let me know how to impement Type2 scd in PX .

[kumar_s]

Hi Vidya,
Welcome aboard :D
Type 2 is you want to add new rows to the target and retain the old rows with a unique Key.
So simple surrogate key generator would help to maintain unique keys(Primary Key).
For the concept of update/Insert, have a look at vincents blog here.
http://www.db2magazine.com/story/showAr ... =186500841
Do a search on "SCD type2" you will get lot more information.

[iamnagus]

Hi,

Using two Dates fields also we can implement the Type2 SCD. This is one of the way where you can implement Type2 SCD.

But it is bit difficult to explain in brief here how exactly achieve it in the job.

With the combination of a Primary Key & Effective-In-Date key(composite key) you can show updated record(as if it is history record) in the target table. Based on these two dates fields you can distinguish the PRESENT or HISTORY records in the target table. First you need to design the two date fields for insert,update & delete records on how they should present in the target table and this is upto you how could be achieved this in the trasformer stage. Before this you might be using the CDC stage to capture the status of each record from source to target for New INSERT, UPDATE & DELETEs which occured at source systems.

I feel this story could be confusing you. But.....

Regards,
iamnagus

[ray.wurlod]

You must make sure that data from target and source are identically partitioned so that all valid comparisons are correctly made.

Loading dimesion (parent) tables first if the natural key does not already exist, insert a new row. If the natural key does exist in the target check for differences in relevant columns and, if any is found, insert a new record and "expire" the prior variant.

Loading fact (child) tables use a reverse-lookup from the dimension (parent) table for each foreign key to map natural key to surrogate key.