Informatica Basic Dac Obia7964

RR ITEC
RR ITEC Informatica Basic + DAC + OBIA 7964 Classroom Notes
By Ram Reddy
29/Nov/2015
Confidential
Contents
1. INFORMATICA 9 4
1.1 INFORMATICA Introduction 4
1.2 INFORMATICA 9 Installation 4
1.2.1 Server Installation 4
1.2.2 Client Installation 9
1.3 Informatica Administration Console 13

1.4 Repository Manager 17
1.5 Configuring RRITEC Database 19
1.5.1 Configuring source database 19
1.5.2 Configuring Target database 19
1.6 Hands on 01_Source_Target_Import 20

1.6.1 Exercise 1: Import a Source Schema 20
1.6.2 Exercise 2: Import a Target Schema 28
1.7 Handson 02_Creating_mapping 31

1.7.1 Exercise 1: Create a Mapping 32
1.8 Hands on 03: Creating Workflow 39

1.8.1 Exercise 1: Create a Workflow 39
1.8.2 Exercise 2: Create source and target database connections 43
1.8.3 Exercise 3: Create a Task and Run the Workflow 44
1.8.4 Exercise 4: Monitor the Workflow 47
1.9 Hands on 04: Flat file Filter 49

1.9.1 Exercise 1: Import Source and Target 52
1.9.2 Exercise 2: Create the Mapping 55
1.9.3 Exercise 3: Create and Run the Workflow 59
2. DAC 11G 61
2.1 DAC Introduction 61
2.2 DAC Installation 61
2.3 DAC Configuration 72
2.4 Development Activities 78
2.4.1 Creating Tasks 78
RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 2
Confidential
2.4.2 Creating Task Groups 80
2.4.3 Creating Subject Area 80
2.4.4 Creating Execution plan 81
2.4.5 Scheduling 82
2.4.6 Configuring Email 82
2.4.7 Deployment 83
3. OBIA 7964 85
3.1 OBIA Introduction 85
3.2 Main components of OBIA 7.9.6.4 86
3.3 OBIA 7964 Installation 86
3.4 CONFIGURATION 87
3.4.1 STEP1: Configuring Informatica Repository 87
3.4.2 STEP2: Configuring DAC Repository 91
3.4.3 STEP3: Configuring OBAW (or) Creating OBAW 93
3.4.4 STEP4: Configuring RPD & Catalog 94
3.1 Understanding HR Analytics 95
Confidential
1. INFORMATICA 9
1.1 INFORMATICA Introduction
1. Informatica is an ETL tool (or) Integration tool
2. It is useful to Extract different Heterogeneous Sources (like Sibel, Sales force, Oracle
Apps, PeopleSoft, Mainframe .) Transform as Analytical data (Aggregating data,
cleaning data) and Load into DW.
3. In Informatica latest version is INFORMAICA 9.5

4. The earlier famous versions of informatica 9.1,8.6, 8.1, 8.0, 7.1, 6.0
5. In Informatica we have mainly following components.
1. Informatica Administration console Administrator Creating services ,Security
2. Informatica Repository Manager Developer ,Administrator Deployment
3. Informatica Designer Developer Mapping
4. Informatica workflow Manager Developer Workflows
5. Workflow Monitor Developer , Administrator monitor execution of Workflows
1.2 INFORMATICA 9 Installation
1.2.1 Server Installation
Step 1 : Double click on install.exe
1.
Step 2 : Installation wizard Starts. Choose the installation type Click Next.
Step : 3 Installation Pre-requisites will be shown before the installation starts as below.
Click Next.
Confidential
Step 4 : Enter the license key Click Next
Step 5 : Pre-installation summery will give the items installed during the installation
process based on the license key Click Next
Confidential
Step 6 : Installation Begins. It takes couple of minutes to finish. Soon after completion of this
step, Configuring Domain window opens. Continue the steps from Domain Configuration.
Step 6:
o Choose Create a Domain radio button.

o Check Enable HTTPS for Informatica Administrator
o Leave the Port number as it is and choose Use a keystore file generated by the
installer
Click Next.
Step 7: Provide the Repository database details as below
o Database Type : Choose your Repository database (Oracle/SQL Server/Sybase)

o Database user ID : Database user ID to connect database.
o User Password : Password.
o Schema Name : If Schema name is not provided default schema will be used.
Confidential
o Database Address and Port : Machine on which database in installed and default
port number.
o Database Service Name : Database Name.
Below image shows the configuration using SQL Server.

Click Next.
Step 8:
You can give the Domain details, Admin user details now.
o Domain Name : Name of your Domain.

o Node Host Name : Machine name on which Informatica Server is running.
o Node Name : Name of the Node.
o Node Port Number : Leave the default port Number.
o Domain user name : This is the Administrator user
o Domain password : Administrator password
Note : Remember your Admin User ID, Password to log on to Admin Console later in the
installation.
Confidential
Step 9
Use the default configuration and Click Next.
Step : 5
Installation is complete and you get the post-installation summery. You get a link to the
installation log file and a link to Admin console Click Done.
Confidential
1.2.2 Client Installation
Step 1 : Click on the install.bat On Installation wizard Starts Click Start.
Step 2 :Choose the installation type as in the below imageClick Next.
Confidential
Step 3: On Installation Pre-requisites window Click Next.
Step 5: Choose the client tools you need. Only PowerCenter Client is mandatory.
Click Next.
Confidential
Step 6 : Choose the client installation directory Click Next.
Step 7: You can choose the type of Eclipse installation in this step. This window will be
available if you choose to install Informatica Developer or Data Transformation Studio
Click Next.
Confidential
Step 8:Pre-installation summery will give the items installed during the installation process
Click Next.
Step 9: Installation Begins. It takes one or two minutes to complete this step.
Confidential
Step 10: Installation is complete and you get the post-installation summery.
1.3 Informatica Administration Console
It is a web based component from 8.0 versions onwards
It is used to handle all admin activities
Administrator is going to work only on this component
Admin Activities:
1. Creating Repository Services

2. Creating Integration services
3. Creating Users
4. Creating Groups
5. Providing Permissions
Confidential
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
Step1: Starting Informatica Server.
1. Start Control panel Administrative tools Services select Informatica 9.0.1

Right Click click on start
OR
2. Start Run services.msc select Informatica 9.0.1 Right Click click on start
Step2: open Informatica Administration Home page
1. Start all programs Informatica Server Informatica Administration Home

