It is possible to generate sequential surrogate key in the target table without the use of an Informatica Sequence Generator transformation. Using this option, one can avoid any gap in the sequence numbers of the surrogate key.

Sequencing Logic

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 JOB_ORDER_TRANS.
  • 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

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

Here, $$LkpCol_Nm=JOB_ORDER_TRANS and $$LkpCol_Nm=Order_Key passed from the Parameter file.

 

Reusable Expression:

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)

Port I_DUMMY is just a dummy port to get the link from the upstream transformation.

 

Mapping Snapshot:

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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • APEAR - A tool for automating Informatica Performance Tuning

    DWBIConcepts is launching APEAR – an Automated Performance Evaluation and Reporting tool for Informatica. As the name suggests, this tool will help you tune the performance of Informatica sessions fully automatically. Now don't waste your precious...

  • Using Informatica Normalizer Transformation

    Normalizer transformation is a native transformation in Informatica that can ease many complex data transformation requirements. Learn how to effectively use normalizer in this tutorial.

  • Informatica Performance Tuning - Complete Guide

    This article is a comprehensive guide to the techniques and methodologies available for tuning the performance of Informatica PowerCentre ETL tool. It's a one stop performance tuning manual for Informatica.

  • The benefit and disadvantage of Informatica Persistent Cache Lookup

    Persistent cache may be your choice of caching when it comes to lookup performance. But you should be aware of the hazards of persistent cache as well.

  • All about Informatica Lookup

    A Lookup is a Passive, Connected or Unconnected Transformation used to look up data in a relational table, view, synonym or flat file. The integration service queries the lookup table to retrieve a value based on the input source value and the...

  • When to use Informatica Stored Procedure Transformation

    There are loads of mis-information spreaded across Internet on good use-cases of Informatica Stored Procedure transformation. Exactly where do you use this transformation? This article finds out.

  • Implementing Informatica Partitions

    Identification and elimination of performance bottlenecks will obviously optimize session performance. After tuning all the mapping bottlenecks, we can further optimize session performance by increasing the number of pipeline partitions in the...

  • Pushdown Optimization In Informatica

    Pushdown Optimization which is a new concept in Informatica PowerCentre, allows developers to balance data transformation load among servers. This article describes pushdown techniques.

  • What is Active Lookup Transformation

    Informatica 9x allows us to configure Lookup transformation to return multiple rows. So now we can retrieve multiple rows from a lookup table thus making Lookup transformation an Active transformation type.

  • Challenges of Informatica Partitioning with Sequencing

    In the previous article, we showed how surrogate keys can be generated without using Sequence Generator transformation. However, if Informatica partitioning is implemented for such cases, then since each partition pipeline will call the lookup...