SCD Design

Raftsman · Post by **Raftsman** » Wed Apr 30, 2008 11:10 am

I am trying to get the following design working and please need some help

I have one main input stream and one reference (DIM table). If no records exist in the main stream and 1 exists in the dimension, I want to set the dimensional record expiry date. I selected one business key and many type 2 keys as the lookup. None get processed.

Can the SCD stage handle this and if so, could someone point me in the right direction. Again, the documentation is very poor on this.[/img]

Raftsman · Post by **Raftsman** » Wed Apr 30, 2008 2:03 pm

If anything, I don't think the Lookup is working in the SCD. It's difficult to determine. I simplified the process with Peek stages and still no comparison. As usual, the learning curve grows....

kcbland · Post by **kcbland** » Wed Apr 30, 2008 2:12 pm

I don't think you can achieve your goal easily. I don't know much of the mechanics behind this stage, but I would assume your stage would need a streaming input of rows to match the rows in the SCD target table.

I would guess you would need to determine which dimension natural keys don't exist in the first input stream of facts. Something like:

select dim_natural_key from dim_target where dim_natural_key not in (select dim_natural_key from fact_source). Those rows are the ones to be retired, then I would suggest you see if the SCD stage can retire without creating a new variant. If not you'll just have to do this the old fashioned way and update the most recent rows for those natural keys.