Thursday 21 August 2014

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


  1. Drag and Drop sources EMP,DEPT,SALGRADE  and target EMP_DEPT_SAL_GRADE  tables to mapping workplace
  2. Delete the above SQ_DEPT,SQ_SALGRADE source qualifiers tables
  1. Drag and drop DEPTNO,DNAME,LOC  from DEPT table to SQ_EMP table
  2. Drag and drop GRADE,HISAL ,LOSAL from SALGRADE table to SQ_EMP table.
  3. Connect DNAME ,SAL and GRADE   from source qualifier to Target table


  1. Double click on Source Qualifier SQ_EMP → Click on Properties Tab
  2. Click on SQL Query down arrow mark
  3. 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
   


  1. 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
  1. 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                                                                            
  1. Click on OK → Click on OK Save Mapping
  2. Create a session and workflow
  3. Run the workflow

Thursday 7 August 2014

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
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
Step 3: Create interface

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

Monday 4 August 2014

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

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

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. Change odi params.bat file repository section as shown
  6. Change User credentials for agent startup program
  7. Let us say ODI username : SUPERVISOR and password : RRitec123
  8. As already we encoded RRItec123 password we can use same
  9. Change odi params.bat file user credentials section as shown
  10. Change work repository section as shown
  11. 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. On go if agent is not running then debug using log file