Issue with Suurogate key Generation

[Yoga_12]

we are using KeyMgtGetNextValueConcurrent() Utility to generate the surrogate key.
We are receiving Duplicates from the Source feed it suppose to generate the one surrogate key for the combination of natural keys but for each duplicate it is creating the new key.

The hash is having around 10M records

[srimitta]

Code: Select all

suppose to generate the one surrogate key for the combination of natural keys but for each duplicate it is creating the new key.

Before generating Surrogate Keys remove duplicates on you NATURAL KEY COLUMNS using Remove Duplicates stage.

Use same column or set of columns which you are using in KeyMgtGetNextValueConcurrent() utility to remove duplicates in Remove Duplicate stage.

[chulett]

There's no "Remove Duplicates" stage in Server, never mind the fact that they want duplicates.

[chulett]

Yoga - that's the way the routine works, each call to it gets you a unique number. You'll need to hash the incoming records so you know to call it only when you need a new key but use an 'old' one for the duplicates.

[srimitta]

Oh Sorry , I didn't noticed this thread is of Server.

[chulett]

No worries, we all accidentally wander into strange territories on occasion.

[Yoga_12]

My ETL Flow goes like this I am hashing Surrogate key along with Natural keys from the Dimension. Then I am using the Hash file as the Look up and passing the natural keys to the hash and getting the Surrogate key and checking whether it is null i n case of null value i am generating the New key and Updating the Hash value. even though it already exist in the Hash every time it is generating the new Surrogate key .

Dimension --------> Hash
|
| Lkp on hash
Source -------> Transformer --------> UpdationHash in the transformer i am validating the output of the Lookup value an dbased on it i am using KeyMgtGetNextValueConcurrent() utility. the same logic is working for some of the dimensions perfectly in this i am facing problem...

[chulett]

Code: Select all

Dimension --------> Hash
                     | 
                     | Lkp on hash
 Source -------> Transformer --------> UpdationHash

Code tags, people, code tags. Doesn't anyone preview their posts before pulling the trigger?

Post your derivation code. How are you checking the hashed lookup results and branching around the Key routine call based on that result?

[Yoga_12]

let say i am building the territory dim out of the territory feed which is source. The Territory hash is going to hold territory_Name and the territory_Key. That is the first flow

then i am selecting the territory_name from the source and passing to the Hash whcih was built by the previous flow if the Hash has the value for the given natural key it will return the Key otherwise it will return the null value
so i am using the below code to generate the Key value.


if isnull(Lnk_Lkp_TerrHash.TERR_KEY) then KeyMgtGetNextValue('FTB_Terr_Seq') else Lnk_Lkp_TerrHash.TERR_KEY

I think now i am clearly given my Req and you can get the Problem clearly

[chulett]

That part looks fine. Try switching to using the NOTFOUND Link Variable rather than the null check and see if it works more better.