etl-tools.info - datastage-tutorial- scd2-implementation?

vmcburney · Post by **vmcburney** » Wed Feb 13, 2008 4:10 pm

An SCD type 2 table is supposed to hold duplicates - it shows changes to that particular CUST_ID over time. When the REC_CURRENT_IND flag is set to Y that is the current record - that's the one used by most reports and queries. When the flag is set to N that's a superceded record and the REC_EFFDT field shows you when that row came into effect. I have only seen SCD Type 2 tables with START and END date fields so you can use between joins for historical reporting. So in this case there should also be a REC_ENDDT field set to a high date for current records (2999-12-31) and set to an end date for superseded records.

kduke · Post by **kduke** » Wed Feb 13, 2008 6:55 pm

Link does not work.

chulett · Post by **chulett** » Wed Feb 13, 2008 7:43 pm

Remove the extra dot from the end. After you are done poking around over there, don't forget to wash your hands.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 13, 2008 10:03 pm

Start at this page for the full set. Heed Craig's advice.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 14, 2008 5:03 am

Of course it can. It's the surrogate key that can not. If some attribute of the customer (maybe name) changes, in a Type 2 SCD there will be a new row with the same CUST_ID, but with a new surrogate key.

laknar · Post by **laknar** » Tue Aug 05, 2008 6:30 am

Hi,

I have exactly the same design.

When we load the records first time into the dimension table.
How can i load without Suurogate key stage,trigger.

chulett · Post by **chulett** » Tue Aug 05, 2008 6:43 am

Start a new post. Include the details of your job and state your problem there.