Surrogate Key Assignment

duh_sk8erboi · Participant Joined: 03 Dec 2005 Posts: 4 Points: 48

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 · Participant Joined: 10 Oct 2005 Posts: 178 Points: 1474

HI,

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

Saik

duh_sk8erboi · Participant Joined: 03 Dec 2005 Posts: 4 Points: 48

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 · Participant Joined: 10 Oct 2005 Posts: 178 Points: 1474

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

No need to write any code

Good luck Smile

rasi · Posted: Tue Feb 28, 2006 8:31 pm

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 · Posted: Tue Feb 28, 2006 10:59 pm

duh_sk8erboi wrote:

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

Hi,

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

Thanks,
Ram

duh_sk8erboi · Participant Joined: 03 Dec 2005 Posts: 4 Points: 48

Alrite !!! Very Happy

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 · Posted: Wed Mar 01, 2006 9:01 pm

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/KeyM ...

What's this?

duh_sk8erboi · Participant Joined: 03 Dec 2005 Posts: 4 Points: 48

Thanks a ton craig Very Happy

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 · Participant Joined: 12 Apr 2006 Posts: 47 Points: 349

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 · Posted: Wed Dec 06, 2006 3:56 pm

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 · Participant Joined: 12 Apr 2006 Posts: 47 Points: 349

Hi Craig,

Thanks for your response. I actually got it done.

Sylvan Rydes

Vemireddy_Sasi · Participant Joined: 11 Jul 2007 Posts: 2 Points: 10

ram.m wrote:

duh_sk8erboi wrote:

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

Hi,

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

Thanks,
Ram

Hi Ram,

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

Thanks and Regards,
Bhaskar

ArndW · Posted: Wed Jul 11, 2007 11:39 pm

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 i ...

What's this?

chulett · Posted: Wed Jul 11, 2007 11:45 pm

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

Code:

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

YourSequenceName is the 'key' to reset.

? is the value to reset it to.