page
2. Provide username : Administrator Password: Administrator
Step3: Creating Repository Schema in Database.
1. Open SQL PLUS Type / as sysdba press enter
2. 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;
Confidential
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 login
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 Dropdown 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
Confidential
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
Creating Integration services :
1. 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
a. Name : RRITEC_INT
b. select License and Node from dropdown click Next
Confidential
2. Provide repository information as for below
a. Select Repository Service : RRITEC_REP
b. Username : Administrator
c. Password : Administrator
d. Data Movement Mode : ASCII
3. Click on Finish
1.4 Repository Manager
1. Repository manager is Window based component

Confidential
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
Confidential
1. Select RRITEC_REP Repository Go to folder menu Click on Create name it as
RRITEC Click on ok Again ok
1.5 Configuring RRITEC Database
1.5.1 Configuring source database
Step 1: Creating user SDBU and load tables into SDBU schema
a. Create user SDBU identified by RRitec123;
b. Grant DBA to SDBU;
c. Conn SDBU@ORCL
3. Go to RRITEC labcopy labdata folder and take full path of driver and execute as for
below
1.5.2 Configuring Target database
Step 1: Creating user TDBU and load tables into TDBU schema
a. Create user TDBU identified by RRitec123;
b. Grant DBA to TDBU;
c. Conn TDBU@ORCL
Confidential
6. Go to RRITEC labcopy labdata folder and take full path of driver and execute as for
below
1.6 Hands on 01_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.
1.6.1 Exercise 1: Import a Source Schema
This exercise walks the student through the process of creating a source definition
from the EMPLOYEE table.
Step 1. Start the PowerCenter Designer.

1. Select Start Programs Informatica 9.0.1 Client Power Center Client
Power Center Designer.
2. Click on OK
Confidential
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--

username: Administrator
password: Administrator
5. Click the Connect button.
Step 3. Open the folder

Note that there is a difference between an expanded folder and an open folder even
though they both appear the same in the Navigator window.
Before PowerCenter objects can be added, deleted or modified, a folder must be explicitly
opened.
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 workspace.
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 the Source
Analyzer button in the toolbar.
Confidential
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 workspace by selecting View
Workbook.
Step 5. Import the source definition.

A PowerCenter source definition defines the structure, or schema, of source data to be read by
the PowerCenter 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.
Confidential
3. Click on ODBC Data Source Click on System DSN tab Add Oracle in
oraDB11g_home1
4. Finish Provide below information

Data Source Name :RRITEC_SOURCE
TNS Service Name: ORCL
User ID :sdbu
5. Click on OKOK
6. From Drop down of ODBC Data Source select RRITEC_SOURCE
The database user and password are NOT the same as the PowerCenter repository user
and password.
Confidential
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
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 workspace:
15. Note that the EMPLOYEE source definition is also added to the Sources node in the
Navigator window:
Confidential
Step 6. Edit the source definition.
1. In the Source Analyzer workspace, 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.
Confidential
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 PowerCenter client by selecting Tools |
Customize.
2. The Customize dialog box appears.
Confidential
3. Check the Repository checkbox on the Toolbars tab:
4. Click OK.
5. The new toolbar appears at the top right of the Designer window:
Note that all work must be manually saved in the PowerCenter Designer. Additionally, work
can only be saved in one folder at a time. The Save command will save the contents of the
folder that is currently active in the workspace.
While working in the PowerCenter Designer, save often to avoid losing any changes!
Save all work by selecting Repository | Save or by typing Ctrl-S.
6. In the output windows Save tab, scroll up for a message confirming your work is
saved.
Step 8. Clear the workspace.
1. Right-click anywhere in the workspace and select Clear All.

2. To view a source definition in the workspace, select the source definition in the
Navigator window, then drag and drop it into the Workspace window.
Confidential
1.6.2 Exercise 2: Import a Target Schema
In this exercise, the student will import a target definition for the relational database
table, ODS_EMPLOYEE.
Step 1. Open the Warehouse Designer.
Select Tools | Warehouse Designer, or click the Warehouse 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.
3. Click on ODBC Data Source Click on System DSN tab Add Oracle
in
Confidential
oraDB11g_home1
4. Finish Provide below information

Data Source Name :RRITEC_TARGET
TNS Service Name: ORCL
User ID :tdbu
5. Click on OKOK
6. From Drop down of ODBC Data Source select RRITEC_TARGET
7. Enter the database username and password:
username: tdbu
owner: TDBU
password: Rritec123
Confidential
8. Click on Connect.
9. Expand the database owner name.
10. Expand the TABLES node.
11. Select the ODS_EMPLOYEE table:

13. The new target table definition now appears in the Warehouse Designer
workspace:
14. Note that the EMPLOYEE target definition is also added to the Targets folder in
the Navigator window:
Confidential
Step 3. Save the target definition.
1. Save all work by selecting Repository | Save or by typing Ctrl-S.
1.7 Handson 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:
Confidential
Exercises
1.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 dataflow between sources and targets. The instructions defined in
the mapping tell the Informatica Server how to read, transform and write the data.
Step 1. Open the Mapping Designer tool.

3. In the Navigator window, select the RRITEC folder
4. 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
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:
Confidential
2. Drag-and-drop the EMPLOYEE source definition to the far left side of the workspace.
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 far right side of the
workspace.
5. The workspace should look as follows:
Confidential
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, precisions and scales:
8. Click the OK button to close the dialog box.
Confidential
Step 5. Create an expression transformation.
1. If the toolbar is not visible, activate it by selecting Tools | Customize, then select the
Transformations checkbox
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 workspace.
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 workspace to the right of the sq_EMPLOYEE source qualifier. In
the workspace, the cursor appears as crosshairs Left-click the mouse.An Expression
transformation is created.
An alternative method is : select Transformations Create from the menu and then
choose Expression as the transformation type. In this method, you name the
transformation at the time you create it.
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.
3. Rename the transformation to exp_ODS_EMPLOYEE.
Confidential
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
checkmark 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 checkmark 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 in the Expression column of the NAME_out port and notice the arrow that
appears at the far right:
Confidential
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.
Ports
tab
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 spacebar 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.
Confidential
15) Click OK to close the Edit Transformation dialog box.
16) Save the repository.
Using the || string operator instead of CONCAT improves Informatica Server performance
in processing the formula.
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 workspace and select Arrange All
the mapping should look like the following:
Step 11. Validate the mapping.

1. Save (CTRL+ S) the repository.
2. Every time a repository is saved, mapping validation checks are performed.
3. Click on the Save tab in the Output window.
4. Expand the Output window and scroll up until the validation test details are visible:
Confidential
5. Verify that there are no errors.
1.8 Hands on 03: Creating Workflow
The exercises in this lab familiarize the student with the process of creating a simple workflow
with a start task and session task. This workflow will essentially define a run-time version of
the mapping created in the previous lab. The exercises will also familiarize the student with
the process of starting and monitoring a workflow.
Objectives
Use the Workflow Manager.

