We use sequence generator transformation largely in SCDs. Using a sequence generator transformation to generate distinctive main key values may cause efficiency points as a further transformation is required to course of in mapping.
You can use expression transformation to generate surrogate keys in a dimensional desk. Here we’ll see the logic on find out how to generate sequence numbers with expression transformation.
When you employ the reset choice in a sequence generator transformation, the sequence generator makes use of the unique worth of Current Value to generate the numbers. The sequences will all the time begin from the identical quantity.
As an instance, if the Current Value is 1 with reset choice checked, then the sequences will all the time begin from worth 1 for a number of session runs. We will see find out how to implement this reset choice with expression transformation.
Follow the under steps:
- Create a mapping parameter and name it as $$Current_Value. Assign the default worth to this parameter, which is the beginning worth of the sequence numbers.
- Now create an expression transformation and join the supply qualifier transformation ports to the expression transformation.
- In the expression transformation create the under further 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 similar as NEXTVAL port in sequence generator transformation.
Primary Key Values Using Expression and Parameter:
We will see right here find out how to generate the first key values utilizing the expression transformation and a parameter. Follow the under steps:
- Create a mapping to put in writing the utmost worth of main key in the goal to a parameter file. Assign the utmost worth to the parameter ($$MAX_VAL) in this mapping. Create a session for this mapping. This ought to be the primary session in the workflow.
- Create one other mapping the place you wish to generate the sequence numbers. In this mapping, join the required ports to the expression transformation, create the under further ports in the expression transformation and assign the under 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 first key values simply because the sequence generator transformation.
Primary Key Values Using Expression and Lookup Transformations:
Follow the under steps to generate sequence numbers utilizing expression and lookup transformations.
- Create an unconnected lookup transformation with lookup desk as goal. Create a primary_key_column port with kind as output/lookup/return in the lookup ports tab. Create one other port input_id with kind as enter. Now overwrite the lookup question to get the utmost worth of main key from the goal. The question seems to be as
SELECT MAX(primary_key_column) FROM Dimension_table
- Specify the lookup situation as primary_key_column >= input_id
- Now create an expression transformation and join the required ports to it. Now we’ll name the unconnected lookup transformation from this expression transformation. Create the under further 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 desk.