SCD - Effective Date - Do not always use currentTimeStamp()

algfr · Post by **algfr** » Fri Jul 24, 2009 10:30 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Jul 24, 2009 7:30 pm

Don't know. Haven't needed to try it. Have YOU tried it?

algfr · Post by **algfr** » Mon Jul 27, 2009 3:36 am

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.

algfr · Post by **algfr** » Wed Sep 16, 2009 2:24 am

This solution implies that the previous SK is kept.

What I did in the end was performing an update statement to replace the oldest begin data by 01/01/1901.

I think they should add this option in the next version because it's a common need.

Mike · Post by **Mike** » Wed Sep 16, 2009 5:55 am

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

algfr · Post by **algfr** » Thu Sep 17, 2009 2:30 am

Mike 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

Hi

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