- It is a web based component from 8.0 versions on wards
- It is used to handle all admin activities
- Administrator is going to work only on this component
- Admin Activities:
- Creating Repository Services
- Creating Integration services
- Creating Users
- Creating Groups
- Providing Permissions
Creating Repository Services:-
1. Repository service is a storage location of informatica source ,target, mapping,task ,workflow …etc information.
2. This information all together will store in database schema with the help
of 511 tables.
Step 1: Starting Informatica Server:
Start -> Control panel -> Administrative tools -> Services -> select Informatica 9.0.1
->Right Click -> Click on start
Step 2: open Informatica Administration Home Page:
Start -> All programs -> Informatica -> Server -> Informatica Administration Home page
Provide username : Administrator
Password : Administrator
Step 3: Creating Repository Schema in Database:
Open SQL PLUS -> Type / as sysdba press Enter
Create a user by executing below commands
a. Create user ITECINFA identified by RRitec123;
b. Grant DBA to ITECINFA;
c. Conn ITECINFA@ORCL
d. Password RRitec123
e. Select count (*) from tab;
3. Please Note that no tables are available in this schema.
Step 4 : Configuring Repository Services in Admin Console
1.Open Informatica Administration Home page -> Provide username and password
as Administrator -> Click on log in
2. Under domain navigator select domain Domain_rritec
3. Go to Action ->new ->Click on Power Center Repository Service
4. Provide below information
a. Name : RRITEC_RS
b. select license and Node from Drop down -> Click Next
c. Password : Administrator
d. Data Movement Mode : ASCII
3. Click on Finish.
4. Repository Manager
1. Repository manager is Window based component
2. We can do below activities.
a. Add Repositories to client
b. Create folders
c. Deploy from one server to another server …etc.,
Step 4 : Configuring Repository Services in Admin Console
1.Open Informatica Administration Home page -> Provide username and password
as Administrator -> Click on log in
2. Under domain navigator select domain Domain_rritec
3. Go to Action ->new ->Click on Power Center Repository Service
4. Provide below information
a. Name : RRITEC_RS
b. select license and Node from Drop down -> Click Next
5. Provide below database information create in Step 3
a. Database type: Oracle
b. Username: ITECINFA
c. Password: RRitec123
d. Connection String: ORCL
6. Select 2nd radio button (i.e. No content….) -> click on Finish
7. Once Process is completed SQL PLUS execute “Select Count (*) form tab” and
notice that 511 tables are created.
Step 5 : Changing Operating Mode from exclusive to normal:
8. Select RRITEC_REP repository -> click on repository properties Edit
-> Change operating mode from exclusive to normal ->Click on OK
->Restart repository service by click on OK.
-> Change operating mode from exclusive to normal ->Click on OK
->Restart repository service by click on OK.
Creating Integration Service:-
Integration services are useful to Read Metadata from Repository Services &
Execute corresponding Workflows.
Process:-
1. Select Domain_rritec -> Go to Action menu -> Click on new
->Click on Power Center Integration services.
->Click on Power Center Integration services.
a. Name : RRITEC_INT
b. Select License and Node from drop-down -> Click Next
2. Provide Repository information as for below
a. Select Repository Service : RRITEC_REP
b. Username : Administratorc. Password : Administrator
d. Data Movement Mode : ASCII
3. Click on Finish.
4. Repository Manager
1. Repository manager is Window based component
2. We can do below activities.
a. Add Repositories to client
b. Create folders
c. Deploy from one server to another server …etc.,
Adding Repository:
1. Start -> All programs -> Informatica -> Client -> Power Center Client
-> Power Center Repository Manager -> Click on OK.
2. Go to Repository menu -> click on Add Repository -> provide
a. Repository name: RRITEC_REP
b. Username : Administrator & Click on OK.
3. Right click on RRITEC_REP repository -> Click on Connect -> provide
a. Username: Administrator
b. Domain: Domain_rritec
c. Password: Administrator -> Click on connect
Create Folder:-
1. Select RRITEC_REP Repository -> Go to folder menu
->Click on Create -> name it as RRITEC -> Click on OK
-> Again OK.
->Click on Create -> name it as RRITEC -> Click on OK
-> Again OK.
5.Configuring RRITEC Database:
5.1 Configuring Source Database
Step 1: Creating user SDBU and load tables into SDBU Schema
1. Open SQL PLUS ->Type / as sysdba press Enter
2. Create a user by executing below commands
a. Create user SDBU identified by RRitec123;
b. Grant DBA to ITECINFA;
c. Conn SDBU@ORCL
d. Password RRitec123
e. Select count (*) from tab;
3. Go to RRITEC lab copy -> lab data folder and take full path of driver
and execute as for below.
and execute as for below.
4.Commit;
5.2 Configuring Target Database
1. Open SQL PLUS à Type / as sysdba press Enter
2. Create a user by executing below commands
a. Create user TDBU identified by RRitec123;
b. Grant DBA to ITECINFA;
c. Conn TDBU@ORCL
d. Password RRitec123
e. Select count (*) from tab;
3. Go to RRITEC lab copy lab data folder and take full path of driver
and execute as for below
and execute as for below
6.HANDS ON -1:
6.1 SOURCE_TARGET _IMPORT:
The exercises in this lab are designed to walk the student through the
process of creating source & target definitions by importing metadata
from a relational database table.
from a relational database table.
Exercise 1: Import Source Schema This exercise walks the student through the process of creating
a source definition from the EMPLOYEE table.
a source definition from the EMPLOYEE table.
Step 1. Start the Power Center Designer:
1. Select Start -> Programs -> Informatica 9.0.1 -> Client -> Power Center
Client -> Power Center Designer.
2. Click on OK
Step 2. Connect to the Repository.
1. Select the RRITEC_REP repository.
2. Right Click on Repository RRITEC_REP | Connect.
3. The Connect to Repository dialog box appears
4. Enter the username and password As Administrator
5. Click the Connect button.
5. Click the Connect button.
Step 3. Open the folder:
1. Right-click on the RRITEC folder and Select Open.
2. When a folder is opened for the first time, the Source Analyzer tool will be opened in the work space.
Step 4. Open the Source Analyzer:
1. The Workbook View allows easy navigation among open folders.
If the Source Analyzer is not already open, select Tools | Source Analyzer, or
click on the Source Analyzer button in the toolbar.
2. The Source Analyzer window opens by default maximized and with workbook
tabs. If your setting are different then do manual or ignore below two steps
a. Maximize the Source Analyzer window by clicking on
the maximize window button.
b. Create workbook tabs at the bottom of the work space
by selecting View Workbook.
Step 5. Import the source definition:
A Power Center source definition defines the structure, or schema,
of source data to be read by the Power Center Server.
For a relational source, this includes information about each of the columns
– including key designation, data type, precision and scale.
1. Select Sources | Import from Database.
2. The Import Tables dialog box appears.
3. Click on ODBC Data Source
> Click on System DSN tab
-> Add ->Oracle in
4. Finish -> Provide below information
Data Source Name : RRITEC_SOURCE
TNS Service Name: ORCL
User ID :sdbu
5. Click on OK->OK
6. From Drop down of ODBC Data Source select RRITEC_SOURCE
7. Note that the Owner name field defaults to the username that was entered.
Enter the database username and password
username: sdbu
owner: SDBU
password: Rritec123
8. Click the Connect button.
9. Once the Connect button changes to read Re-connect,
a direct connection to the source database has been established.
10. In the Select Tables window, expand the database owner name (SDBU).
11. Expand the TABLES node.
12. Select the EMPLOYEE table:
13. Click the OK button.
14. The new source table definition now appears in the Source Analyzer work space:
15. Note that the EMPLOYEE source definition is also added to the Sources
node in the Navigator window:
6. From Drop down of ODBC Data Source select RRITEC_SOURCE
Enter the database username and password
username: sdbu
owner: SDBU
password: Rritec123
8. Click the Connect button.
9. Once the Connect button changes to read Re-connect,
a direct connection to the source database has been established.
10. In the Select Tables window, expand the database owner name (SDBU).
11. Expand the TABLES node.
12. Select the EMPLOYEE table:
13. Click the OK button.
14. The new source table definition now appears in the Source Analyzer work space:
node in the Navigator window:
Step 6. Edit the source definition:
1. In the Source Analyzer work space, double-click
on the header of the EMPLOYEE source definition.
2. The Edit Tables dialog box appears.
3. In the Table tab, Description field, enter the following text:
This source comes from the CRM system and includes all Sales
Representatives from the Sales Department.
Note that the Description field can be used to add a description for
each column
To associate a description with a particular column, highlight the column
and enter a description in the Description field.
4. Select the Columns tab.
5. Select TYPE_CODE.
6. In the Description field (at the bottom of the dialog box),
enter the following text: Human Resource Job Code ->Click Apply.
Step 7. Save all work.
1. Add the Repository toolbar to the Power Center client by selecting
Tools | Customize.
2. The Customize dialog box appears.
3. Check the Repository check box on the Tool bars tab
4. Click OK.
5. The new toolbar appears at the top right of the Designer window
While working in the Power Center Designer, save often to avoid losing
any changes! Save all work by selecting Repository Save
or by typing Ctrl-S.
6. In the output window’s Save tab, scroll up for a message confirming
your work is saved.
Step 8. Clear the work space:
1. Right-click anywhere in the work space and select Clear All.
2. To view a source definition in the work space, select the
source definition in the Navigator window, then drag and
drop it into the Work space window.
6.2 Exercise 2: Import a Target Schema
In this exercise, the student will import a target definition for therelational database table, ODS_EMPLOYEE.
Step 1. Open the Warehouse Designer:
Designer button in the toolbar
Step 2. Import the structure of the ODS_EMPLOYEE table:
1. Select Targets | Import from Database…
2. The Import Tables dialog box appears.
-> Add->Oracle in
4. Finish -> Provide below information
Data Source Name :RRITEC_TARGET
TNS Service Name: ORCL
User ID :tdbu
5. Click on OK->OK
6. From Drop down of ODBC Data Source select RRITEC_TARGET
7. Enter the database username and password:
username: tdbu
owner: TDBU
password: Rritec123
8. Click on Connect.
9. Expand the database owner name.
10. Expand the TABLES node.
11. Select the ODS_EMPLOYEE table:
12.Click the OK button.
13. The new target table definition now appears in the
Warehouse Designer work space:
14. Note that the EMPLOYEE target definition is also added to the
Targets folder in the Navigator window:
Step 3. Save the target definition:
1. Save all work by selecting Repository | Save or by typing Ctrl-S.
7. Hanson 02_Creating_Mapping
Lab at a Glance
The exercises in this lab are designed to familiarize the student with the
process of creating a mapping. The student will also learn to navigate
within the Mapping Designer tool.
Objectives
After completing the lab, the student will be able to:
- Open the Mapping Designer tool.
- Create a new mapping.
- Create an expression transformation.
- Link sources, transformations, and targets within a mapping.
- Modify and add ports to an expression transformation.
- Validate a mapping.
The completed mapping should look like this:
Exercises:
7.1 Exercise 1: Create a Mapping
In this exercise, the student will create a mapping that represents the
data flow between the EMPLOYEE source and the ODS_EMPLOYEE
target.
A mapping represents the data flow between sources and targets.
The instructions defined in the mapping will tell the Informatica Server
how to read, transform and write the data.
Step 1. Open the Mapping Designer tool:
1. In the Navigator window, select the RRITEC folder
2. Select Tools | Mapping Designer, or click the Mapping
Designer button in the toolbar.
Step 2. Create a new mapping:
1. Select Mappings | Create
2. The Mapping Name dialog box appears
3. Enter M_ODS_EMPLOYEE for the New Mapping name
4. Click the OK button.
Step 3. Add source and target definitions:
1. In the Navigator window, expand the Sources and RRITEC_SOURCE
nodes and locate the EMPLOYEE source definition
2. Drag-and-drop the EMPLOYEE source definition to the far left side
of the work space.
3. In the Navigator window, expand the Targets node and locate the
ODS_EMPLOYEE target definition
4. Drag-and-drop the ODS_EMPLOYEE target definition to the right side of
the work space.
5. The work space should look as follows
Note that an object called SQ_EMPLOYEE is automatically added to the
mapping above. This transformation object is called the Source Qualifier
and is required with all relational and flat file sources. It was created automatically
when an instance of the EMPLOYEE source definition was added to the mapping.
Step 4. Rename and Examine the Source Qualifier Transformation:
1. Edit SQ_EMPLOYEE by double-clicking on the header.
2. The Edit Transformations dialog box appears.
3. Click the Rename button.
4. Enter SQ_EMPLOYEE in the Transformation Name field.
5. Click OK.
6. Click Apply.
7. Click on the Ports tab to view the port names, datatypes,
precision's and scales.
8. Click the OK button to close the dialog box.
Step 5. Create an expression transformation:
1. If the toolbar is not visible, activate it by selecting Tools | Customize,
then select the Transformations check box
2. Before the target is loaded, the FIRST_NAME and LAST_NAME ports
for each row must be concatenated. This concatenation can be done in
an Expression transformation.
3. Locate the Transformation toolbar
4. The default location is at the top of the Designer.
5. Click on the vertical bar at the beginning of the toolbar and drag the
toolbar to the center of the Designer work space.
6. In order to give yourself more room to work, you may wish to toggle off the
navigator window. You can do this by selecting View – Navigator from
the menu or by clicking the “x” in the upper-right corner of the navigator.
7. Click on the Expression Transformation button in the toolbar.
8. Move the pointer in the work space to the right of the sq_EMPLOYEE
source qualifier. In the work space, the cursor appears as cross hairs
Left-click the mouse.An Expression transformation is created.
Step 6. Link the Source Qualifier to the Expression transformation:
1. In the SQ_EMPLOYEE source qualifier, hold down the Ctrl key and select the
following ports:
- EMPLOYEE_ID
- FIRST_NAME
- LAST_NAME
- DATE_HIRED
- TYPE_CODE
2. Drag the ports to an empty line of the expression transformation object and
release the mouse button.
Step 7. Rename the Expression transformation:
1. Double-click on the header of the Expression transformation.
2. Click the Rename button.
3. Rename the transformation to exp_ODS_EMPLOYEE.
Step 8. Modify/add ports to the Expression transformation:
1. Select the Ports tab.
2. Disable the output ports for FIRST_NAME and LAST_NAME
by removing the check mark in the O (output) column. This will define the
port as input only.
3. Click on the LAST_NAME column.
4. Click on the Add button to add a new port.
5. Name the new port NAME_out.
6. Verify the data type is string and increase the precision to 51.
7. Disable the input port for NAME_out by removing the check mark in the
I (input) column. This will define the port as output only
Step 9. Create an expression formula:
1) Note that a formula can also be typed in manually, if the names of
the functions and ports are known. Click on the Expression column of the
NAME_out port and notice the arrow that appears at the far right
2) Click on the arrow.
3) The Expression Editor dialog box appears.
4) Delete the text, NAME_out, in the Formula field.
5) Select the Ports tab on the left-hand side of the Expression Editor dialog box.
6) Double-click on the port FIRST_NAME. Note that it is added to
the Formula field.
7) Click on the double-pipe button in the Operator keypad.
The concatenation operator is added to the formula.
8) Click on the single-quote button.
9) Press the space bar once and click the single-quote button again.
This will cause a space to be placed after the FIRST_NAME in the formula.
10) Click on the double-pipe button again.
11) Double-click on LAST_NAME. The concatenation formula is complete,
and should look like this: FIRST_NAME||''||LAST_NAME
12) Click Validate.
13) Upon successful parsing of the formula, the Expression parsed
successfully message will appear.
14) Click OK.
15) Click OK to close the Edit Transformation dialog box.
16) Save the repository
Step 10. Link the target definition:
1. Click on the following ports in exp_ODS_EMPLOYEE and drag them
onto ODS_EMPLOYEE to link the ports:
- EMPLOYEE_ID -> EMPLOYEE_ID
- NAME_out -> NAME
- DATE_HIRED -> DATE_HIRED
- TYPE_CODE -->TYPE_CODE
2. The mapping is now complete. Right-click in the work space and
select Arrange All – the mapping should look like the following:
Step 11. Validate the mapping:
- Save (CTRL+ S) the repository.
- Every time a repository is saved, mapping validation checks are performed.
- Click on the Save tab in the Output window.
- Expand the Output window and scroll up until the validation test details are visible:
5. Verify that there are no Errors.
No comments:
Post a Comment