Hi,
The SCD Type 2 design works fine but I have a requirement :
When I load my fact tables, I will do a range lookup to check what was the correct reference at the time of the fact.
To me, this range lookup can only work if, when the record is NEW, the effective date should be 01/01/1901 and not the insertion time so that the BETWEEN range always works.
Is that can be done ?
Regards
SCD - Effective Date - Do not always use currentTimeStamp()
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Well, I have a solution but I don't like it.
In the SCD stage, I create an extra field called PREV_SK which uses the PrevSKChain function (not documented that much by IBM).
It has an argument : the business key. If the record is the first one, it returns 0, otherwise, it returns the surrogate key of the previous version of the record.
So what I did is getting this info in the SCD stage but I'm forced to use an extra (again...) Transformer stage to do the following for the effective date :
Effective Date
If PREV_SK = 0 Then '1901-01-01 00:00:00' Else CurrentTimeStamp()
I already have 2 Transfomers in my job so that's quite bothering.
I wonder what people do with the SCD stage when it comes to finding what surrogate key to put in the fact tables, at some point there needs to be a lookup to the Business Key + the date ranges.
In the SCD stage, I create an extra field called PREV_SK which uses the PrevSKChain function (not documented that much by IBM).
It has an argument : the business key. If the record is the first one, it returns 0, otherwise, it returns the surrogate key of the previous version of the record.
So what I did is getting this info in the SCD stage but I'm forced to use an extra (again...) Transformer stage to do the following for the effective date :
Effective Date
If PREV_SK = 0 Then '1901-01-01 00:00:00' Else CurrentTimeStamp()
I already have 2 Transfomers in my job so that's quite bothering.
I wonder what people do with the SCD stage when it comes to finding what surrogate key to put in the fact tables, at some point there needs to be a lookup to the Business Key + the date ranges.
Whoa algfr! You're out in the weeds. Why do you think you have to use current timestamp for a type 2 effective date? I think I've never used that derivation, so I know it's not a stage requirement.
Let's go back to the derivation that you would like to use and post the error message that you're getting.
If you want to use a literal value, you may just need a StringToDate or StringToTimestamp function to match your target data type.
Mike
Let's go back to the derivation that you would like to use and post the error message that you're getting.
If you want to use a literal value, you may just need a StringToDate or StringToTimestamp function to match your target data type.
Mike
HiMike wrote:Whoa algfr! You're out in the weeds. Why do you think you have to use current timestamp for a type 2 effective date? I think I've never used that derivation, so I know it's not a stage requirement.
Let's go back to the derivation that you would like to use and post the error message that you're getting.
If you want to use a literal value, you may just need a StringToDate or StringToTimestamp function to match your target data type.
Mike
Well I'm not sure you understand my requirement. Let my explain
Let's assume i get one record for the first time with one business key. Then I want to have the following :
ID1 ProductXXX 01/01/1901 31/12/9999
Indeed, I assume that this record must be valid whatever the date.
Then If i get a SCD type 2 update, I want to have that (today's date is 17/09/2009 and I load today) :
ID1 ProductAAA DESC1 01/01/1901 17/09/2009
ID2 ProductAAA DESC2 17/09/2009 31/12/9999
As far as I understand it, I cannot do that in SCD stage because the Effective date does not have an expiration value.
If I set 01/01/1901 then I would get the following :
ID1 ProductAAA DESC1 01/01/1901 17/09/2009
ID2 ProductAAA DESC2 01/01/1901 31/12/9999
That is not good of course
If I use the effective date I get the following (I update on the 20th)
ID1 ProductAAA DESC1 17/09/2009 20/09/2009
ID2 ProductAAA DESC2 20/09/2009 31/12/9999
If I load fact that are anterior to 17/09/2009 then I would not be able to match my records.
Do you see my point ?
Arnaud