Removing ports from an existing mapping - Do's and Dont's

Recently, a colleague called in for an issue that he was facing with an Informatica mapping. Let me recreate that situation here -

There is a mapping that get n ports from a source and loads m ports to a target. Standard stuff, nothing special or fishy around there. There comes a change request that says, such and such x number of columns are not required in the target, and since there would be a sure performance penalty for carrying through extra data (however small), the mapping should be changed to remove those ports from the pipeline.

What this gentleman did was to remove the connections for those ports from the source qualifier onwards. That saved him from changing and re-importing the source definition.

However, the mapping execution failed, complaining about a certain error situation. Thats when I got the call :)

whether I could discuss and fix it is something else, but what was the reason of the error - ?  

The very fact that if you are bringing in certain ports in the source qualifier, you HAVE to take them forward.  Thats a rule from informatica's side. Which means, if you are pulling up n ports from the source, you HAVE to expose all those n ports going away from the source qualifier. Or, putting it differently, in whichever way you create a set of ports in your source qualifier, all of them have to be consumed by some transformation object. Not a single one can be left unconnected, on the input side or the output side. Simple.

That was the reason, and the solution was fairly simple, to reduce his effort, either remove the ports from source qualifier as well, or just carry them forward to one transformation. and then drop them onwards.

Having said that, the very reason for which this whole change was initiated, the performance gain that would come through by not having the extra ports being carried forward, would come only when you remove the un-wanted ports from the source itself.

Finding Informatica domain name

Recently I came across a situation where the customer people had provided us with informatica server hostname, but not the domain name, nor the port for domain.

In such a case, we lost quite some time figuring out how to go through the domain configuration. That's when I started thinking about the alternates for finding the domain name information from the system (assuming different access levels)

If the repository database access is available, i.e. you can access the informatica repository database, you can use the following query to get the domain name out.

select pos_name
from PO_DOMAINSERVICECONFIG

 This sql will need to be run in the schema where the domain repository has been created.

On another approach, if the database access is not there, and the informatica server access is available, another file, domains.infa in the $PM_HOME equivalent directory will be able to provide information on domain name/port etc..

About the port

Though the installations process allows customization of the ports for domain, many installations keep the default as is.  In any case, a simple telnet to the host on the suspicious port will confirm whether the port is open or not.

In my example situation, it turned out to be the default 6005.

Informatica Questions from a friend - Part 2 - Schedulers

Need of Scheduling and Commonly used Schedulers

Any and all Data warehousing environments need some kind of scheduler setup to enable
jobs being run at periodic intervals without human intervention.  Another important feature
is the repeatability of the jobs set up such.  Without the help of a scheduler, things would
become very ad-hoc and thus prone to errors and messups. 

Oracle provides an built in scheduling facility, accessible through its dbms_scheduler package.
Unix provides basic scheduling facility using cron command. Similarly, Informatica also 
provides basic scheduling facilities in the Workflow Manager client.
 

The features provided by these scheduling tools are fairly limited, often limited to launching
a job at a given time, providing basic dependency management etc. 
 

However, in real time data warehousing solutions, the required functionality is lot more 
sophisticated than whats offered by these basic features.  Therefore, the need for full 
fledged scheduling tools, e.g. Tivoli Workload Scheduler, Redwood Cronacle, Control-M, 
Cisco Tidal etc..
 

Most of these tools provide sophisticated launch control, dependency management features 
and therefore allow the data warehouse to be instrumented at finer levels.
 

Some of the tools, e.g. Tidal for informatica and Redwood for Oracle, provide support for
the Tools' API as well, therefore integrating even better with the corresponding tool.  

Informatica Questions from a friend - Part 1

HOW TO USE A COBOL FILE FOR TRANSFORMATION

Informatica allows reading data from cobol copybook formatted data files. These files mostly 
come from mainframe based source systems. Given that many of the world's leading business 
systems still use IBM Mainframe as their computing systems, e.g. airlines, banks, insurance 
companies etc, these systems act as a major source of information for Data warehouses, 
and thus to our Informatica mappings.  

For using a cobol copy book structure as a source, you'd have to put that copybook in a 
empty skeleton cobol program. 

IDENTIFICATION DIVISION.
PROGRAM-ID. RAGHAV.

ENVIRONMENT DIVISION.
SELECT FILE-ONE ASSIGN TO "MYFILE". 

DATA DIVISION.
FILE SECTION.
FD FILE-ONE.

COPY "RAGHAV_COPYBOOK.CPY".

WORKING-STORAGE SECTION.

PROCEDURE DIVISION.

