Data Integration

Informatica

This section contains a number of articles and tutorials on Informatica PowerCenter™ and PowerMart™ ETL tool.

Pages

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 lookup condition.

Updated on May 14, 2019

Aggregation without 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!

Updated on May 14, 2019

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.

Updated on May 14, 2019

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.

Updated on May 14, 2019

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 table changes?

Updated on May 14, 2019

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.

Updated on May 14, 2019

Working with Informatica Flatfiles

In this article series we will try to cover all the possible scenarios related to flatfiles in Informatica.

Updated on May 14, 2019

Loading Flatfiles delimited by comma and double quotes

In this article let us take up a very trivial but an important aspect that we as DW developers usual face. This is related to loading flat file sources. Whenever we have flat file sources we usual ask source systems for a specific type of field delimiters.

Updated on May 14, 2019

Informatica Excel Source

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

Updated on May 14, 2019

Informatica Reject File - How to Identify rejection reason

When we run a session, the integration service may create a reject file for each target instance in the mapping to store the target reject record. With the help of the Session Log and Reject File we can identify the cause of data rejection in the session.

Updated on May 14, 2019

Implementing Informatica Incremental Aggregation

Using incremental aggregation, we apply captured changes in the source data (CDC part) to aggregate calculations in a session. If the source changes incrementally and we can capture the changes, then we can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to delete previous loads data, process the entire source data and recalculate the same data each time you run the session.

Updated on May 14, 2019

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 pre-built on the database which can be connected through Informatica.

Updated on May 14, 2019

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.

Updated on May 14, 2019

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.

Updated on May 14, 2019

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.

Updated on May 14, 2019

Calling C executable from Java Transform

We are going to do is, to call C++ Executable from Informatica, using Passive Java Transform and capture the output of the C++ using Java and write the result to corresponding target column.

Updated on May 14, 2019

Stop Hardcoding- Follow Parameterization Technique

This article tries to minimize hard-coding in ETL, thereby increasing flexibility, reusability, readabilty and avoides rework through the judicious use of Informatica Parameters and Variables.

Updated on May 14, 2019

CDC Implementation using Informatica Variable

This article explains the Change Data Capture mechanism using Informatica Mapping Variable. We can use the Informatica Mapping Variable to extract the CDC data without using any other custom table. Here it goes.

Updated on May 14, 2019

CDC Implementation using Flatfile

This article shows how to use a flatfile to implement Change data Capture. Suppose we want to maintain the last extraction date in a flatfile, based on that value we want to capture the changed data of our business table.

Updated on May 14, 2019

Implementing SCD2 in Informatica Using ORA_HASH at Source

In this article we shall see how we can implement SCD type2 in Informatica using ORA_HASH, which is an ORACLE function that computes hash value for a given expression. We can use this feature to find the existence of any change in any of the SCD column.

Updated on May 14, 2019

Comparing Performance of SORT operation (Order By) in Informatica and Oracle

In this "DWBI Concepts' Original article", we put Oracle database and Informatica PowerCentre to lock horns to prove which one of them handles data SORTing operation faster. This article gives a crucial insight to application developer in order to take informed decision regarding performance tuning.

Updated on May 14, 2019

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 informed decision regarding performance tuning.

Updated on May 14, 2019

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.

Updated on May 14, 2019

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.

Updated on May 14, 2019

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. So let’s delve deeper into this.

Updated on May 14, 2019

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 transformation if any of the following condition is true –

Updated on May 14, 2019

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

Updated on May 14, 2019

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 session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.

Updated on May 14, 2019

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.

Updated on May 14, 2019

PowerCenter SOA Components

PowerCenter has a Service-Oriented Architecture that provides the ability to scale services and share resources across multiple machines. Let us know more about the components and services associated with Powercenter.

Updated on May 14, 2019

Informatica Metadata Tables - Overview and Tutorial

Informatica PowerCentre stores all the information about mapping, session, transformation, workflow etc. in a set of database tables called metadata tables. While these tables are used internally by Informatica, one can get useful information by accessing it separately

Updated on May 14, 2019

How to get Informatica Repository Information from Metadata

Some metadata views can be very handy to get Informatica Repository Information. Know How.

Updated on May 14, 2019

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.

Updated on May 14, 2019

Useful Informatica Metadata Repository Queries

Informatica metadata repository stores and maintains information about all the objects in Informatica. They contain details of connection information, users, folders, mappings, sources, targets etc. These information can serve many purposes while accessed through external SQL query.

Updated on May 14, 2019

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 time any longer trying to figure out how to speed up your Informatica sessions.

Updated on May 14, 2019

Generate Surrogate Key without using Sequence Generator

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.

Updated on May 14, 2019

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 simultaneously, we will end up generating duplicate sequence numbers. In this article, we will see how we may resolve this issue.

Updated on May 14, 2019

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 need any help, do not hesitate to post your questions in our Community Forum and our experts or other community members will answer your questions.

Updated on May 14, 2019
© DWBI