Page 1 of 1
SCD Type 1 - how to implement no change in some values
Posted: Thu Aug 25, 2011 10:39 pm
by evee1
I am trying to build Type 1 dimension using SCD stage. The definition out the Update link contains the following columns, with their SCD purpose codes and derivations:
Code: Select all
Derivation Column Name Purpose
NextSurrogateKey() REC_KEY Surrogate Key
lnk_In.REC_IDNT REC_IDNT Business Key
lnk_In.REC_DESC REC_DESC Type 1
REC_LOAD_DT
CurrentDate() REC_UPD_DT
RUN_KEY RUN_KEY
I have a problem with the control column REC_LOAD_DT though. This column is meant to be set to sysdate when the record is first created. After that it should remain unchanged throughout the life of the record, regardless of changes (type 1) to REC_DESC column.
Other columns, lile REC_UPD_DT and RUN_KEY should be updated every time a new version of the record arrives that have.
PS. I would like to be able to do this in Type 2 dimensions as well.
Is this achievable using SCD stage?
Re: SCD Type 1 - how to implement no change in some values
Posted: Thu Aug 25, 2011 10:47 pm
by SURA
In fast path 2 & 4 use purpose and Derivation.
DS User
Posted: Thu Aug 25, 2011 10:58 pm
by evee1
The problem is that the only Pupose Code I could use here is Type 1, or <blank>. Together with setting derivation to sysdate (or in my case BATCH_DATE) it does not achieve my goal, i.e. leaving REC_LOAD_DT as it is.
Posted: Thu Aug 25, 2011 11:07 pm
by SURA
Post your fath path 2 & 4th code
DS User
Posted: Thu Aug 25, 2011 11:10 pm
by evee1
I realise I could override Update and Insert statements in the target Oracle connector, i.e. drop the REC_LOAD_DT column from the update statement,
but I was wondering whether I could handle it someone in SCD stage instead and use the generated upsert in the Ora connector.
Posted: Thu Aug 25, 2011 11:17 pm
by SURA
I am not sure about what you are trying.
Bur as far as i know, you need to create a proper type 1 table using proper key constraint and use this SCD stage. I trust it will work with no further magic.
If i am right, you need to consider the upsert method (insert then update / update then insert)
DS User
Posted: Fri Aug 26, 2011 12:33 am
by ray.wurlod
What happens if you provide no derivation or purpose at all for this column in the SCD stage?
Posted: Fri Aug 26, 2011 12:38 am
by evee1
It marks the column with red and the compilation fails with the error message : A dimension update column REC_LOAD_DT does not have a derivation!
Posted: Fri Aug 26, 2011 1:50 am
by ray.wurlod
What if you derive it as itself?
Posted: Sun Aug 28, 2011 7:41 pm
by evee1
I have change the derivation to make it as lnk_Target.REC_LOAD_DT. It looks a bit funny on the screen as the incoming link starts on the left panel in the middle of nowhere
. But it works really nice for updates now.
However I cannot make it work properly for new records now. New records should have this value set to the current date, and I tried to do it by setting the derivation to:
Code: Select all
If IsNull(lnk_Target.REC_LOAD_DT) Then CurrentDate() Else lnk_Target.REC_LOAD_DT
Uninitialised timestamp field must have some other value than NULL as it does not recognise this condition. I'm not sure what the actual value is, as Peek or File output only shows RECD_LOAD_DT:*******************.
Would you know how can I test for uninitialised Timestamp field, since I can't (I think) test for "change code" in SCD stage, similarly to how it's done in a case of Change Capture stage?
Posted: Sun Aug 28, 2011 8:43 pm
by evee1
Got it!!!
I have changed the test to the following code:
Code: Select all
If IsValidTimestamp(lnk_TargetDim.REC_LOAD_DT) Then lnk_TargetDim.REC_LOAD_DT Else CurrentDate()
and now it works correctly for both new records and updates :D.