Surrogate Key Assignment

[duh_sk8erboi]

Hello to all DS gurus

I'm new to DS & ETL development, but i'm quickly catching up & learning so many wonderful things about DS. I have come across a new challange, surrogate key assignment. how can one generate a new surrogate key for a column which is the key column in a transformer, which i used to extract data from a complex flat file & attempting to load to a oracle table.

Thanks in Advance

[rafidwh]

HI,

Use the DS transform function "KeyMgtGetNextValue" in the surrogate key column in the transformer.

Saik

[duh_sk8erboi]

wow... so fast!! thanks saik. ok, so i use that DS function, but how i make it generate a number? do i have to write additional code for this function to make it generate the sgt key?? please advice

Thanks in Advance

[rafidwh]

Use the function in the surrogate key column in the derivation column

No need to write any code

Good luck

[rasi]

You need to pass in the unique name for which it gets the next surrogate key. The value is stored in hash file and you can always reset the value by updating the hash file

[ram.m]

Hi,

by using KeyMgtGetNextval u can generate the sequence number in the transformer stage.

Thanks,
Ram

[duh_sk8erboi]

Alrite !!! :D thanks guys. I was able to generate surrogate key values using the DS transform function 'KeyMgtNextValue'. for the unique sequence name, after a little experimenting, i was able to use a column name that was the key column and also use a '0' and a '1' in place of the '%sequencename%' and got the same result of generated surrogate key values from 1 to atleast 300+ values. can anyone explain as to why the result was the same when i used different sequence names??? Another concern i face is to generate a hierarchy level ID with respect to the level of detail in the grain for a dimension. how is this possible in DS while mapping columns in a transformer? please advice

Thanks in Advance

[chulett]

People didn't give you much guidance in it's use, pretty much just said to use it - hence part of the confusion. I'm curious if you checked it out in the Manager - it lives under the Routines/sdk/KeyMgt branch and has a big brother called 'KeyMgtGetNextValueConcurrent'. Double-clink on each to read up on how they are used and to see their code.

The one you are using states inside on the Long Description that it 'does not support access to a particular sequence by more than one process concurrently'. As you can probably tell from the names, that's what the other one is for. Keep that in mind - you'll be ok if only one process is accessing sequence numbers from a particular 'sequence name' at a time. If multiple processes will be requesting numbers from the same sequence at the same time, you'll need to use the 'Concurrent' version. Of course, both can be copied, renamed and modified to suit your needs if those needs differ from what functionalities these deliver.

...after a little experimenting, i was able to use a column name that was the key column and also use a '0' and a '1' in place of the '%sequencename%' and got the same result of generated surrogate key values from 1 to atleast 300+ values. can anyone explain as to why the result was the same when i used different sequence names???

Not sure what you are saying here. Read the routine code to see what it does. There is a 'table' - a hashed file in the Project / Account - called SDKSequences that holds all of the current key values for you, one value per record or row. Each record is keyed by a unique 'sequence name' and this would typically be the table name you would be generating the sequence for. When you call it, what it ends up doing is basically this:

1) Try to read the record keyed by the value passed in.
2a) If #1 fails, create the record and assign a NextVal of 1.
2b) If #1 succeeds, take the current value in the record as the NextVal.
3) Assign NextVal as the value to be passed back from the routine.
4) Increment NextVal and write the updated record back to the table.

Every time you call the routine with the same value it will return the incremented surrogate assigned to that value. Since these values are typically used to provide surrogate keys to specific tables, they are generally keyed (called with) the name of the table the surrogate is destined for.

There are plenty of posts here on this subject which a search would have turned up. Try searching for 'KeyMgtGetNextValue' and see what turns up. The syntax to reset an individual record to a desired value has been posted here many times.

Another concern i face is to generate a hierarchy level ID with respect to the level of detail in the grain for a dimension. how is this possible in DS while mapping columns in a transformer?

Been a long day and I'm not sure what you are asking here. Perhaps someone else would like to take a stab at it or you may want to rephrase your concern.

[duh_sk8erboi]

Thanks a ton craig :D i did go through at the routine from DS manager, but never bothered to know the differance between the next value and concurrent. Now i do and would go through the steps you suggested, including reading up on the previous posts. My next issue was trying to generate another value, but in this case a ID number that would correspond to the hierarchy level (detailed to the last grain of data) of the dimension table that i'm trying to populate with my job design, where i'm extracting data from a mainframe file, by referencing a copybook (cobol FD metadata). I'm trying to map the dimension table's columns to some of the columns from the mainframe file. The first one was the surrogate key assignment and the second the Hierarchy level ID, which is based on another column from the file. I would appreciate if you could shed some light on this

[sylvan_rydes]

Hi All,

I've been reading this post and I have one question. There is a file in common storage and the path is as below-

Common /Sequences/ Initialized, NextVal, SeqFile

I just need to know that if I need to clear SeqFile. How can I do that. Thanks in advance.

Sylvan Rydes

[chulett]

Clear Seqfile? Not sure what you are asking. All you need to do to use these routines is understand how they work and then just... use them. SeqFile is not 'a file in common storage' per se, but the name of the 'Sequence File' in use - i.e. the SDKSequences hashed file - from what I recall.

[sylvan_rydes]

Hi Craig,

Thanks for your response. I actually got it done.

Sylvan Rydes

[Vemireddy_Sasi]

Hi Ram,

Do you have any idea How can we set the initial value for the Sequence using KeyMgtGetNextval?

Thanks and Regards,
Bhaskar

[ArndW]

There is no way to set the initial value to anything other than 1 using the default routine. But you can create your own copy of the Manager/Routines/sdk/KeyMgt/KeyMgtGetNextValue routine and modify it appropriately to start your sequence at some other number.

[chulett]

More of a 'reset' than a 'set', per se, but perhaps it will help:

Code: Select all

UPDATE SDKSequences USING DICT VOC SET F1 = '?' WHERE @ID = 'YourSequenceName';

YourSequenceName is the 'key' to reset.

? is the value to reset it to.