/wp-content/uploads/2016/06/diving_979866.png

Vast is an Ocean,So is vast the World of Knowledge. With my diving suit packed, loaded with imaginative visions, and lots of curiosity, started diving deep into the world of BODS.Lots of work is going on. Got attracted towards the “Key_Generation” transform and was fascinated at its features.Now it was time for me to fuse and adapt myself into its world.

THE KEY_GENERATION TRANSFORM:-

This transform is categorized under the “Data Integrator Transforms”. This generates new keys for source data, starting from a value based on existing keys in the table we specify.


If needed to generate Artificial keys in a table, the Key_Generation transform looks up the maximum existing key value from a table and   uses it as the starting value to generate new keys.


The transform expects the generated key column to be part of the input schema.


STEPS TO USE KEY GENERATION TRANSFORM:-

    Scenario:- Here the target data source for which the keys is needed to be added, have certain newly added rows without a Customer_ID. This could be easily understood in the following snap:-

SurkeysGeneratd.png

Our aim here is to automatically generate the keys(Customer_ID) in this case , for the newly inserted records which have no Customer_Id. Accordingly we have taken the following as our input (the modified data without Customer_ID)

INPUT DATA (to be staged in the db):-

KeyGenInput(Modfd).png

TARGET TABLE(which contains the data initially contained in the source table before the entry of new records in the database):-

Target(InitialSourceData).png

THE GENERATED DATA FLOW:-

TheFlow.png

CONTENT OF SOURCE DATA:- (containing the modified entry alone)

ContentInpt.png

CONTENT OF QUERY_TRANSFORM:-

ContentsQuery.png

CONTENT OF THE KEY_GENERATION TRANSFORM:-

ContentsKeyGenrtn.png

THE CONTENTS OF THE TARGET TABLE PRIOR JOB EXECUTION:-

TargetBfrLd.png

The JOB_EXECUTION:-

JobExecution.png

THE OUTPUT AFTER THE JOB EXECUTION:-

Output.png

We can now see from the output how Keys have been generated automatically to those records which did not have the Customer_ID initially.

I explored this  little process of the Key_Generation transform, and it seems a savior at times when huge amount of data have the missing entries(wrt to the keys or any sequential column fields).

Now its time to go back to the surface of waters……..

               dive back.png

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. W. Huyser

    Dear Suresh.

    Thank you for the informative piece. I have some questions regarding the input data. The technical manuals say that it accepts only INSERT rows.

    However, does that mean, that should there be a map operation transform before the key generation, and all rows are set update, that the key generation will not add the key?

    (0) 
  2. Moumita Maity Post author

    Hi Huyser
    Sorry for the delayed response. Thanks a lot for your question.Even similar questions I had, and still have. What I was trying to do was if we delete few rows from the existing rows,and insert new rows, how will the key generation work. However I was stuck in the middle. Requesting you to try that out and let us know:)

    Regards
    Thanks

    (0) 

Leave a Reply