Source Qualifier Transformation

Exercise 2:Source Qualifier Transformation

Step1: Import source tables

1. Import EMP,DEPT and SALGRADE  tables from SCOTT schema to informatica source Analyzer

Step 2 : Create a target Table and import as a target

1. Open SQL Developer and type below sql query in Target schema
2. CREATE TABLE  EMP_DEPT_SAL_GRADE   (DNAME  VARCHAR2(20),DEPT_WISE_SALARY NUMBER(10,2),GRADE NUMBER);

Step3: Create mapping with the name of m_SQ1

1. 

   Go to mappings menu bar → click on create → Click on OK

2. Drag and Drop sources EMP,DEPT,SALGRADE  and target EMP_DEPT_SAL_GRADE  tables to mapping workplace
3. Delete the above SQ_DEPT,SQ_SALGRADE source qualifiers tables

4. Drag and drop DEPTNO,DNAME,LOC  from DEPT table to SQ_EMP table
5. Drag and drop GRADE,HISAL ,LOSAL from SALGRADE table to SQ_EMP table.
6. Connect DNAME ,SAL and GRADE   from source qualifier to Target table

7. Double click on Source Qualifier SQ_EMP → Click on Properties Tab
8. Click on SQL Query down arrow mark
9. In SQL Editor window provide below information
1. ODBC data source as “RRITEC_SOURCE”
2. USER NAME as: SOURCE_USER
3. Password as: SOURCE_USER

   

10. Click on Generate SQL → then automatically Query will be generated

SELECT DEPT.DNAME, EMP.SAL, SALGRADE.GRADE

FROM

EMP, DEPT, SALGRADE

WHERE

DEPT.DEPTNO=EMP.DEPTNO

11. Modify Query as shown below

SELECT DEPT.DNAME, SUM(EMP.SAL)SAL,SALGRADE.GRADE                                                                   FROM                                                               

DEPT,EMP,SALGRADE                                                               

WHERE                                                             

DEPT.DEPTNO=EMP.DEPTNO                                                

AND SAL>=LOSAL AND SAL<=HISAL                                                                     

GROUP BY                                                                       

DNAME,GRADE                                                                            

12. Click on OK → Click on OK → Save Mapping
13. Create a session and workflow
14. Run the workflow

ODI 11G INTERFACE FOR TOTALSAL AND TAX

ODI 11G INTERFACE FOR TOTALSAL AND TAX

Step 1: Reverse engineer source into source model

1. right click on scott source model and reverse engineer emp table
2. 

Step 2: Reverse engineer Target into Target model

1. Create table in target database
   CREATE TABLE TGT_EMP

 (

   EMPNO    NUMBER,

    ENAME    VARCHAR2(30),

   SAL      NUMBER ,

    COMM     NUMBER,

   TOTALSAL NUMBER,

   TAX      NUMBER,

    DEPTNO   NUMBER

  )

1. Right click on TDBU Target model and reverse engineer tgt_emp table
2. 

Step 3: Create interface

1. Expand projects → Right click on interface → new interface → name it as m_TOTALSAL_TAX
2. 
3. Go to mapping tab
4. drag and drop source EMP and target TGT_EMP into respective areas → click on ok on auto mapping window
5. 
6. Go to totalsal column → in properties pane develop below expression
7. 
8. Go to Tax column → in properties pane develop below expression
9. 
10. select flow tab  → then select target → select IKM SQL CONTROL APPEND
11. select flow option as FALSE
12. 
13. save → run → observe output of TGT_EMP table
14. 

Creating Stand alone Agent

1. Agents are useful to schedule Procedures (Load plans ,scenarios ..etc)
2. These are three types
1. completely standalone agent (no WLS installed. This OBE uses this kind of agent.)
2. standalone collocated agent (WLS is installed, but is not being used. The agent is started as its own binary.)
3. JEE agent (WLS installed and used. The domain, admin server, managed servers, node managers, and so on are started in the normal WLS way. The agent is one of many possible JEE apps running in the domain. )

Exercise 1 : Creating Stand alone Agent

Step 1 : Creating Physical Agent

1. open ODI studio and connect using SUPERVISOR user
2. Go to topology tab → in physical Architecture right click on Agents → Click on new agent name it as OracleDIAgent → click on save → Click on close
3. 

Step 2 : Creating Logical Agent and Mapping Physical and logical agents in context

1. Go to topology tab → in LogicalArchitecture right click on Agents → Click on new agent name it as OracleDIAgent → under physical agent select OracleDIAgent opposite to development context → click on save → Click on close
2. 

Step 3 : Modifying parameter file odiparams.bat

1. Take backup of file odiparams.bat
2. Change Repository Connection Information
1. Let us say Master repository database name : ORCL
2. username : B109ODIMR
3. password : RRitec123
3. encode password of master repository
4. open CMD → and execute as shown
5. 
6. Change odi params.bat file repository section as shown
7. 
8. Change User credentials for agent startup program
9. Let us say ODI username : SUPERVISOR and password : RRitec123
10. As already we encoded RRItec123 password we can use same
11. Change odi params.bat file user credentials section as shown
12. 
13. Change work repository section as shown
14. 
15. save and close

Step 4 : Running and Testing  Agent

1. Go to location C:\OBIEE_HOME\Oracle_ODI1\oracledi\agent\bin
2. double click on agent.bat file
3. Observe cmd prompt
4. 
5. On go if agent is not running then debug using log file
6.