Create a new workflow.
Create and edit session tasks.
Link tasks within a workflow.
Validate a workflow.
Start a workflow.
Use the Workflow Monitor to monitor a workflow.
View session information and the session log after a
workflow has run.
Exercises
1.8.1 Exercise 1: Create a Workflow
In this exercise, the student will create a new workflow.
Step 1. Start the Workflow Manager.

1. Select Start Programs Informatica 9.0.1 Client Power Center Client
Power Center Workflow Manager.
2. Click on OK
Confidential
Step 2. Connect to the repository.
6. Select the RRITEC_REP repository.
7. Right Click on Repository RRITEC_REP | Connect.
8. The Connect to Repository dialog box appears:
9. Enter the username and password--

username: Administrator
password: Administrator
11. Right-click on the RRITEC folder and select Open.
Confidential
12. Open the Workflow Designer by selecting Tools | Workflow Designer or click the
Workflow Designer button above the workspace.
Step 3. Set the default target load type

1. Select Tools | Options, Miscellaneous tab, and set the default target load type
for all sessions to Normal.
2. Target Loads are two types
o Normal
o Bulk
3. If target table has index then we must use normal load method
4. If target table has no index then we can use either normal or bulk .However bulk is
recommended
5. Bulk load is performance wise better than normal load
Confidential
6. Click OK.
Step 4. Create a new workflow.
A workflow is a set of instructions that tells the Informatica Server how to execute tasks.
Every workflow contains a start task, which represents the beginning of the workflow.
1. Select Workflows | Create.

2. The Create Workflow dialog box appears.
3. On the General tab, enter wf_ODS_EMPLOYEE in the Name field.
4. Click OK.
5. The Start task appears in the Workflow Designer workspace:
Confidential
6. Save the repository.
1.8.2 Exercise 2: Create source and target database connections
Creating Source connection :
1. Go to Connection menu Click on Relational Click on new Select oracle

click on OK
o Name : Native_source
o Username : sdbu
o Password :Rritec123
o Connect String : ORCL
2. Cleck on ok click on close

Creating Target connection :
1. Go to Connection menu Click on Relational Click on new Select oracle

click on OK
o Name : Native_target
o Username : tdbu
o Password :Rritec123
o Connect String : ORCL
Confidential
2. Cleck on ok click on close
1.8.3 Exercise 3: Create a Task and Run the Workflow
In this exercise, the student will create a session task.
Step 1. Create a session task.
Session tasks represent mappings for the Informatica Server to run. The Informatica Server
uses the instructions configured in the session to move data from a source to a target.
1. Note that each session task is associated with a single mapping. Click on the Session
button on the Task toolbar.
This appears by default at the top right of the Designer window and may be part
hidden. Click on the vertical bar at the left of the toolbar to drag it to another position.
2. Move the pointer into the workspace and click The Mappings dialog box appears
Select the mapping m_ODS_EMPLOYEE Click OK.
3. The session task s_m_ODS_EMPLOYEE appears in the workspace.
Note : We also can create using navigation Tasks Create provide name
m_ODS_ EMPLOYEE Create Select the mapping m_ODS_EMPLOYEE Click
OK
Step 2. Edit the session tasks properties

1. Double-click on the s_m_ODS_EMPLOYEE session task. The Edit Tasks dialog
appears Select the Properties tab
Confidential
2. Note that the default Session Log File Name, s_m_ODS_EMPLOYEE.log, and the
default Session Log File directory, $PMSessionLogDir, may be overridden, but we
will use the defaults for this lab.
3. Select the Mapping tab.
4. In the Navigator window, select the source sq_EMPLOYEE.
In the Connections section, click on the down arrow , select native_source and click
OK.
5. In the Navigator window, select the target ODS_EMPLOYEE.
Confidential
6. In the Connections section, click on the down arrow , select native_target and click
OK.
7. In the Properties section, the Target load type should be defaulted to Normal.
8. Scroll down (if necessary) and check the Truncate target table option checkbox.
9. Click OK to close the Edit Tasks dialog box.

Step 3. Link the workflow tasks.
1. Click the Link button on the Task toolbar.

2. Left-click and drag the cursor between the Start task and the s_m_ODS_EMPLOYEE
task:
3. Toggle off the link mode by clicking again on the Link button, or single click on one of
the objects.
Step 4. Validate the workflow.

1. In order for a workflow to be valid, all tasks must be linked. Save the repository.
2. Locate the Save tab in the Output Window at the bottom of the Workflow Manager
and view the results of the validation check:
Confidential
Step 5. Start the workflow.
1. Select Workflows | Start workflow, or right-click in the workspace and select Start
Workflow.
2. The Workflow Manager dialog box appears:
1.8.4 Exercise 4: Monitor the Workflow
Step 1. Navigate in Workflow Monitor.
You may wish to maximize the window for readability.
1. A workflow will show a Succeeded status if it starts up and complete successfully, in

and of itself. There may be several tasks within the workflow and each will succeed or
fail but the workflow itself will can complete successfully. One of the only reasons a
workflow will fail is when the Server cannot write the workflow log. The Workflow
Monitor window opens automatically when a workflow is started and automatically logs
into the repository, displaying the workflow status.
2. The Workflow Monitor has two tabs Gant Chart and Task View. Look at the Task
View.
3. Notice the two objects:

wf_ODS_EMPLOYEE (workflow)
s_m_ODS_EMPLOYEE (session task)
4. The Status column shows the session status:
Succeeded indicates that the Informatica Server was able to successfully complete
a workflow or task.
Confidential
Failed indicates that the Informatica Server could not complete the workflow or task
due to a fatal processing error.
Step 2. View the session properties.

1. Session properties provide detailed information about processing, such as the number
of rows loaded into the target by a session task. Locate the s_m_ODS_EMPLOYEE
session task under the Workflow Run column
2. Right click on s_m_ODS_EMPLOYEE session click on get run
propertiesObserve source success rows and target success rows
Step 4. View the session log.
The session log is copied (via ftp) from the Informatica Server machine, and displayed on the
client machine as TmpSessionLog_x.
If errors have occurred, review the session log to determine what the problem is. Test and
rerun as necessary.
1. Right-click on the session in the Task Name column (Time window) and select Get
Session Log, or by clicking on the Session Log button.
Confidential
2. The session log is displayed:
1.9 Hands on 04: Flat file Filter
Lab at a Glance
The exercises in this lab are designed to walk the student through the process of importing and
analyzing a flat file source using the Flat File Wizard. The student will also learn how to use the
Filter transformation.
Objectives
Use the Flat File wizard.

