Informatica

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 20 Dec, 2023

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 20 Dec, 2023

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 30 Sep, 2020

Working with Informatica Flatfiles

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

Updated 30 Sep, 2020

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 30 Sep, 2020

Informatica Excel Source

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

Updated 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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 20 Dec, 2023

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 20 Dec, 2023

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 20 Dec, 2023

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 20 Dec, 2023

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 20 Dec, 2023

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 20 Dec, 2023

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 20 Dec, 2023

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 20 Dec, 2023

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 30 Sep, 2020

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 20 Dec, 2023

How to get Informatica Repository Information from Metadata

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

Updated 20 Dec, 2023

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 30 Sep, 2020

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 30 Sep, 2020

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 30 Sep, 2020

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.

Updated 20 Dec, 2023