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.

  • Implementing Informatica Persistent Cache

    You must have noticed that the "time" Informatica takes to build the lookup cache can be too much sometimes depending on the lookup table size/volume. Using Persistent Cache, you may save lot of your time. This article describes how to do it.

  • How to Tune Performance of Informatica Aggregator Transformation

    Similar to what we discussed regarding the Performance Tuning of Joiner Transformation, the basic rule for tuning aggregator is to avoid aggregator transformation altogether...

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

  • Informatica Java Transformation

    Feel the Power of Java programming language to transform data in PowerCenter Informatica. Java Transformation in Informatica can be used either in Active or Passive Mode.

  • How to Tune Performance of Informatica Joiner Transformation

    Joiner transformation allows you to join two heterogeneous sources in the Informatica mapping. You can use this transformation to perform INNER and OUTER joins between two input streams. For performance reasons, I recommend you ONLY use JOINER...

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

  • Informatica Join Vs Database Join

    In this yet another "DWBI Concepts' Original article", we test the performance of Informatica PowerCentre 8.5 Joiner transformation versus Oracle 10g database join. This article gives a crucial insight to application developer in order to take...

  • Informatica Excel Source

    This article is a guide on how to Unload data from EXCEL file system to target relational database using Informatica.

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

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