STOP RUN. 

The copybook file can by a plain record structure.
Read more about defining copybooks around here.

Partitioning in Informatica

Sourced from Internet

All transformations have some basic counters that indicate the number of input rows, output rows, and error rows.

Source Qualifier, Normalizer, and target transformations have additional counters that indicate the efficiency of data moving into and out of buffers. Use these counters to locate performance bottlenecks.

Some transformations have counters specific to their functionality. For example, each Lookup transformation has a counter that indicates the number of rows stored in the lookup cache.

When you view the performance details file, the first column displays the transformation name as it appears in the mapping, the second column contains the counter name, and the third column holds the resulting number or efficiency percentage. If you use a Joiner transformation, the first column shows two instances of the Joiner transformation:

	<Joiner transformation> [M]. Displays performance details about the master pipeline of the Joiner transformation.
	<Joiner transformation> [D]. Displays performance details about the detail pipeline of the Joiner transformation.

When you create multiple partitions, the Integration Service generates one set of counters for each partition. The following performance counters illustrate two partitions for an Expression transformation:

Transformation Name	Counter Name	Counter Value
EXPTRANS [1]	Expression_input rows	8
	Expression_output rows	8
EXPTRANS [2]	Expression_input rows	16
	Expression_output rows	16

Note: When you increase the number of partitions, the number of aggregate or rank input rows may be different from the number of output rows from the previous transformation.

The following table describes the counters that may appear in the Session Performance Details area or in the performance details file:

Transformation	Counters	Description
Aggregator and Rank Transformations	Aggregator/Rank_inputrows	Number of rows passed into the transformation.
	Aggregator/Rank_outputrows	Number of rows sent out of the transformation.
	Aggregator/Rank_errorrows	Number of rows in which the Integration Service encountered an error.
	Aggregator/Rank_readfromcache	Number of times the Integration Service read from the index or data cache.
	Aggregator/Rank_writetocache	Number of times the Integration Service wrote to the index or data cache.
	Aggregator/Rank_readfromdisk	Number of times the Integration Service read from the index or data file on the local disk, instead of using cached data.
	Aggregator/Rank_writetodisk	Number of times the Integration Service wrote to the index or data file on the local disk, instead of using cached data.
	Aggregator/Rank_newgroupkey	Number of new groups the Integration Service created.
	Aggregator/Rank_oldgroupkey	Number of times the Integration Service used existing groups.
Lookup Transformation	Lookup_inputrows	Number of rows passed into the transformation.
	Lookup_outputrows	Number of rows sent out of the transformation.
	Lookup_errorrows	Number of rows in which the Integration Service encountered an error.
	Lookup_rowsinlookupcache	Number of rows stored in the lookup cache.
Joiner Transformation (Master and Detail)	Joiner_inputMasterRows	Number of rows the master source passed into the transformation.
	Joiner_inputDetailRows	Number of rows the detail source passed into the transformation.
	Joiner_outputrows	Number of rows sent out of the transformation.
	Joiner_errorrows	Number of rows in which the Integration Service encountered an error.
	Joiner_readfromcache	Number of times the Integration Service read from the index or data cache.
	Joiner_writetocache	Number of times the Integration Service wrote to the index or data cache.
	Joiner_readfromdisk*	Number of times the Integration Service read from the index or data files on the local disk, instead of using cached data.
	Joiner_writetodisk*	Number of times the Integration Service wrote to the index or data files on the local disk, instead of using cached data.
	Joiner_readBlockFromDisk**	Number of times the Integration Service read from the index or data files on the local disk, instead of using cached data.
	Joiner_writeBlockToDisk**	Number of times the Integration Service wrote to the index or data cache.
	Joiner_seekToBlockInDisk**	Number of times the Integration Service accessed the index or data files on the local disk.
	Joiner_insertInDetailCache*	Number of times the Integration Service wrote to the detail cache. The Integration Service generates this counter if you join data from a single source.
	Joiner_duplicaterows	Number of duplicate rows the Integration Service found in the master relation.
	Joiner_duplicaterowsused	Number of times the Integration Service used the duplicate rows in the master relation.
All Other Transformations	Transformation_inputrows	Number of rows passed into the transformation.
	Transformation_outputrows	Number of rows sent out of the transformation.
	Transformation_errorrows	Number of rows in which the Integration Service encountered an error.
*The Integration Service generates this counter when you use sorted input for the Joiner transformation. **The Integration Service generates this counter when you do not use sorted input for the Joiner transformation.

If you have multiple source qualifiers and targets, evaluate them as a whole. For source qualifiers and targets, a high value is considered 80-100 percent. Low is considered 0-20 percent.

