What is Informatica Power Center?
Intformatica Power Center is an ETL(Extraction, Transformation,Loading) tool. This tool is used in Data warehousing projects.
When Informatica comes into picture?
If you are working on Data Warehousing projects, Informatica comes into picture.
If you are under Business Intelligence team, Informatica comes into picture.
If you take any Data Warehousing Project, there are two parts ETL and Reporting. Informatica Comes under ETL part
Why you need to learn Informatica?
Informatica power center is a powerful ETL tool, many companies using this and many jobs are available in the market. You will get attractive compensations.
What is pre-requisite to learn Informatica?
You should have little knowledge on SQL.
What are different types of jobs available on informatica?
Informatica Developer
Informatica Lead/ Informatica Architect
Informatica Admin
What is DWH/Data Warehouse?
a) According to the author Ralph Kimbal, a data warehouse is a historical database which is used for analysis and decision making
b) If i speak technically a data warehouse is a collection of tables with some relationships. You can build data warehouse on any one of the databases which are available in the marekt. Eg:- Oracle, Teradata etc
What is ETL?
ETL means
Extraction ==>Fetching data from multiple systems
Transformation ==> Applying Business Rules on the extracted data
Loading==>Inserting data into the Data Warehouse
To know more information on above topics, please see the below video
What is Informatica Power Center Tool?
Informatica Power Center is an ETL Tool. This is a GUI tool. This comes as two parts Client and Server.
Power Center Client is used by Informatica Developers
Server Side (Admin Console) used by Informatica Admins
What are different components with in Power Center Tool?
Power Center Designer
Power Center Workflow Manager
Power Center Workflow Monitor
Power Center Repository Manager
What we do with Power Center Designer?
It is a GUI tool, used mainly to import source and target structures, creating reusable transformations, creating mapplets and mappings.
What are the different tools under Power Center Designer?
a) Source Analyzer==>To import source structures
b) Target Designer ==>To import target structures
c) Tranformation Developer ==>To create reusable transformations
d) Mapplet Designer ==> To create mapplets
e) Mapping Desinger ==> To create mappins
What we do with Power Center Workflow Manager?
Used mainly to create sessions, worklets and workflows
What are the different tools under Power Center Workflow Manager?
a) Task Developer ==>To create sessions
b) Worklet Designer ==> To create worklets
c) Worfkflow Designer ==>To create workflows
What we do with Power Center Workflow Monitor?
Used to monitor data loads (Sessions succeeded/failed etc)
What we do with Power Center Repository Manager?
Used to import/export objects
Used to compare folders/objects
Used to create folders etc
As a informatica developer you closely work with designer, workflow manager and workflow monitor. Few times Repository Manager.
As a Informatica Administrator, closely work with Repository Manager and Administration Console.
PASS THROUGH MAPPING FIRST PRACTICAL
Connecting to Oracle Database:
1. Start=> All programs=>Oracle=>Application
development=> Sqlplus
It asks following details.
User name : System
Password
: ******
Host String : ORCL
Click on ok
Create two users one user is source another
one is target
Query:
Sql>Create user source identified by source;
Sql>Grant connect, resource to source;
Query:
Sql>Create user target identified by
target;
Sql>Grant connect, resource to target;
Create source table and target table by
connecting to users source and target.
Sql> Conn
Sql> Create table employee(empno
number,ename varchar2(20),esal number);
Sql> Insert into employee
values(100,’ravi’,2000);
Sql> Insert into employee
values(101,’vinay’,3000);
Sql> Insert into employee
values(102,’raju’,6000);
Sql>Commit;
Sql> Conn
Sql> Create table tb_emp(empno
number,ename varchar2(20),esal number);
Open
Informatica PowerCenter Tool:
start=>All Programs=>Informatica=>Client=>PowerCenter
Designer
Double click on "rep" in the left
side navigation bar. It will ask userid and pwd
User:******
Pwd:******
Double click on your folder in the left
side navigation bar.
Importing
Source Definition
Select source analyzer tool to import the
source structure from database.
From the menu bar select sources=>import
from database.
It opens a dialouge box. Select the icon
with 3 dots.
Click on ADD
=>select the oracle driver oracle in "oradb1og-home1"
click on finish
Enter the following details:
Data source name: source_test
TNS Service name : Orcl
User ID:source
Click the test connection
Enter the pwd: source
Click On “ok”
select the dsn source_test.
enter userid and pwd. click on connect.
select table and click on ok.
From the menu bar select repository and
click on save.
Importing
Target Definition
Select target designer tool to import the target
structure from database.
From the menu bar select targets=>import
from database.
It opens a dialogue box. Select the icon
with 3 dots.
Click on ADD
=>select the oracle driver oracle in
"oradb1og-home1" click on finish
Enter the following details:
Data source name: target_test
TNS Service name : Orcl
User ID:target
Click the test connection
Enter the pwd: target
Click On “ok”
select the dsn target_test.
enter userid and pwd. click on connect.
select table and click on ok.
From the menu bar select repository and
click on save.
Design
a mapping
Select mapping designer tool to create a
mapping.
1.
From mapping menu select create
2.
Enter mapping “name” click ok
3.
From repository navigator window
drag the source definition on to the mapping designer workspace
4.
From target drag the target
definition on to the mapping designer workspace
5.
From source qualifier connect
columns to target definition with simple drag and drop
6.
From repository “save”
Creation of session
1.
Open client workflow manager
2.
Connect repository with valid
user name and password
3.
Select the folder from tools
menu
4.
Select task developer
5.
Select tasks from menu bar.
6.
Select task type session
7.
Enter name
8.
Click on “create”
9.
Select mapping click on “ok”
10.
Click on “done”
11.
Click on “save”
Creation of
reader connection:- (Source relational connection)
1.
From workflow manager client
2.
Select connection menu click on
relational
3.
From list select oracle click
on new enter the following database connectivity
4.
Name : test_source
5.
User name:source
6.
Password :source
7.
Connect string: orcl
8.
Click on “ok”
Creation of
writer connection:- (Target relational connection)
1. Click on new
2. Name:test_target
3. User name:target
4. Password :target
5. connect
string : orcl
Click on apply
ok
Double click the
session
1.
Select the mapping tab
2.
From left panel select “source”
3.
From a relational reader set
connection the value “relational source name”
4.
From left panel select “target”
5.
From a relational writer set
connection the value “relational target name”
6.
From repository click on “save”
Creation of the workflow:-
1.
From tool menu select workflow
designer
2.
Workflow menu create select
enter the workflow name:
3.
Click “ok”
4.
From repository navigator
window expended session for drag session drop beside the workflow
5.
Task menu select link task drag
link from workflow, drop on session
6.
Execution workflow
7.
Workflow menu click on
8.
Start workflow
Workflow monitor:-
Monitor the
session succeeded or failed.
