Question Re: Generated Key Column and Key Generation Transform in Technology Q&A

Generated Key Column and Key Generation Transform

rajan_burad — Thu, 29 Dec 2016 11:52:29 GMT

Hello,

I implemented a scenario in which I used source -> query -> table_comparison -> target. In target I added Cust_gen column and after job execution I found that it contains null values which is correct as I have not added any key generation transform to get the values.

Now after this I added Key generation transform to check how it actually works and added a row in source and after execution of job i found that cust_gen column has a set of values for every record but I have read that it looks for maximum existing value and then assigns value to newly inserted records. So as per my understanding the maximum value in source is 501 and the value of cust_gen should start from 501.

Can someone please help me that how does this Key generation transform works and also why every record has got values of cust_gen.

Regards,

Rajan

Re: Generated Key Column and Key Generation Transform

Former Member — Fri, 30 Dec 2016 11:03:06 GMT

Key_gen transform looks for the max value in the target, not in the source.

Re: Generated Key Column and Key Generation Transform

rajan_burad — Sat, 31 Dec 2016 10:56:25 GMT

Thanks Ravi!

But then why is it generating random values under Cust_gen column. Also now if suppose I insert 2 new records in my source and set generate key column to Cust_gen(which is not present in source) then what will be the output.

Can you please help!

One more clarification required: - Under generated key column we have to select that common column(which has unique value or is primary key) which is present in both source and target, right?

Re: Generated Key Column and Key Generation Transform

Former Member — Thu, 05 Jan 2017 11:40:20 GMT

Hi Rajan,

The surrogate key column is created in BODS (in query transform) and this column should be made primary key and use this in Key generation transform. It will create keys serially. it will not create random keys.

Please let me know if you need more info

Re: Generated Key Column and Key Generation Transform

rajan_burad — Sun, 08 Jan 2017 08:17:57 GMT

Hello Ravi,

I added this surrogate column (Cust_gen) in query transform and same applied in TC and Key_gen and ran the job, but still found that its giving null values.

Also I mapped the input cust_id column with this surrogate key.

I have attached the screenshots of my job.

Please let me know where did I went wrong.

Thanks much for your help!! 🙂error-03.png error-04.png error-05.png

Re: Generated Key Column and Key Generation Transform

Former Member — Thu, 26 Jan 2017 06:35:26 GMT

Hi Rajan,

Pass 1 in the mapping for cust_gen in query transform. remove cust_gen from generated key column in Table comparison and also please picture of key_gen transform.