Sequence Generator Transformation for Unique Key Generation

  Level2 Training , Transformations

The Sequence Generator transformation generates numeric values in a sequential order. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers. In this tutorial lets see a practical implementation of Sequence Generator transformation.

For the demonstration purpose lets consider the below scenario.

Customer source data arrives at each store in a flat file. Each file contains the customer name and other customer details. However, there is no unique id to identify each customer. The unique id for each customer will be generated through the mapping.

Solution 

1. Use a Sequence Generator transformation to generate a unique id for each customer.    
2. Use this generated Customer id as the primary key in the target table.   

Mapping Layout

I. Import Sources, Targets and create a Mapping

Note : Click the link to Learn more on Source Definition and Target Definition.

1. Import source definition for Customers, which is a flat file uploaded on the server.
2. Create a target table, which is similar to the source. Add the CUSTOMER_ID port as a Primary Key. Name the target as Tgt_Customer_x.
3. Create a mapping by the name M_Custid_x.
4. Drag the source definition Customers and target definition for Tgt_Customer_x in the designer workspace.

II. Drag Sources and Targets into the Mapping

Note : Click the link to Learn more on  Mapping Designer.

1. Drag all the source tables into the Mapping Designer.
2. Create the Source Qualifier transformation and link the sources to the transformation.

III. Create a Sequence Generator Transformation

1. Create the Sequence Generator transformation.
1. Select TRANSFORMATION | CREATE and select Sequence Generator or
2. Click on the  icon from the Transformation toolbar.
3. Enter the name as Seq_Custid_x.
2. Set the Start Value, End Value, Increment Value and other attributes as shown below. Check the Reset box.
3. Link NEXTVAL column of Sequence Generator to target table.
4. Link remaining columns from Source qualifier to target table.
5. Your mapping should look like the one shown below.

IV. Load the Target

1. Create a Workflow with the name wf_Custid_x.
2. Create a session task with the name s_Custid_x
3. Run the Workflow.
4. Monitor the Workflow.

V. Verify the Results

Select the data from the target table to see the results. You will see the CUSTOMER_ID starting from 1 and increasing in a sequence.

Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise and subscribe to the mailing list to get the latest tutorials in your mail box.

Share with your friends