Sequence Generator Transformation

Sequence Generator is an Passive and Connected.

The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.

It contains two output ports that you can connect to one or more transformations. The Integration Service generates a block of sequence numbers each time a block of rows enters a connected transformation. If you connect CURRVAL, the Integration Service processes one row in each block. When NEXTVAL is connected to the input port of another transformation, the Integration Service generates a sequence of numbers. When CURRVAL is connected to the input port of another transformation, the Integration Service generates the NEXTVAL value plus the Increment By value.

You can make a Sequence Generator reusable, and use it in multiple mappings. You might reuse a Sequence Generator when you perform multiple loads to a single target.

Note: Generally we use it as Surrogate Key in Datawarehousing while maintaining history, we need Unique Identification number other than Primary Key. we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key.

Common Uses for Sequence Generator

You can complete the following tasks with a Sequence Generator transformation:

• Create keys.
• Replace missing values.
• Cycle through a sequential range of numbers.

Creating Keys

We can create primary or foreign key values with the Sequence Generator transformation by connecting the NEXTVAL port to a target or downstream transformation. You can use a range of values from 1 to 9,223,372,036,854,775,807 with the smallest interval of 1.

When we create primary or foreign keys, use the Cycle option to prevent the Integration Service from creating duplicate primary keys. You might do this by selecting the Truncate Target Table option in the session properties or by creating composite keys.

To create a composite key, you can configure the Integration Service to cycle through a smaller set of values. For example, if you have three stores generating order numbers, you might have a Sequence Generator cycling through values from 1 to 3, incrementing by 1. When you pass the following set of foreign keys, the generated values then create unique composite keys:

COMPOSITE_KEY	ORDER_NO
1	12345
2	12345
3	12345
1	12346
2	12346
3	12346

Sequence Generator Ports

The Sequence Generator transformation has two output ports: NEXTVAL and CURRVAL. You cannot edit or delete these ports. Likewise, you cannot add ports to the transformation.

NEXTVAL

Connect NEXTVAL to multiple transformations to generate unique values for each row in each transformation. Use the NEXTVAL port to generate sequence numbers by connecting it to a downstream transformation or target. You connect the NEXTVAL port to generate the sequence based on the Current Value and Increment By properties. If the Sequence Generator is not configured to cycle through the sequence, the NEXTVAL port generates sequence numbers up to the configured End Value.

Note: When you run a partitioned session on a grid, the Sequence Generator transformation skips values depending on the number of rows in each partition.

CURRVAL

CURRVAL is NEXTVAL plus the Increment By value. You typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation. When a row enters a transformation connected to the CURRVAL port, the Integration Service passes the last created NEXTVAL value plus one.

Note: When you run a partitioned session on a grid, the Sequence Generator transformation might skip values depending on the number of rows in each partition.

Sequence Generator Transformation Properties

The Sequence Generator transformation is unique among all transformations because you cannot add, edit, or delete the default ports, NEXTVAL and CURRVAL.

Start Value and Cycle

Use Cycle to generate a repeating sequence, such as numbers 1 through 12 to correspond to the months in a year.

To cycle the Integration Service through a sequence:

1. Enter the lowest value in the sequence that you want the Integration Service to use for the Start Value.
2. Enter the highest value to be used for End Value.
3. Select Cycle.

As it cycles, the Integration Service reaches the configured end value for the sequence, it wraps around and starts the cycle again, beginning with the configured Start Value.

Sample Mapping:

1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer

3. Select Mappings --> Create

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_seq_number

 

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Tabledefinitions.

5. Select 'Transformation' from Menu --> Create

a) That will appear you 'Select the transformation type to create:'

b) Select 'Sequence Generator' from drop down and 'Enter a new name for this transformation:' as "seq_employee"

c) Click 'Create' and 'Done'

or

a) Click on Sequence Generator Transformation icon marked below in below snapshot.

b) Click in the workspace in Mapping Designer.

c) Select SEQTRANS in workspace and Right Click --> Edit.

d) In Transformation tab --> Click on 'Rename' highlighted above which will pop-up 'Rename Transformation'. Enter the Transformation Name: "seq_employee"

e) Click on 'OK'

f) Click on 'Apply' and 'OK'.

Properties Tab:

Start Value: By default it is 0, It starts sequence generating values up to Maximum value is 9,223,372,036,854,775,806.

Increment By: By Default is 1, It is difference between two consecutive values from the NEXTVAL port and Maximum value is 2,147,483,647.

End Value: Maximum value is 9,223,372,036,854,775,807.

Current Value: Integration Service to use as the first value in the sequence, the value must be greater than or equal to the start value and less than the end value.

b If enabled, the Integration Service cycles through the sequence range.

Number of Cached Values: Number of sequential values the Integration Service caches at a time.

Reset: Integration Service generates values based on the original current value for each session, Otherwise, the Integration Service updates the current value to reflect the last-generated value for the session plus one, and then uses the updated current value as the basis for the next session run. Disabled for reusable Sequence Generator transformations.

6). Drag required ports from 'SQ_EMP' Source Qualifier and 'NEXTVAL' from 'Employee_Seqno' Sequence Generator Transformation to Employee_Seqno target.

7) Select 'Mapping' from Menu --> Validate. 

8) Select 'Repository' from Menu --> Save.

 

Note:

1. A reusable sequence generator can be used in multiple mappings.
2. When you run a partitioned session on a grid, the Sequence Generator transformation skips values depending on the number of rows in each partition.
3. If you configure the Sequence Generator to Reset, the Integration Service uses the current value as the basis for the first generated value for each session.
4. Reset is disabled for reusable Sequence Generator transformations.