Surrogate Key Assignment
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 4
- Joined: Sat Dec 03, 2005 6:51 pm
- Contact:
Surrogate Key Assignment
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
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
Adrianna D
-
- Participant
- Posts: 4
- Joined: Sat Dec 03, 2005 6:51 pm
- Contact:
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
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Re: Surrogate Key Assignment
Hi,
by using KeyMgtGetNextval u can generate the sequence number in the transformer stage.
Thanks,
Ram
by using KeyMgtGetNextval u can generate the sequence number in the transformer stage.
Thanks,
Ram
ram
-
- Participant
- Posts: 4
- Joined: Sat Dec 03, 2005 6:51 pm
- Contact:
Re: Surrogate Key Assignment
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
Thanks in Advance
Adrianna D
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.
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.
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.
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:duh_sk8erboi wrote:...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???
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.
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 also wrote: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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 4
- Joined: Sat Dec 03, 2005 6:51 pm
- Contact:
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
Adrianna D
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 pm
-
- Participant
- Posts: 2
- Joined: Wed Jul 11, 2007 8:58 pm
Re: Surrogate Key Assignment
Hi Ram,
Do you have any idea How can we set the initial value for the Sequence using KeyMgtGetNextval?
Thanks and Regards,
Bhaskar
Do you have any idea How can we set the initial value for the Sequence using KeyMgtGetNextval?
Thanks and Regards,
Bhaskar
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
More of a 'reset' than a 'set', per se, but perhaps it will help:
YourSequenceName is the 'key' to reset.
? is the value to reset it to.
Code: Select all
UPDATE SDKSequences USING DICT VOC SET F1 = '?' WHERE @ID = 'YourSequenceName';
? is the value to reset it to.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers