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]
SCD Design
Moderators: chulett, rschirm, roy
SCD Design
Jim Stewart
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle