reset surrogatekey

george s thazhath · Post by **george s thazhath** » Sat Sep 16, 2006 5:46 am

i want to reset sdknextseqvalue how to do it
kindly give the way

ray.wurlod · Post by **ray.wurlod** » Sat Sep 16, 2006 6:19 am

Welcome aboard. :D
Did you think to search the forum? This question has been asked and answered in the past.
Essentially you need to be aware that the sequence is a record in a Repository table called SDKSequences; the name of the sequence is the key to the record, and the next key value is stored in the first non-key field.
However, SDKSequences does not have a fully-defined dictionary (metadata) so that you need some indirection in your update mechanism. For example

Code: Select all

 UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = '<<SequenceName>>';

kduke · Post by **kduke** » Sat Sep 16, 2006 7:42 am

We use a naming convention that the column name is the key. So CustomerID becomes the key for CustomerDim. We reseed these values right before we use them. I provided this job in EtlStats. It is called ETL_Reseed_SDKSequences. If you do a search on "reseed" then you will find other posts about this topic. This job is a multiple instance job and runs before every dimension table gets loaded with new records. It does not slow down the ETL. It can be run when the data is being extracted from the source. Besides it is extremely fast because these fields are usually the primary key. So how long does it take to run this SQL when CustomerID is the primary key.

Code: Select all

select max(CustomerID) + 1 As NextKey from CuistomerDim

Your ETL should control these hashed files. I am sure Ray has a job just like this. I also like that this forces a naming convention on the key in SDKSequences.