Create a Filter transformation.
Summary
In this lab, the student will create a mapping (and associated workflow) to extract data from a
flat file that contains date records.
An example of data from the flat file follows:
Confidential
The mapping must transform the data and load it into a target table as follows:
In addition, the mapping must filter the data for only those dates after 12/04/1999.
The final mapping should look as follows:
Confidential
Confidential
1.9.1 Exercise 1: Import Source and Target
Step 1. Import the file

5. Go to RRITEC LabCopy/LabData folder copy TIME.txt file and paste in
Informatica\9.0.1\server\infa_shared\SrcFiles
6. In the Designer, open the Source Analyzer by clicking the Source Analyzer button
or by selecting Tools | Source Analyzer
7. Select Sources | Import From File.
8. The Open Flat File dialog box appears Change the Files of type: to All Files(*.*)
Locate the directory where the flat file resides typically
Informatica\9.0.1\server\infa_shared\SrcFiles
9. Select the flat tile TIME.txt Click OK.
10. The Flat File Import Wizard appears.

11. Ensure the Delimited radio button is selected (default).
12. Select the Import field names from first line checkbox.
Confidential
13. Click Next.
14. Make sure the Comma checkbox is selected under Delimiters.
15. Make sure the Double Quotes radio button is selected under Text Qualifier.
16. Click Next

17. By importing the field names from the first line, the port names have already been
created. No other modifications are necessary.
Confidential
18. Click Finish.
19. The source definition should look as follows:
The first row in the TIME.txt file contains the column names. When this option is checked, the
import automatically starts at row 2 and assumes that future extracts will start at row 2.
The wizard makes a best guess at the data type, length and width of the columns based on the
data read from the first 500 rows.
Step 2. Import the target table
1. Open the Target Designer by clicking the Target Designer button on the toolbar.
2. Select Targets | Import From Database.
a. ODBC Data Source : RRITEC_TARGET
b. Username : TDBU
c. Owner :TDBU
Confidential
d. Password :RRitec123
3. Click on Connect
4. Expand the TDBU schema and TABLES node.
5. Select ODS_TIME Click OK The ODS_TIME target table should look as follows:

1.9.2 Exercise 2: Create the Mapping
Step 1. Create a mapping.
1. Open the Mapping Designer by clicking on the Mapping Designer button

2. Create a new mapping called m_ODS_TIME
Step 2. Add source and target Tables.

1. From the FlatFile category under the Sources node, drag-and-drop the TIME source
definition into the Mapping Designer workspace.
2. From the Targets node, drag-and-drop ODS_TIME into the Mapping Designer
workspace.
Step 3. Create an Expression transformation.

1. Create a new Expression transformation.
2. Select all ports within the sq_TIME source qualifier, then drag-and-drop them onto the
Expression transformation.
3. Double-click on the header of the Expression transformation.
4. The Edit Transformations dialog appears.
6. Enter exp_ODS_TIME in the Transformation Name field and click the OK button.
7. Click on the Ports tab.
8. Highlight the third port, QUARTER, then add the following ports by clicking the Add Port
button:
DATE_ID, Datatype=date/time, Prec=29(default), Port Type=Output Only
MONTH_out, Datatype=string, Prec=3, Port Type=Output Only
Confidential
QUARTER_out, Datatype=string, Prec=2, Port Type=Output Only
YEAR_out, Datatype=string, Prec=4, Port Type=Output Only
An expression will be needed for each of the above ports to derive the values. Open the
Expression Editor and add expressions for each port as follows:
DATE_ID TO_DATE(DATE,DD-MON-YYYY)
MONTH_out
QUARTER_out SUBSTR(QUARTER,6,1)
YEAR_out SUBSTR(QUARTER,1,4)
9. Change the ports, DATE and QUARTER, to input only ports.
10. The Ports tab of the exp_ODS_TIME expression should look as follows:

12. The expression transformation should look as follows:
Step 4. Create a Filter transformation.
1. Click on the Filter Transformation button on the Transformation toolbar.

2. Click and drag the pointer (which appears as crosshairs in the workspace) to the
right of the exp_ODS_TIME expression.
3. Drag and drop the following ports in the order specified from exp_ODS_TIME to the
FILTRANS window:
DATE_ID
DAY_OF_WEEK
MONTH_out
QUARTER_out
YEAR_out
4. Double-click on the Filter transformation.
Confidential
5. The Edit Transformations dialog box appears.
6. Click the Rename button and enter fil_ODS_TIME.
7. Click on the Ports tab and rename the following ports:
MONTH_out = MONTH
QUARTER_out = QUARTER
YEAR_out = YEAR
8. Click on the Properties tab.
9. Click on the down arrow to edit the Filter Condition:
10. To define a new filter condition, remove the default text, TRUE.
11. Open the Expression Editor and click on the Ports tab to the right of the Functions
tab.
12. Double-click on DATE_ID.
13. Click the greater than or equals button on the operator keypad.
14. Click the Functions tab, expand the Conversion folder and double-click on
To_Date.
15. To complete the expression, type '12/04/1999', 'MM/DD/YYYY' between the
parentheses. The final expression should look as follows:
DATE_ID >=TO_DATE('12/04/1999', 'MM/DD/YYYY')

16. Click on the Validate button to validate the filter condition.
17. Click OK twice to save changes.
18. The Filter transformation should look as follows:
Confidential
Step 5. Link the Target transformation.
1. Right-click in the workspace and select Autolink
2. Select fil_ODS_TIME in the From Transformation field and ODS_TIME in the
To Transformations field.
3. Make sure that the Name radio button is selected in the By field:
4. Click OK.
5. All ports should be linked between these two transformations:
6. Validate the mapping by selecting Mapping |Validate.

7. Remember to check the Validate tab in the Output window for the results.
9. The completed mapping should look as follows:
Confidential
1.9.3 Exercise 3: Create and Run the Workflow
In this exercise, the student will create a workflow to run and test the new mapping.
Step 1. Start the Workflow Manager.

Open the Workflow Manager Connect to the repositoryOpen the RRITEC folder.
Step 2. Create a workflow.

1. Select Workflows | Create to create a new workflow.
2. Name the workflow wf_ODS_TIME
3. Create a session task for the m_ODS_TIME mapping called s_m_ODS_TIME
4. Double-click on the session task. The Edit Tasks dialog box appears.
Select the Mapping tab.
In the Navigator window, select the source SQ_TIME.
Under Properties, TIME File Reader, Source filename, enter TIME.txt.
In the Navigator window, select the target ODS_TIME.

