SCD Type 1 - how to implement no change in some values

[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?

[SURA]

In fast path 2 & 4 use purpose and Derivation.

DS User

[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.

[SURA]

Post your fath path 2 & 4th code

DS User

[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.

[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

[ray.wurlod]

What happens if you provide no derivation or purpose at all for this column in the SCD stage?

[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!

[ray.wurlod]

What if you derive it as itself?

[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?

[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.