We use sequence generator transformation mostly in SCDs. Using a sequence generator transformation to generate unique primary key values can cause performance issues as an additional transformation is required to process in mapping.

You can use expression transformation to generate surrogate keys in a dimensional table. Here we will see the logic on how to generate sequence numbers with expression transformation.

When you use the reset option in a sequence generator transformation, the sequence generator uses the original value of Current Value to generate the numbers. The sequences will always start from the same number.

As an example, if the Current Value is 1 with reset option checked, then the sequences will always start from value 1 for multiple session runs. We will see how to implement this reset option with expression transformation.

Follow the below steps:

We will see here how to generate the primary key values using the expression transformation and a parameter. Follow the below steps:

Follow the below steps to generate sequence numbers using expression and lookup transformations.

You can use expression transformation to generate surrogate keys in a dimensional table. Here we will see the logic on how to generate sequence numbers with expression transformation.

**Sequence Generator Reset Option**:When you use the reset option in a sequence generator transformation, the sequence generator uses the original value of Current Value to generate the numbers. The sequences will always start from the same number.

As an example, if the Current Value is 1 with reset option checked, then the sequences will always start from value 1 for multiple session runs. We will see how to implement this reset option with expression transformation.

Follow the below steps:

- Create a mapping parameter and call it as $$Current_Value. Assign the default value to this parameter, which is the start value of the sequence numbers.
- Now create an expression transformation and connect the source qualifier transformation ports to the expression transformation.
- In the expression transformation create the below additional ports and assign the expressions:

v_seq (variable port) = IIF(v_seq>0,v_seq+1,$$Current_Value) o_key (output port) = v_seq

- The v_seq port generates the numbers same as NEXTVAL port in sequence generator transformation.

**Primary Key Values Using Expression and Parameter**:We will see here how to generate the primary key values using the expression transformation and a parameter. Follow the below steps:

- Create a mapping to write the maximum value of primary key in the target to a parameter file. Assign the maximum value to the parameter ($$MAX_VAL) in this mapping. Create a session for this mapping. This should be the first session in the workflow.
- Create another mapping where you want to generate the sequence numbers. In this mapping, connect the required ports to the expression transformation, create the below additional ports in the expression transformation and assign the below expressions:

v_cnt (variable port) = v_cnt+1 v_seq (variable port) = IIF( ISNULL($$MAX_VAL) OR $$MAX_VAL=0,1,v_cnt+$$MAX_VAL) o_surrogate_key (output port) = v_seq

- The o_surrogate_key port generates the primary key values just as the sequence generator transformation.

**Primary Key Values Using Expression and Lookup Transformations**:Follow the below steps to generate sequence numbers using expression and lookup transformations.

- Create an unconnected lookup transformation with lookup table as target. Create a primary_key_column port with type as output/lookup/return in the lookup ports tab. Create another port input_id with type as input. Now overwrite the lookup query to get the maximum value of primary key from the target. The query looks as

SELECT MAX(primary_key_column) FROM Dimension_table

- Specify the lookup condition as primary_key_column >= input_id
- Now create an expression transformation and connect the required ports to it. Now we will call the unconnected lookup transformation from this expression transformation. Create the below additional port in the expression transformation:

v_cnt (variable port) = v_cnt+1 v_max_val (variable port) = IIF(v_cnt=1, :LKP.lkp_trans(1), IIF(ISNULL(v_max_val) or v_max_val=0, 1, v_max_val)) v_seq (variable port) = IIF(ISNULL(v_max_val) or v_max_val=0, 1, v_cnt+v_max_val) o_primary_key (output port) = v_seq

- The o_primary_key port generates the surrogate key values for the dimension table.

## No comments:

## Post a Comment