Under Connections, click on the down arrow , select native_target and click OK.
Under Properties, the Target load type should be defaulted to Normal. Scroll down
to select the Truncate target table option.
Click OK to close the Edit Tasks dialog box.
Save the repository.
Link Start to s_m_ODS_TIME_xx.
Confidential
Save, validate and start the workflow.
Monitor and review the results for s_m_ODS_TIME in the Workflow Monitor.
Confidential
2. DAC 11G
2.1 DAC Introduction
1. Is a centralized console to design, configure and execute ETL for loading the Oracle
Business Analytics Warehouse(OBAW)
2. Single command and control point for the OBAW
3. Provides a framework for managing the entire life cycle of OBAW implementations
4. Provides an easy-to-use interface for deploying, defining, administering and monitoring
of Data warehouse processes
5. Simplifies ETL customization and execution
1. Define ETL subject areas and execution plans
2. Automate configuration of ETL for full and incremental load of subject areas
3. Automatic index management
4. Prioritize and load balance ETL workflow execution
5. Compile historical tracking of diagnostic ETL logs
6. Provide restart of ETL execution from point of failure
7. Update database statistics on OBAW tables and indexes
6. DAC present version is 11G
7. DAC 11g Supports ODI objects executions
2.2 DAC Installation
1. Download software from oracle website
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bi-
downloads-1525270.html
2. In downloaded software navigate to below location and double click on 11G
Confidential
3. In Welcome screen click on NEXT
4. Provide installation location C:\orahome\10gR3_1 Click on Next
Confidential
5. Select Informatica 8.x Click on Next
6. Provide paths Informatica PowerCenter : C:\Informatica\9.0.1\server Informatica

Domain File : C:\Informatica\9.0.1\ domains.infa Click on Next
7. In Prerequisites window Click on Next
Confidential
8. In Installation Summary Window Click on Next
9. In Installation Summary Window Click on Next
Confidential
10. In Installation Completed Window Click on Finish
Post installation activities :
1. Copy OJDBC6 file from oracle location

C:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib to DAC location
C:\DAC_HOME\dac\lib
2. Creating DAC Connection
a. Open DAC Client : start All Programs Oracle Business Intelligence Data
Warehouse Administration Console 11g DAC Client
b. Click on Configure Select Create Connection
Confidential
c. Creating user DAC in database
i. Open SQL PLUS Type / as sysdba press enter
ii. Create a user by executing below commands
1. Create user DAC identified by dac;
2. Grant DBA to DAC;
3. Conn DAC@ORCL
4. Password dac
5. Select count (*) from tab;
d. Provide Name : DAC TNS Name ORCL
e. Click on Authentication File From drop down select Create authentication

file Click on OK
Confidential
f. Again click on OK Name it as DAC.con
g. Click on OK Provide Table Owner Name : dac and password : dac Click on
OK
Confidential
h. Click on Test Connection Click on ok
i. Click on Login do not provide anything in table space Click on Yes
Confidential
j. Provide username : dac password : dac Conform Password : dac
k. Go to Tools menu Click on DAC Server Management Click on DAC

Server Setup
l. Click on Yes
m. Click on Populate from preconfigured client connection Click on OK
Confidential
n. Click on Test Connection Click on Save
Note: DAC repository is going to store in database with the help of 145 tables
3. Setting Environment Variables :
a. Copy below paths Right click on my computer Click on Advanced tab

Click on Environment Variables
Confidential
1
D:\Informatica\9.0.1\server\bin;C:\orahome\10gR3_1\bifoundation\dac\lib;C:\orahome\10
gR3_1\bifoundation\dac\bin
b. Double click on Path variable End of the path type ; provide entire above
path Click on OK Again OK
c. Start Dac 11g Server : C:\DAC11G\dac double click on startserver.bat
Confidential
2.3 DAC Configuration
Step1: Sign in to DAC
1. Start All program DAC Client provide below login information
1. Connection : dac
2. User name : dac
3. Password : dac
Step2: Creating Container
1. In DAC repository we may create n number of containers but always best practice is
create only one container.
2. Container is useful to store DAC metadata (Task Names, Task Groups , subject area,
execution plans etc)
Process:
1. Go to file menu Click on new Source system container provide id as rritec123 and
name as rritec select create empty new container Click on ok
Step3: Configure Database.
1. Click on Setup tab double click on Physical data sources click on New
provide below information
1. Name : ORCL_SDBU
2. Type : Source
3. Connection type : Oracle Thin
4. TNS name : ORCL
5. Table owner : SDBU
6. Table owner password : RRitec123
Confidential
7. Dependency priority : 1
8. Data source number : 1521
2. Click on save ok
3. Similarly another connection with name ORCL_TDBU
a. Name : ORCL_TDBU
b. Type : Warehouse
c. Connection type : Oracle (oci8)
d. TNS name : ORCL
e. Table owner : TDBU
f. Table owner password : RRitec123
g. Dependency priority : 1
Confidential
h. Data source number : 1521
Step4: Configure Informatica Repository Server.
1. Click on Setup tab double click on Informatica Server Click on new
1. Name : INFORMATICA_REP_SERVER
2. Type : Repository
3. Hostname : local host
4. Server port : 6005
5. Domain : Domain_rritec
6. Login : Administrator
7. Password : Administrator
Confidential
8. Repository name : RRITEC_REP (open Informatica Admin console and name as
per the console repository name )
2. Click on save Click on test connection Click on ok.
Step5: Configure Informatica Integration services.
1. Click on Setup Informatica Services go to new
1. Name : INFORMATICA_INT_SERVER
2. Type : Informatica
3. Services : RRITEC_INT
4. Domain : Domain_rritec
5. Login : Administrator
6. Password : Administrator
7. Repository name : RRITEC_REP (open Informatica Admin console

and name as per the console)
2. Click on save Click on test connection Click on ok.
Confidential
Step6: Configuring Informatica physical folder.
1. This name should be same as Informatica folder name
2. Go to Tools menu Click on Seed Data Click on Task Physical Folder
3. Click on New Name it as RRITEC click on save Click on Close.
Step7: Creating Logical Folders.
1. Go to tools menu Click on Seed data Click on Task Logical Folder

2. Click on New Name it as RRITEC Click on save Click on close.
Confidential
Step8: Mapping Logical folder And Physical folder.
1. Go to Design tab select your container double click on Source System Folders
select New Select
Logical folder as RRITEC Select Physical folder as RRITEC
2. Click on Save.
Step 9: Creating Logical Data Sources
1. Go to Tools menu Click on Seed data Click on Logical Data Source

2. Click on New Name it as source Click on Save
3. Again click on New Name it as target Click on Save Click on Close.
Step 10: creating Task Phases.
1. Task phase is useful for Priority of Tasks (workflows)

2. Go to Tools menu Click on Seed Data Click on Task Phases.
3. Click on New Name it as General Click on Save Click on Close.
Confidential
2.4 Development Activities
1. Creating Tasks
2. Creating Task Group
3. Creating Subject Area
4. Creating Execution Plans
5. Creating Schedules
6. Exporting & Importing(Deployment)
2.4.1 Creating Tasks
1. It is a lowest object in DAC

