Surrogate Key with included Effective Date

ke.rstin_ · Post by **ke.rstin_** » Sat Jun 23, 2012 9:55 am

Hi everybody,
due to foreign keys which I have to consider in my core dwh, I need a combined surrogate key which consists of the "surrogate key id" + the effective date. I have to realize that via the SCD stage of DataStage.

I either get an error for violating constraints or too little data is stored.

Do you have any ideas for me or do you know if this is possible so far with this approach in the SCD stage at all?

Best regards,
Kerstin

jwiles · Post by **jwiles** » Sat Jun 23, 2012 12:52 pm

Generate your surrogate key and the concatenate with whatever date within a transformer. You could generate the SK within the transformer as well and use one less stage in the job.

Regards,

ke.rstin_ · Post by **ke.rstin_** » Sun Jun 24, 2012 2:42 pm

I'm not sure if I got you. The idea with the transformer afterwards sounds good but I'm scared it doesn't prevent from generating a new sk id within the scd Stage... It's not easy to handle :/

jwiles · Post by **jwiles** » Sun Jun 24, 2012 9:36 pm

If you need to control when a new surrogate key value is created, use a transformer to generate the surrogate key using the NextSurrogateKey function in a stage variable derivation with appropriate if-then-else logic.

Do your business rules dictate that multiple records will have the same surrogate key value and are differentiated by the date value you are adding to the end of the SK?

Regards,

SURA · Post by **SURA** » Sun Jun 24, 2012 10:03 pm

After i saw your thread, just i modified my job to see how the Datastage is responding to it! Just i concatenated something(Date) but it is not consider that concatenation and the reason is, SCD is just getting the next value from somewhere. So You need to write a procedure to handle this in such a way!

That procedure can called inside the SCD.

ke.rstin_ · Post by **ke.rstin_** » Mon Jun 25, 2012 12:45 am

Thank you for your ideas so far.

@ jwiles: Yes, unfortunately I HAVE to use the same SK with another date because of foreign keys. That makes using the SCD stage not really simple.

@ SURA: You mean a stored procedure where I check if an update happens and if yes, I keep the SK? I'm asking this (perhaps silly) question, because I've never written one in DataStage before. How do I have access to the values of DataStage or do you mean using a database instead of SKF-files?

Thanks,
Kerstin

SURA · Post by **SURA** » Mon Jun 25, 2012 8:12 pm

Yes, SCD stage will identify the new records and if so, it will fetch the new SKEY otherwise pass the old.

You need to write a Stored Proc in DB.

sreewin7 · Post by **sreewin7** » Tue Jun 26, 2012 5:57 pm

Hey ,

I am just adding the with "jwiles" comments and i gone through ur post, u can use "KeyMgtGetNextValue('xyz')" in the transfer stage and concate with today timestamp.

As you mentioned you are using in foreign key s.key, u can call the same "KeyMgtGetNextValue('xyz')".

Example
KeyMgtGetNextValue('xyz') -Call in the stage variable and concate with the output column.

Sura & jwiles :- correct me if any thing wrong in my answer.

chulett · Post by **chulett** » Tue Jun 26, 2012 8:31 pm

sreewin7 wrote:u can use "KeyMgtGetNextValue('xyz')" in the transfer stage

James has posted the correct parallel function to use when generating surrogate keys in the Transformer (not transfer) stage, you've just mentioned the Server / BASIC equivalent.

ps. People don't really need to ask for their answer to be corrected if anything is wrong - don't worry, it will be. No asking required.

jwiles · Post by **jwiles** » Tue Jun 26, 2012 10:26 pm

kerstin is trying to do this within the/for use by the Slowly Changing Dimensions stage (my eyesight had misread SCD as SK originally). My answer may not be relevant for his needs unless the key could be generated/regenerated after SCD...?

Regards,

ray.wurlod · Post by **ray.wurlod** » Tue Jun 26, 2012 11:55 pm

It's Kerstin who is seeking a solution, not U (another of our posters).

The second person personal pronoun in English is spelled "you".

ke.rstin_ · Post by **ke.rstin_** » Wed Jun 27, 2012 12:52 am

Thank you for so many replies

The concatenation is not really what I need, by saying "combined surrogate key" I meant that I have two fields which build together the PK. One is the ID (generated Surrogate Key Value) and another is the FromDate.

As soon as I get a new data record with the same business key, I have to generate a new entry with the same ID but another FromDate (Effective Date) and ToDate (Expiration Date). (That's not how SCD should work according to Kimball, I know, but that's how the customer wants it to work...)

My explanations are sometimes a little bit misleading, I'm sorry for that

ke.rstin_ · Post by **ke.rstin_** » Mon Jul 09, 2012 1:41 am

Let me show you an example how it should look like

Entry on 08.07.2012:
ID FROM_DATE TEXT
1 08.07.2012 A

Entry on 09.07.2012:
ID FROM_DATE TEXT
1 08.07.2012 A
1 09.07.2012 Ab (new entry)

ID + FROM_DATE = SK