Issue with Suurogate key Generation
Moderators: chulett, rschirm, roy
Issue with Suurogate key Generation
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
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
Yoga Kathirvelu
Working with interesting people is more interesting than just working
Working with interesting people is more interesting than just working
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.
Use same column or set of columns which you are using in KeyMgtGetNextValueConcurrent() utility to remove duplicates in Remove Duplicate stage.
Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.
By William A.Foster
By William A.Foster
There's no "Remove Duplicates" stage in Server, never mind the fact that they want duplicates.
Last edited by chulett on Tue Jul 29, 2008 8:04 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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...
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...
Yoga Kathirvelu
Working with interesting people is more interesting than just working
Working with interesting people is more interesting than just working
Code: Select all
Dimension --------> Hash
|
| Lkp on hash
Source -------> Transformer --------> UpdationHash
Post your derivation code. How are you checking the hashed lookup results and branching around the Key routine call based on that result?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
Yoga Kathirvelu
Working with interesting people is more interesting than just working
Working with interesting people is more interesting than just working