2. In DAC task is equal to Informatica a work flow
3. In one task we can map minimum one work flow and max is 2.
Process:
1. Open DAC client and provide user name and password as dac.
2. Click on Design tab double click on Task Click on New provide below
information
1. Name : wf_ODS_EMPLOYEE
2. Command for incremental load : wf_ODS_EMPLOYEE
3. Command for Full load : wf_ODS_EMPLOYEE
4. Folder name : RRITEC
5. Primary source : source
6. Primary target : target
7. Task phase : General
8. Execution type : Informatica
Confidential
3. Click on Save.
4. Right click on Task wf_ODS_EMPLOYEE Click on Synchronize Task Click on ok
Click on ok.
5. Similarly create one more task with name of wf_ODS_TIME and use workflow as
wf_ODS_TIME
6. Synchronize Task: It will import source and target tables metadata into DAC repository
Confidential
2.4.2 Creating Task Groups
1. Task group is mainly used to integrate n number of tasks.

2. In Task group we will configure dependency order of the task. This dependency order
will be shown as Depth in Execution plan.
3. Dependency order smallest to highest will be running , If dependency order is same for
2 Tasks then both will run parallel.
Process:
1. Click on Design tab double click on Task Groups Click on new

Name: RRITEC_TASKGROUP Click on Save.
2. Click on Child task click on Add/Remove Click on Go Select

wf_ODS_EMPLOYE and wf_ODS_TIME Click on Add Click on ok
3. Under dependency order give 1 and 2 Click on ok Click on Save.
2.4.3 Creating Subject Area
1. A Subject area is equal to Business area or Data mart. OR

2. A group of Tasks and Task groups are called as Subject area.
Ex: Loyalty, Marketing, Sales, Finance.
Process:
Confidential
1. Click on Design tab Double click on Subject area Click on new Name:
RRITEC_SA Click on Save.
2. Click on Tasks click Add/Remove select wf_ODS_EMPLOYE , wf_ODS_TIME
and RRITEC_TASKGROUP
3. Click on Add Click on ok Click on Save.
4. Right click on Subject area click on Assemble Click on ok Click on Accept
Click on ok Click on Save.
2.4.4 Creating Execution plan
1. It is used to integrate n number of Subject areas.

Process:
1. Click on Execute Tab Click on Execution Plans Click on new Name:

RRITEC_EP Click on Save.
2. Click on Subject Areas tab Click on Add/Remove select RRITEC_SA Click on
Add Click on ok Click on Save
3. Click on Parameters tab Click on Generate Click on ok
Type Name Value
Data Source source ORCL_SDBU
Data Source target ORCL_TDBU
Folder RRITEC RRITEC
4. Click on Save.
5. Right click on Execution plan click on Build Click on ok
Running Execution plan:
1. Start the DAC server (start all programs DAC Start Server).
2. Select RRITEC_EP execution plan click on Run now click on Yes Click on ok
3. Go to Informatica Workflow monitor connect to our Repository RRITEC_REP
Open Folder RRITEC observe that workflows are running.
Note: At a time only one Execution plan can run in DAC 10g however this limitation lifted in 11g
.
Confidential
2.4.5 Scheduling
1. Click on Execute tab click Scheduler Click on new

2. Provide name as RRITEC_SC select Execution plan as RRITEC_EP
3. Select Run only once give Start date as 2 min forward from current time save
Note: The Execution time may delay 10 60 sec.
2.4.6 Configuring Email
1. Any change in the server status will be mailed to corresponding users.

Process:
1. Click Setup tab E-mail Recipients Click on new Click on provide

a. Name : Ram Reddy
b. E-mail : rritec@gmail.com
2. Click on Save.
Configuring SMTP Server:
Confidential
2.4.7 Deployment
1. Copying DAC Repository from one environment to another environment is called as

Deployment.
Method 1: Data Base Move.
1. DAC total work will be saved in backend schema what we configured (in RRITEC it is
DAC USER).
Method 2: Using DAC client
Step1 : Exporting DAC Metadata (Taking Back up of DAC Repository)
1. Go to Tools menu DAC Repository Management Export

2. Select Rritec container click on Change Import & Export folder select Desktop
create new folder and name it as DAC_BACKUP select it and click ok Select all
the options under Categories under Application list select Rritec click ok ok.
(After few min.. Export process will be completed).
Confidential
Step2: Importing
1. Once we exported in Development environment that we can import into testing server
2. Go to Tools menu DAC Repository Management Import Select
DAC_BACKUP folder ok.
Confidential
3. OBIA 7964
3.1 OBIA Introduction
1. OBIA stands for Oracle Business Intelligence Applications Or Oracle BI Apps
2. Current version of OBIA is OBIA 11G(11.1.1.9.2) ( OBIEE 11.1.1.7.1/9.0+ ODI 11.1.1.7/9 G)
3. Previous to this OBIA 7.9.6.4 ( OBIEE 11.1.1.7 G + Informatica 9+DAC 11 G)
4. OBIA is a predefined work of ODI,Informatica,DAC and OBIEE
5. OBIA 7.9.6.3 we need to use with OBIEE 11.1.1.6 and Informatica 9
6. OBIA 7.9.6.4/3 are compatible with informatica 9th version, DAC 11g/10G and OBIEE 11G.
7. Previous to this we have 7.9.6.2 it is compatible with informatica 8th /7th versions, DAC 10G
or DAC 7th version and OBIEE 10G.
8. OBIA pre defined work also known as Out of Box(OOB) work
9. OBIA analytics are divided into three types
1. CRM Analytics
Marketing Analytics
Sales Analytics
Service Analytics
..etc
2. ERP Analytics
HR analytics
Finance
SCOM
Procurement and Spend Analytics
..etc
3. Industry analytics
Telecom
Pharma
..etc
Confidential
3.2 Main components of OBIA 7.9.6.4
1. Informatica ETL (SDE, SIL, PLP)
2. DAC repository (useful to schedule informatica ETL)
3. OBAW (predefined model)
4. RPD
5. Web Catalog
OBIA end-to-end solutions are available in 5 flavors.
1. Source System as oracle APPS(EBS)
2. Source System as JDEdwards
3. Source Application as PeopleSoft
4. Source Application as Siebel
5. Source Application as Universal (for any source apps)
3.3 OBIA 7964 Installation
1. Before start OBIA installation make sure OBIEE installed and running
2. Download software from OTN/eDelivery
3. Double click on setup file welcome screen will appear
4. Click Next to display the Specify Installation Location screen.
a. Enter the BI Oracle Home Location : C:\OBIEE_HOME\Oracle_BI1
b. Enter the BI Instance Location : C:\OBIEE_HOME\instances\instance1

