Monday, 14 July 2014

Informatica ETL tool capabilities

If you are designing a solution and need to know when you can use Informatica or for that matter any ETL tool, the below list can be useful:

a) Data Integration: Informatica is a data integration tool .i.e. it can pull data from multiple heterogenous sources and extract, tranform, and load the data to again another set of data targets.
The source/target database can be mysql, oracle, sql server, Netezza, Db2, Flat files, etc.

The source can also be web service that provides data in XML format. Informatica can also generate files in XML format.

The source can be the main frame VSAM files generated by main frames.

There can be data also read from cloud such as Amazon red shift. Big data integration are also available.

b) Informatica is pretty good in reading from flat files and loading data into database or converting it to flat files or xml feeds.

c) Informatica is a tool that can be used to apply lot of transformation logic. This tranformation logic could be something simple like changing a data type or doing some arithmetic using data, aggregating, sorting, ranking, normalizing, etc or complex such as xml parsing.

d) Informatica workflows can also be called as web service i.e. you can create web services from Informatica.

e) Informatica can be used to execute commands on the host machine for any purpose such a sftpign a file, moving a file, etc. It can wait on events such a touch files or completion of jobs.

f) Informatica comes with a basic scheduler that can be used to schedule the jobs. Also, provides various notification options for failures, success, job completion etc using emails etc.

g) Informatica can be used to sequence jobs, apply conditions between each job i.e. it can be used to create workflows where each job gets executed one after another in a defined sequence.

h) Informatica can be used to create operation data warehouses, datamarts, star schemas, slowly changing dimensions, type1/type2/type3 dimensions etc/.

Other tools from Informatica such as IDQ and MDM can be used for address validation and master data management respectively. Informatica cloud allows pushing jobs to the cloud.

Informatica might not do when when do..while or for ..loop kind of logic is required to handle data. However using java transformations or other workaround this kind of logic can be implemented in Informatica.

See also: http://dwbitechguru.blogspot.ca/2014/07/performance-tuning-in-informatica.html