The transaction table
JOB_ORDER_TRANS has details about Job Orders created by the Marketing team in a product company. This transaction table has a sequence key (PK) in order to perform an update post the data load as demanded by the business. This PK is being populated via below mentioned method
Following sequencing logic uses “reusable expression and lookup transformation” coupled together to generate the Surrogate Key/Primary Key.
- Reusable Lookup transformation gets maximum Primary Key from the target table and passes the value to the calling Reusable expression. In this case, Target table is the transaction table
- Table Name and Column Name values in Reusable Lookup transformation are parameterized so that this combination can be used to generate sequences wherever required in other mappings.
Reusable lookup has one I/P port (dummy), one Return Port to return the maximum Primary Key value and one Lookup dummy port to match with I/P dummy port.
Lookup Override used in this transformation is shown below:-
SELECT 1 AS DUMMY, NVL(MAX($$LkpCol_Nm),1) as TARGET_PK FROM $$LkpTbl_Nm
$$LkpCol_Nm=Order_Key passed from the Parameter file.
This Reusable expression calls the unconnected reusable lookup to get maximum Primary Key from the target table by passing dummy I/P. This lookup is called only once and repeated calls to this lookup is avoided by generating sequence in expression itself after the initial call.
The logic in the port
V_LKP_MAX_PK is as follows:-
IIF (V_LKP_MAX_PK=0,:LKP.LKP_TARGET(1), V_LKP_MAX_PK+1)
I_DUMMY is just a dummy port to get the link from the upstream transformation.
Below picture shows the usage of reusable transformations in the mapping that loads Job Order Transaction table.
In the next article, we will learn about various challenges that we may face when implementing pass-through partitioning in this mapping