Confidential
c. Enter the Domain Home Location:
C:\OBIEE_HOME\user_projects\domains\bifoundation_domain
5. Click Next to display the WebLogic Administration Server Details screen.
a. Enter the port number :7001
b. Enter the user name :weblogic (OBIEE installation username)
c. Enter the password :RRitec123 (OBIEE installation password)
6. Click Next to display the Select the Oracle Business Intelligence Applications...
screen Select required analytics or all analytics
7. Click Next to display the summary screen observe summary Click on next click
on finish
Post Installation verification:
1. Make sure biapps folder created under C:\OBIEE_HOME\Oracle_BI1
2. Note: All analytics metadata RPD is :EnterprisebusinessAnalytics.rpd .At the time of

OBIA installations ,if we select particular analytics then the RPD is
OracleBIAnalyticsApps.rpd
3.4 CONFIGURATION
3.4.1 STEP1: Configuring Informatica Repository
1. Creating Repository Schema in Database.
a. Open SQL PLUS Type / as sysdba press enter
b. Create a user by executing below commands
c. Create user <username> identified by RRitec123;
d. Grant DBA to <username>;
e. Conn <username>@ORCL
f. Password RRitec123
g. Select count (*) from tab;
2. Creating Informatica empty repository
a. Go to Informatica Administrator home page Provide

Confidential
b. User name: Administrator Password: Administrator
c. Select Domain_rritec click on Action New power center repository

service
d. Name it as BIA_RS. Select license and node from dropdown click on next
e. Select DB type as oracle give Username & password as BIA_RS & RRitec123
Connection string as ORCL Select First radio button (content exists
under..) Click on finish.
f. Select BIA_RS Go to actions enable service.
Confidential
3. Importing OBIA repository Content
a. Download patch (Patch 18104144)rep file(Infa951RepFor7964) or take from

RRITEC labdata folder and copy into
c:\informatica\9.0.1\server\infa_shared\backup
b. Select BIA_RS Repository go to Action Repository content Restore
c. Select backup file name as Infa951RepFor7964 Select Restore as new
d. Click on ok again ok
e. connect to BIA_RS in database and observe that 512 tables are created.
f. Change Repository operating mode from exclusive to Normal
4. Creating Integration Service
a. Select Domain_rritec Go to action new power center integration service.
b. Provide name as BIA_IS Select license and node from dropdown next
c. Select repository service as BIA_RS username & password as administrator

Click on finish.
d. Select BIA_IS Go to actions Enable service.
5. Adding BIA_RS repository to Informatica clients
a. All programs Informatica Client power center client power center

Designer.
b. Go to repository menu click on Add name it as BIA_RS user name as

administrator click on ok.
c. Right click on BIA_RS click on Connect Select domain from dropdown

provide user name& password as a administrator Click on connect
6. Configuring Source(OLTP)
Confidential
a. Default vision database installed with EBS installation , with the name of VIS
b. Apps user is a master user and will be able to communicate with all modules like
AP,AR,GL..etc
c. Make sure we are able to connect OLTP open sqlplus conn apps/apps@vis
d. Open work flow manager Connect Repository BIA_RS
e. Go to connections menu New select connection type as Oracle
f. Click on New name it as VIS username & password as apps & apps
connection string as vis click ok click on close.
7. Create Target(OLAP/OBAW) Connection (Note : This step validation we have to do after

STEP 3 OBAW configuration)
a. Open work flow manager right click on SDE_UNIVERSAL_ADAPTOR click

on Open
b. Go to connections menu New select connection type as Oracle
c. Click on New name it as DATAWAREHOUSE username & password as

dev_dw& RRitec123 connection string as orcl click ok click on close.
8. Observe one dimension table
a. SDE MAPPING UNDERSTANDING(SDE_ORA_AbsenceEvent)
SOURCE DATABASE
b. PER_ABSENCE_ATTENDANCE_TYPES
c. PER_ABS_ATTENDANCE_REASONS
d. PER_ABSENCE_ATTENDANCES
Note : DEV_DW schema has staging and DW tables
STAGING AREA
e. W_ABSENCE_EVENT_DS
SIL MAPPING UNDERSTANDING(SIL_AbsenceEventDimension)
f. Source W_ABSENCE_EVENT_DS
g. Target W_ABSENCE_EVENT_D
PLP(Post Load Process) : Mainly useful to create agg tables ,stats analysis ..etc
Confidential
3.4.2 STEP2: Configuring DAC Repository
1. Import OOB DAC repository
a) Open DAC go to Tools menuDAC repository management Click on Import
b) Change Import/Export folder and point to

C:\OBIEE_HOME\Oracle_BI1\biapps\biapps7964\dwrep\dac_metadata\dac_client\ex
port
c) Select OracleR12.1.1 and universal application click on ok.
2. DAC configuration after import
a) select DAC server host as local host In DAC11g make sure host is configured as
for your dac server
3. Create Source Database Connection
a) Click on physical data sources select Oracle R12.1.1 connection ->provide
b) Name: VIS_OLTP
c) Type: Source
Confidential
d) Connection type: oracle (oci8)
e) TNs name :(VIS)
f) Table owner: apps
g) Table owner password: apps
h) Datasourcename: 1521
i) Dependency priority: 10
j) Click on Test connection -> click on ok
4. Create Target Database Connection
a) Click on physical data sources select DataWarehouse connection ->provide
b) Name: DataWarehouse
c) Type: warehouse
d) Connection type: oracle (oci8)
e) TNs name :(orcl)
f) Table owner: DEV_DW
g) Table owner password: RRitec123
h) Datasourcename: 1521
i) Dependency priority: 10
j) Click on Test connection -> click on ok. (it will fail because we did not created user)
5. Informatica Repository Services
a) Click on Informatica Servers tab select Informatica_rep_server change
b) Hostname: local host
c) Server port: 6005
d) Domain: domain_RRitec
e) Login: administrator
f) Password: administrator
g) Repository name: BIA_RS
h) Click on save click on Test connection.
6. Configuring Informatica Integration server

Confidential
a) Select informatica_dw_server
b) Service name: BIA_IS
c) Domain: domain_rritec
d) Login: administrator
e) Password: administrator
f) Repository name: BIA_RS
g) Click on Save Test connection click ok.
3.4.3 STEP3: Configuring OBAW (or) Creating OBAW
1. Creating user
a) Login to SQL PLUS as / as sysdba
b) Create user DEV_DW identified by RRitec123
c) Grant DBA to DEV_DW
2. Creating OBAW Tables
a) Open DAC and login as Connection: dac Name: dac Password: dac
b) Go to tools menu ETL managementconfigure select target & source as oracle

