SCD stage

michaeld · Post by **michaeld** » Thu Apr 16, 2009 1:41 pm

I'm trying to use the SCD stage to implement SCD2 logic. The issue I have is that a new surrogate key is generated for both updated records and new records. That is if a records is updated then the result is two record in the dimension update link:

1) entry to expire the old record with the SK and EXP_DT
2) entry to add the new updated record with all columns.

The issue is that for the updated record it doesn't keep the original key from the dimension lookup. It generates a new key. But a new key should ONLY be generated if the record was not found in the dimension lookup.

Is this yet another bug in the SCD stage or am I missing something?

michaeld · Post by **michaeld** » Thu Apr 16, 2009 3:02 pm

I just answered my own question.

It is a bug in the SCD stage:

even thought the dimension table is not displayed in the source column pane you can still access it through transformer script.

example:

if isNull(out_odbc_Products.PRODUCT_SK) then
NextSurrogateKey()
else
out_odbc_Products.PRODUCT_SK

It's actually kind of funny because when you type this in then datastage draws a column link going to an invisible table:-)

ray.wurlod · Post by **ray.wurlod** » Thu Apr 16, 2009 4:51 pm

Does this bug still exist in version 8.1?

michaeld · Post by **michaeld** » Fri Apr 17, 2009 7:48 am

ray.wurlod · Post by **ray.wurlod** » Fri Apr 17, 2009 3:05 pm

You said you were running 8.01. Probably it's 8.0.1.
This is not the same as 8.1.

nirdesh2 · Post by **nirdesh2** » Sun Apr 19, 2009 1:30 pm

I am working on 8.0 version and used SCD stage. Its is working fine for me and generating 2 records for type2, one with old SK for update and one with new SK for insert.

michaeld · Post by **michaeld** » Mon Apr 20, 2009 5:39 am

hi nirdesh2,

what do you have in the expression for the SK column on the Dim update tab?

Is it just NextSurrogateKey() or something else?

nirdesh2 · Post by **nirdesh2** » Fri Apr 24, 2009 6:09 am

I am using NextSurrogateKey() in expression nothing else.

michaeld · Post by **michaeld** » Fri Apr 24, 2009 8:48 am

That's odd. I tried that initially and it didn't work. It would always generate a new SK. Maybe I'm missing some DataStage fix or patch or something.

rakbhaga · Post by **rakbhaga** » Thu May 21, 2009 10:20 am

HiI tried Scd type 2 .it is working fine with file but not with database.It is having bug in 8.0.1. If anybody have implement with ODBC stage can help need urgently .

hiral.chauhan · Post by **hiral.chauhan** » Wed Jun 10, 2009 9:16 am

I am using version 8.1.0, and I'm facing the same problem.

Does anybody know of a patch for this?

Thank you micheald !

~Hiral