Sequence Generator Transformation for Unique Key Generation
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.
- Use a Sequence Generator transformation to generate a unique id for each customer.
- Use this generated Customer id as the primary key in the target table.
I. Import Sources, Targets and create a Mapping
- Import source definition for Customers, which is a flat file uploaded on the server.
- 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.
- Create a mapping by the name M_Custid_x.
- 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.
- Drag all the source tables into the Mapping Designer.
- Create the Source Qualifier transformation and link the sources to the transformation.
III. Create a Sequence Generator Transformation
- Create the Sequence Generator transformation.
- Select TRANSFORMATION | CREATE and select Sequence Generator or
- Click on the icon from the Transformation toolbar.
- Enter the name as Seq_Custid_x.
- Set the Start Value, End Value, Increment Value and other attributes as shown below. Check the Reset box.
- Link NEXTVAL column of Sequence Generator to target table.
- Link remaining columns from Source qualifier to target table.
- Your mapping should look like the one shown below.
IV. Load the Target
- Create a Workflow with the name wf_Custid_x.
- Create a session task with the name s_Custid_x
- Run the Workflow.
- 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.