Surrogate Key with included Effective Date
Moderators: chulett, rschirm, roy
Surrogate Key with included Effective Date
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
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
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
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.
That procedure can called inside the SCD.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
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
@ 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
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.
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.
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.sreewin7 wrote:u can use "KeyMgtGetNextValue('xyz')" in the transfer stage
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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,
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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