Click ok
c) Select click on Next Select

target connection as DatawarehouseClick on StartFinish
d) Connect to DB using DEV_DW user and type select count (*) from tab and observe
1000+ tables created.
3. Types of Tables
Confidential
a) Out of these 1000+ tables half of the tables are staging tables remaining half are DW
tables which we are going to use input for RPD.
b) Main tables
a. Dimension tables ends with _d
b. Fact tables ends with _f
c. Aggregate tables ends with _a
d. Staging tables ends with _ds or _fs
e. Helper tables ends with _dhs
f. Hierarchy tables ends with _h
4. Running HR Analytics
a) Start the DAC Server Select Respective container Select execution plans tab
Select HR Analytics click on run Click on ok Click on current runclick on
tasks Observe the status
3.4.4 STEP4: Configuring RPD & Catalog
1. Trim the RPD or get the HR analytics RPD from labdata RPDS folder and copy into
repositories folder
C:\OBIEE_HOME\instances\instance1\bifoundation\OracleBIServerComponent\coreapplicati
on_obis1\repository\ OBIA_ONLY_HR.rpd
2. Changing connection pool parameters
a. Open administrator tool open OBIA_ONLY_HR.rpd in offline mode Password as

RRitec123
b. Go to Manage click on variables select Static Variable
i. Double click on OLAP_DSN Change default initialize as ORCLclickon ok.
ii. Double click on OLAP_USER Change username as dev_dw click ok

close variable Manager.
c. Expand Oracle Data Warehouse in physical layer double click on Oracle Data
Warehouse Connection Pool Type password as RRitec123 Click on ok
again Password as RRitec123click ok.
Confidential
d. Expand Oracle Data Warehouse in physical layer double click on Oracle Data
Warehouse Repository Initblocks Connection Pool Type password as RRitec123
Click on ok again Password as RRitec123click ok.
3. Catalog copying
a. Get the Catalog EnterpriseBusinessAnalytics.zip from labdata folder Extract ZIP

file then we will get OracleBIApps folder/Catalog
b. Copy this catalog OracleBIApps into

C:\OBIEE_HOME\instances\instance1\bifoundation\OracleBIPresentationServicesCo
mponent\coreapplication_obips1\catalog
4. Loading rpd & catalog
a. Open Enterprise Manager login as weblogic as user
b. Go to Deployment tab click on Lock & edit configuration click on Close
c. Click on Browse select OBIA_ONLY_HR.rpd
d. Provide password as RRitec123 -> conform password as RRitec123
e. Under BI presentation catalog At the end of catalog type OracleBIApps click on

Apply click on Activate changes Click on Close
f. Go to Availability tab Restart Oracle BI Server, Oracle BI Presentation Server.
g. Open RPD online mode and observe all objects to conform RPD properly loaded or
not .Mainly concentrate on OLAP_DSN & OLAP_USER variable values.
h. Open Analytics and observer all dashboards and reports
3.1 Understanding HR Analytics
1. HR Analytics is ERP based solutions

2. In HR analytics total 9 dashboards are available .those are
.
3. Open Time and Labor dashboard select 2006 and 2007 years click on apply
Observe all reports are showing data
Confidential
4. Similarly observer all other HR dashboards
5. Reverse Engineer Process and understanding Absence Facts and Dims
a. Go to presentation layer of RPD Right Click on Human Resources - Absence
subject area Query related objects BMM layer logical tables observe
all facts and dims
One of the star schema tables are
FACT TABLE
W_ABSENCE_EVENT_F
Dim Table
W_EMPLOYEE_D
W_HR_PERSON_LEG_D
W_ABSENCE_EVENT_D
W_HR_PERSON_D
..etc
b. similarly observe rest of the star schemas of different subject areas

c. similarly reverse engineer and understand rest of the analytics like finance
,supply chain ,sales ...etc
6. Understand Product Guides ,Data Model Reference Doc and Data Lineage Docs
Confidential

Informatica Basic Dac Obia7964

Uploaded by

Copyright:

Available Formats

Informatica Basic Dac Obia7964

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Informatica Basic Dac Obia7964

Uploaded by

Copyright:

Available Formats

RR ITEC

RR ITEC Informatica Basic + DAC + OBIA 7964 Classroom Notes

1.2.2 Client Installation 9

1.3 Informatica Administration Console 13

1.5.2 Configuring Target database 19

1.6 Hands on 01_Source_Target_Import 20

1.6.2 Exercise 2: Import a Target Schema 28

1.7 Handson 02_Creating_mapping 31

1.8 Hands on 03: Creating Workflow 39

1.8.2 Exercise 2: Create source and target database connections 43

1.8.3 Exercise 3: Create a Task and Run the Workflow 44

1.8.4 Exercise 4: Monitor the Workflow 47

1.9 Hands on 04: Flat file Filter 49

1.9.2 Exercise 2: Create the Mapping 55

1.9.3 Exercise 3: Create and Run the Workflow 59

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 2

2.4.3 Creating Subject Area 80

2.4.4 Creating Execution plan 81

2.4.6 Configuring Email 82

3.4.2 STEP2: Configuring DAC Repository 91

3.4.3 STEP3: Configuring OBAW (or) Creating OBAW 93

3.4.4 STEP4: Configuring RPD & Catalog 94

3.1 Understanding HR Analytics 95

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 3

1.1 INFORMATICA Introduction

1. Informatica is an ETL tool (or) Integration tool

3. In Informatica latest version is INFORMAICA 9.5

1.2 INFORMATICA 9 Installation

1.2.1 Server Installation

Step 1 : Double click on install.exe

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 4

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 5

o Choose Create a Domain radio button.

Step 7: Provide the Repository database details as below

o Database Type : Choose your Repository database (Oracle/SQL Server/Sybase)

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 6

Below image shows the configuration using SQL Server.

o Domain Name : Name of your Domain.

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 7

Use the default configuration and Click Next.

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 8

Step 1 : Click on the install.bat On Installation wizard Starts Click Start.

Step 2 :Choose the installation type as in the below imageClick Next.

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 9

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 10

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 11

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 12

1.3 Informatica Administration Console

It is a web based component from 8.0 versions onwards

It is used to handle all admin activities

Administrator is going to work only on this component

1. Creating Repository Services

RR ITEC #209,Nilagiri Block,Adithya Enclave,Ameerpet @8801408841,8790998182 13

1. Repository service is a storage location of informatica source ,target ,mapping,task

Step1: Starting Informatica Server.

1. Start Control panel Administrative tools Services select Informatica 9.0.1

Step2: open Informatica Administration Home page

1. Start all programs Informatica Server Informatica Administration Home

2. Provide username : Administrator Password: Administrator