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.

  • Informatica Dynamic Lookup Cache

    A LookUp cache does not change its data once built. But what if the underlying table upon which lookup was done changes the data after the lookup cache is created? Is there a way so that the cache always remain up-to-date even if the underlying...

  • Using Informatica Stored Procedure Transformation

    Stored Procedure Transformation - as the name suggests is used to execute stored procedures through Informatica ETL. It can also be used to call functions to return calculated values. The Stored Procedures that are to be executed should be...

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

  • How to Tune Performance of Informatica Lookup Transformation

    To me, look-up is the single most important (and difficult) transformation that we need to consider while tuning performance of Informatica jobs. The choice and use of correct type of Look-Up can dramatically vary the session performance in...

  • Best Informatica Interview Questions & Answers

    Welcome to the finest collection of Informatica Interview Questions with standard answers that you can count on. Read and understand all the questions and their answers below and in the following pages to get a good grasp in Informatica. If you...

  • How to get Folders and Mapping names from Informatica Metadata Query

    We can use OPB_MAPPING and OPB_SUBJECT tables residing under informatica Repository to obtain information about all the mappings under each Informatica Folder. Following SQL query shows you how to do it.

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

  • Informatica Tuning - Step by Step Approach

    This is the first of the number of articles on the series of Data Warehouse Application performance tuning scheduled to come every week. This one is on Informatica performance tuning.

  • Aggregation with out Informatica Aggregator

    Since Informatica process data on row by row basis, it is generally possible to handle data aggregation operation even without an Aggregator Transformation. On certain cases, you may get huge performance gain using this technique!

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