Data Lineage.. what is that ?

It is one of those buzzwords, that keep doing the circuit every once in a while. Almost every enterprise wants to do the analysis regarding this, and is almost always hard to find people with knowledge/experience doing this kind of analysis.

For the unaware, Data Lineage is basically (really in very short words) a study of the data from its source to its eventual target, similar to what we'd do for our generation tree, we analyze the generation analysis of the data we are dealing with.

Starting from the source of the data, it travels through different subsystems, sometimes going through transformations, and thus possibly changing shape too...

Informatica had a very interesting blog post around this (already in 2007), which can turn out to be fairly informative.

http://blogs.informatica.com/perspectives/index.php/2007/05/21/data-lineage-where-did-that-data-come-from/

Informatica & hadoop... solutions for future ?

Distributed computing using hadoop has taken the IT industry by a whirlwind in the last few years.  After getting almost "adopted" by yahoo, hadoop has progressed quite fast, and is now maturing slowly but steadily.

More and more enterprise solution providers are annoucing their support for the hadoop platform, hoping to get a pie of the big Data business chunk.  Its possibly a fair thing to expect that the leader in Data Integration business solutions space, Informatica has also announced a tie up with Cloudera, for porting Informatica platform to hadoop.

Though the exact details are yet to come out, the possibilities are endless.  With hadoop (and its inherent distributed computing based on map/reduce technology), informatica can actually think of processing big data in sustainable time frames.

For one my customers, I deal with about 200 million rows of data per day in one job.  Besides the issues with oracle in tuning the query etc, the informatica component itself consumes times in terms of hours.  With map reduce in place, I hope to get that in minutes, oracle issues notwithstanding.

Although word about hadoop is spreading quite fast, its adoption (from buzzword to actual usage in enterprise) is not as fast.  To aid their cause, Informatica and cloudera have started an interesting series of webinars, termed as "hadoop tuesdays".  Its free to join, and they get experts to talk about various related issues around hadoop and big data and informatica.  Its been very useful and informative so far.

convert Informatica Session Logs to text/xml format for out of tool readability

There was a situation recently when the infa repository was not able to point us to the old session logs.
However, the file system still had those files.

But the session log files are in binary format by default. If you didnt ask for backward compatible session log files, you'd get a binary format of session log file on file system. This is done to allow better importability of the session log files for infa support guys.

So, I had a few log files in binary format, and needed to analyze them.
Informatica provides a subcommand for infacmd to achieve that conversion.

The convertLogFile subcommand takes three parameters. Syntax is as follows -

convertLogFile <-InputFile|-in> input_file_name

                 [<-Format|-fm> format_TEXT_XML]

                 [<-OutputFile|-lo> output_file_name]

so, when you launch this, you can specify the input file to be converted, the format as TEXT or XML and the output file that you'd want as a result of conversion.

An example call would look like this -  (expecting server on unix)

infacmd.sh convertLogFile -in /path/to/binary/format/sesslog/file -fm TEXT -lo /my/home/text/format/sesslog/file

Rejecting records in Fact table loads - Informatica

In some development environments, you dont have all the required dimension data and as a result, your fact loading mapping's test runs go for a toss. The mapping wont be able to load anything (reject everything) since some or other foreign key would be missing for each row.

In other words, only those records would be loaded for which ALL the foreign key constraints would be satisfied. However, in Production environment, this would almost never happen. Or even if its the case, we'd actually want those rows to be rejected.

This can work out to be a serious impediment to development/unit testing. It prevents the developer from seeing whether or not his his mapping is behaving appropriately for the happy flow functionality.

One way of achieving this can be to work using a Mapping Variable indicating the Environment. The developer can run using a value like 'D' or something, indicating a different environment than Production, with 'P' (production) being the default value.

Now in the mapping, just about where you decide to reject a record based on different type of conditions, you could put an AND condition involving this mapping variable, e.g. ....AND $$MAPP_ENV = 'P'

Now, the expression would return true only in the Production environment, and therefore would work as expected. In Dev though, this expression would return false and would not reject that row.  

Now, to be able to satisfy the db constraints for the fact table so that the row is actually inserted, you'd need to use some placeholder convention. One of the approaches can be to use an outlier value as the foreign key value.  For Example, for customer id, keep a -1 in dimension table, meaning "Undefined".  And, in all such dev cases, send -1 to the fact. 

It would serve both the purposes, tell your fact table that there is something diff about that row, and still inserting a row in there, so that the testing for the rest of the columns is not stopped because of one foreign key missing out.

