Informatica is an ETL (Extract Transform Load) tool that is used to that is used to extract source data, apply required transformation and load it into the target. It can have multiple source and target. Source can be database or files. Target is usually like a datawarehouse where we can store bulk amount of data. Mapping is used to connect the source and target.
The difference between database and data warehouse is that database can have only small amount of data and contains the latest data. Data warehouse maintains historical data, therefore they have bulk amount of data storage capacity. We can directly access database using queries like insert, delete etc. But we cannot directly access data warehouse using simple queries.
Informatica ETL is also known as Informatica PowerCenter. It mainly consist of 3 main components – Informatica PowerCenter Server, Informatica PowerCenter Client Tools and Informatica PowerCenter Repository.
Informatica PowerCenter Server is a Server where all the execution takes place and only admin can access it. Server performs all the physical connection between the source and target, applies the transformations etc.
Information PowerCenter Client Tool is a development tool where a developer can develop source, target and Mapping. This the developer end. It can be divided into four windows. Designer window is used to define the source, target and transformation process known as mapping. Workflow Manager window is used to define the run-time properties for a mapping known as session. A mapping is triggered from this window. Workflow Monitor window monitors the execution of the sessions. Repository Manager is useful for administrators, to manage repository. In repository you can create your own folder also. Metadata reporter window is used to report metadata.
Informatica PowerCenter Repository is where all the data is present. The repository accesses the database indirectly using metadata. It is the heart of the informatica tools. All the client tools and informatica server fetches data from the repository. The repository is the backend of informatica without which no informatica process can take place as all the data is present here.
There are mainly four types of transformations in informatica. Active transformation where one can change the number of rows being output, change the row type, row order etc. That is where transaction control happens in the transformation. Passive transformation where on cannot change the number of rows being output. Unconnected transformation in which there is no link with any transformation or object in the mapping. Connected transformation is the one in which there is at least one link with other transformation or object in the mapping.
Therefore the basic terms that you should remember in informatica are
- Source, Target and Mapping which are created in Design window
- Session to trigger a mapping which is created in workflow window
- Repository which contains the metadata.