Sequence Generator Transformation

The Sequence Generator Transformation object in Astera is used to add sequences of integer values to a dataflow. The sequences can start with any number and have any step, for example, 50, 55, 60, 65, etc.

Astera Data Stack can either create a sequence instantly at the dataflow’s run-time (this is called an in-memory sequence), or it can read sequence control data from a database table as your dataflow is executed.

In the case of in-memory sequences, a sequence always starts at the Start Value provided in the SeqGenerator: Context Information Properties window. In the case of database sequences, the last value used is recorded in the control database, and a new start value is used every time the sequence is invoked. This makes it possible to generate ever-increasing values for the sequence each time the dataflow runs. In effect, such a sequence is a chain of sequences with non-overlapping values.

Use Case

Here, we have retrieved data from the Orders table using a Database Table Source object. We will use the Sequence Generator Transformation object to generate a sequence for the OrderNo field in our source data. Let’s see how this works.

Using the Sequence Generator

1. Drag the Sequence Generator Transformation object from the Transformations section in the Toolbox and drop it onto the dataflow designer.

1. Map the required fields from the source object to a destination object.

Note: We have the Orders table as our source from a Database Table Source. We will map the fields OrderDate, RequiredDate, ShippedDate, ShipVia, and Freight to an Excel Workbook Destination object.

1. To configure the properties of the Sequence Generator Transformation object, right-click on its header and select Properties from the context menu.

This will open the Context Information Properties window.

1. In this window, you can choose between three different types of sequence generations and specify the Sequence Details.

A description of these three methods is given below:

• In Memory: The sequence will be created in memory at the dataflow run-time. The sequence always starts at the specified Start Value in the sequence properties. The Sequence Details are:

  • Start Value – The initial value for the sequence

  • Step – The increment value

• Database Table: The sequence control information for the database table can be managed within Astera through the Manage Sequences option.

  • Connection: Specify the connection to the database where the sequences will be stored

  • Sequence: Select the sequence from the list of available sequences in the database.

Note: To manage database sequences, go to Menu > Tools > Sequences.

• Batch Size: Specifies the minimum number of values to be allocated to the sequence.

• Use Memory Sequence during preview: Prevents the user from breaking the sequence cycle during a data preview by substituting with an in-memory sequence, which does not affect (i.e. increase) the database sequence’s current value.

• Sequence Object - The sequence control information is read from a special database table coming from SQL Server or Oracle database.

  • Connection: Specify the connection to the database that stores your sequences.

  • Sequence: Select the sequence from the list of available sequences.

  • Use Memory Sequence during previews: Prevents the user from breaking the sequence cycle during a data preview by substituting with an in-memory sequence.

Note: In this case, we will use the In-Memory sequence generator option. Let’s specify the Sequence Details as follows:

Start Value: 0

Step: 1

1. In the destination object, a new field will be created where the sequence generator value will be mapped.

Note: In this case, the OrderNo field has been created in the Excel Workbook Destination object.

1. The NextVal field will be mapped to the OrderNo field in the destination object.

1. You can see the output of the Excel destination object in the Data Preview window.

The sequence has been generated in the new field, OrderNo.

This is how the Sequence Generator Transformation is used in Astera.