Implementing Cartesian Product in Informatica Mapping

As against pentaho, Informatica doesnt provide a ready made transformation for implementing cartesian product in a mapping. Although, most of us would agree that  its not often that we tend to go for cartesian product joins. [the instinct generally is to do enough to avoid a cartesian product, because its a performance killer in general]

However, when your requirements need this, there is no direct way to do it in informatica joiner transformation.  Either you do it in the db side, by overriding your source qualifier sql statement and building it in there.


However, I have seen that some designers dont like to override sql statements, in such cases you'd have to implement it inside the mapping only. Here's a workaround for achieving that. Here goes -

1. Read both the sources using their own source qualifiers, normally.
2. For both of them, put in an Expression Transformation after the source
3. In both the expression transformations, create an output port with a constant value. For Example, call it dummy1 for stream 1 and assign it a value -1.  Similarly, a port would be created in the second pipeline, lets call it dummy2 and assign it a value -1.
4. Now create a joiner transformation. Link ports [including the one that we created with a constant value] to the joiner from both the expressions.
5. In the join condition, choose to compare the dummy columns. 
6. The rest of the joiner configuration would have to be like any other joiner. Nothing specific.

You might want to keep the smaller source as the master in the joiner, since it would save on the caching.

Before implementing the above solution, be sure to go back and check if its actually required for your application to have cartesian product !!!!

availability of native drivers !!!

As I mentioned earlier, I was busy experimenting with a columnar database, InfoBright.

The experience till now is mixed, in some scenarios, the data loading is very good, as much as 40k rows per second, whereas through other channels its a poorer 500 rows per second. When I go with their built in loader, its lightening fast, but when I try from Pentaho or Informatica, its measly.

Apparantly, the drivers and the compatibility of the third party tools do play a role in attaining the performance.

The fact that they dont have any native driver published, is a huge bottleneck. Informatica dont even have a native connector for MySql, the more known cousin of InfoBright (being open source, the core engine of InfoBright is based on MySql only).

One thing came out of this experience for sure, the liking for open source got better and better. During the hunt to see the reason for slow performance from Pentaho, I even tried and managed to get the source code of the plugin (transformation) that pentaho uses for Infobright. Its pure java and it was a very powerful and awakening feeling to see the code of the item. I felt as if I have the power, the choice to make a difference, make it better. :)


I am currently exploring other options, one of them includes creating a dump file (read : csv) of the data, and then launching the command line tool to load the data into target db. I dont like it, but lets see if there is any better (read:faster) way around...

Interesting Demos from Informatica

Pretty Basic Demo by Infa guys on their DW offering...
Informatica Enterprise Data Warehousing Demo

Another interesting one on Data Migration...
Informatica Data Migration

Lookup Transformations in Informatica

Lookup is a transformation to look up the values from a relational table/view or a flat file. There are two types of Lookups in Powercenter, namely;

1. Connected Lookup 
2. Unconnected Lookup

Caching is an important facet of lookup transformation planning. You need to know what kind of data you are dealing with, how frequently do you call the lookup, how frequently does the data change, what is the size of your lookup table etc. among other things. Once you use cache, the trip to database can be avoided, thereby enhancing performance.

Different types of caches can be used with lookup like static, dynamic, persistent, and shared(The dynamic cache cannot be used while creating an un-connected lookup). Each of these has its own identification. For more details, refer to Informatica Transformation Guide.

Lookup is a passive transformation, and can be used either connected or unconnected.  Typically, connected lookup is used when you want to do the lookup for all rows. When you have selective lookup requirements, its normally better to go for unconnected lookup. Unconnected Lookup can be used as if its a function call.

To read more, here is a good article -
What is lookup transformation in informatica? - IT Community

New Informatica support forum

Hey... Informatica have launched a brand new set of discussion forums on their website. This url should normally point to the overview .

I have a good feeling about this, the company hosting its own discussion forums. Before this there were some yahoo groups that acted as discussion forums for the company, and some developer network forums.

See how it goes...

Aggregator : Sorted Input

You would read everywhere that an aggregator responds faster if you provide it sorted data. Yeah, thats correct in theory.

But, be careful while doing that. If you decide to use Sorter transformation to sort data, the end result might be slower. However, another option is to use sorting at the source qualifier level..

In one of my experiments on Powercenter 7.1.3, I had the following stats :

Source Data : 277000 rows
time for an aggregator :

No sorted input : 22 seconds
With sorter in mapping : 50 seconds
With sorting in Src Qualifer : 16 seconds

My view is, if possible, do the sorting at db end.