INFORMATICA

Informatica is a tool which is used to extract the data from the source and transform the current extracted data and load it to target database.
Now the question is WHY ETL?????
The simple answer is lot of client or customer are using legacy system and they are moving to new technology. So in order to move to new technology, their current setup regarding to databases and tables become null and void because new system bring their own database.
So ETL is used to move the customer current data to new database which is compatible to customer/client new technology.

Informatica provide movement and transformation of current data from tables which can be from one source database or multiple source database to new DATA WAREHOUSE. i.e. suppose currently customer legacy system consist of databases from mysql/Oracle,TIBCO and customer is using AWS which has its own database configuration, so in this ETL tools come into picture and INFORMATICA is the widely used tools among other ETL tools.



Mapping excel document is most important table in ETL

Mapping excel document  contains Source tables, staging tables, target tables, business logic, and the table structure.

While converting or loading the data from source tables and target tables, we always have some transformation rule.

This is basic concept of ETL, Extracting the data from source tables, applying the transformation rules to data of source tables and loading the data into the target tables.

So while doing ETL, we always have to think of the source tables sql query that has to be written so that we get concise, precise and correct data

Take a look at the sample mapping document......

Important Term:
Straight pull/one to one mapping------no change or variance in data if data is copied from source to target table i.e no transformation needed.Straight forward Just Copy the data from source to target.

Now one thing is very necessary for us to know is that Transformation logic is written by developer in ETL tool.
That we will show while we go ahead in this tutorial.

Developer will create the Process or job that will be called workflow that contain the logic for the transformation.
This job or process or workflow will have basically the mapping between the source and target tables.
This workflow will extract the data from the source tables, apply the transformation on these data and lastly it will load all the data in the target tables.

Workflow

1. Extract the table for the data
2. Apply the transformation on the tables or individual data
3. Load the data in the target tables

Requirement for creating workflow is the mapping document where all the rules are written.

Developer will refer the mapping document for writing the workflow and 

Tester require the mapping document to test whether all the data are loaded correctly or not.

Tester refer the mapping document and create sql queries to test the load of data.

Informatica has four component

• Repository-It is the place we store everything. Informatica Repository is mainly accessed by the person who has the admin access.
• Designer-It is the place where developer design the workflow.
• WorkFlow Manager-It is the place to trigger the job.
• Workflow Monitor- Once the job is triggered, now to see the status, whether Passed/Failed or if job failed, what is the reason, all this information can be analysed from workflow monitor.