My first brushes with Informatica!!!

If you are a newbie to any tool, the first question that ever occurs in your mind (usually, not always) is “Why is it required???” The lessons learnt during college days seem to be more than sufficient for any problem on earth, at least that’s what we all did for our intern project. But what we don’t realize is not only we are new to the tool, we are also unaware of the real world issues and scenarios. The first question that came to my mind was why is Informatica required when we have all the robust database products in place. This sounds quite stupid, but what’s worth mentioning here is by that time I was not fully aware of what does ETL exactly mean (I did know its full form then).
For a newbie, I suppose what’s most important is asking the right question and then following it up till you get a definitive solution. Having said that, I’ll shoot following questions to the fresher:
1. What will you do if you are asked to load data from a text file to any database, say Oracle (I agree there are work around solutions, but I am not getting into them)
2. A table in a database from one vendor (say, DB2) needs to be loaded to the one from another vendor (say, Oracle) along with its data and without distorting the schema of course (Remember different databases support different data types with varying precisions)
3. Now let us take a scenario where you have to load data from a file into a table. The file is replenished with new data daily and you need to load this new data into the same table. Assuming you find a workaround solution for Question 1 above. Now will you carry out this “smart” procedure of yours for the scenario above daily?

Welcome to the technology of Informatica where problems like above and much more complicated than these can be tackled with utmost ease. Once you solve any of the issues mentioned above, you’ll be said to have done ETL i.e. Extract, Transform and Load. There is much more to be talked about the capabilities of the tool and that is what this blog is dedicated to.