What is Flow Control and Static Control and Recycle Errors in Oracle Data Integrator?

What is Flow Control and Static Control and Recycle Errors in Oracle Data Integrator?

What is Confirmed Dimension in Data Warehousing?

What is Confirmed Dimension in Data Warehousing?

What is Data Warehousing?

What is Data Warehousing?

Oracle Data Integrator Interview Questions and Answers part 3

INTERFACE OTHER OBJECTS:

-----------------------

1)JOINS

2)FILTERS

3)LOOKUP

4)DATASETS

1)JOINS:

Joins we are using for to join more than one table. ODI is supporting for all native sql joins.

1)Equijoin (inner join)

2)Left outer join

3)Right Outer join

4)Full outer join

5) cross join

examples:

--------

source tables:  EMP, DEPT

TARGET table :  EMP_DEPT

emp:  10,20,30,60

dept:  10,20,30,40,50

equi join(inner join or natual join):  10,20,30

leftouter join (EMP   DEPT):10,20,30,60

Right Outer join (EMP DEPT):  10,20,30,40,50

Full Outer join:  10,20,30,40,50,60

Cross Join:  Cross product:  EMP * DEPT

FILTERS:

--------

Filters we are using for to filter the data at source level or staging level. it will apply in where clause and we can apply

any filter condition.

I want to load only DEPTNO=30 to target table.

In filters we can all conditional operators and logical operators like ,  =,>,<,>=,<=,<> and OR ,AND, NOT

LOOKUP:

--------

Lookup we are using for to select data from another table while joining source tables. it will act as a left outer join or select clause sub query and optional based data selection we can go for lookup.

Lookup Advantage:  lookup will better selection compare to normal leftouter join.

Lookup we can use both ways: 

1)Left outer join

2)SQL Sub query expression.

1) LEFT OUTER JOIN Query:

select

    EMP.EMPNO       C1_EMPNO,

    EMP.ENAME       C2_ENAME,

    EMP.JOB    C3_JOB,

    EMP.MGR      C4_MGR,

    EMP.HIREDATE       C5_HIREDATE,

    EMP.SAL    C6_SAL,

    EMP.COMM    C7_COMM,

    EMP.DEPTNO     C8_DEPTNO,

    DEPT.DEPTNO    C9_D_DEPTNO,

    DEPT.DNAME     C10_D_DNAME,

    DEPT.LOC      C11_D_LOC

from  SCOTT.EMP    EMP LEFT OUTER JOIN SCOTT.DEPT    DEPT ON EMP.DEPTNO=DEPT.DEPTNO

where (1=1)

2) SQL Expression From clause.

select

    EMP.EMPNO       C1_EMPNO,

    EMP.ENAME       C2_ENAME,

    EMP.JOB    C3_JOB,

    EMP.MGR      C4_MGR,

    EMP.HIREDATE       C5_HIREDATE,

    EMP.SAL    C6_SAL,

    EMP.COMM    C7_COMM,

    EMP.DEPTNO     C8_DEPTNO,

    (Select DEPT.DEPTNO From SCOTT.DEPT  DEPT where EMP.DEPTNO=DEPT.DEPTNO)    C9_D_DEPTNO,

    (Select DEPT.DNAME From SCOTT.DEPT  DEPT where EMP.DEPTNO=DEPT.DEPTNO)    C10_D_DNAME,

    (Select DEPT.LOC From SCOTT.DEPT  DEPT where EMP.DEPTNO=DEPT.DEPTNO)     C11_D_LOC

from  SCOTT.EMP   EMP

where (1=1)

DATASETS:

----------

Datasets is nothing but our set operators.

1)UNION : union wont display duplicate rows

2)UNIONALL:  union all will display duplicates

3)INTERSACT: intersact will display common values

4)MINUS:  minus will display A-B values. it will remove common values and display only A values

EMP_BANG: 10,20,30,60

EMP_HYD: 10,20,30,40,50

EMP_BANG UNION EMP_HYD:  10,20,30,40,50,60

EMP_BANG UNION ALL EMP_HYD:  10,10,20,20,30,30,40,50,60

EMP_BANG INTERSACT EMP_HYD:  10,20,30

EMP_BANG MINUS   EMP_HYD:  60

PROJECT OBJECTS:

---------------

1) Markers

----------

Markers we are using for to show the status(progress) or priority for particular objects.

We can use this all objects like Projects, packages, interfaces, procedures,....

And also we can customise our own priorities and progresses

2) VARIABLES:

-------------

A variable is a object to store a value at any data type( number, varchar, date) and we can use at dynamically.

There are two types of variables.

1) Declare variable (Default we can assign a value and that values always same it wont change).

2) Refresh VARIABLES (Refresh variable will change a value at dynamically whenever we are running a program.

Note:  VARIABLES can store one value at a time. if we are passing second value it will overwrite existing value.

VARIABLES Usage:

-----------------

There are two types of variables in ODI.

1) PROJECT variable

2) Global VARIABLE

example:  VARIABLE NAME:   V_NAME

usage for Project variable:    #PROJECTNAME.V_NAME  or :PROJECTNAME.V_NAME

Usage for global variables:    #GLOBAL.V_NAME or :GLOBAL.V_NAME

This variables we can use in Procedures, interfaces, packages, inside variable we can another variable.

Global variables we can use Topology and Security as well.

In interface we can use in mapping like : or #PROJECTNAME.VARIABLENAME or #GLOBAL.VARIABLENAME.

This same variable we can 4 types in PACKAGE ***********

1)DECLARE VARIABLE

2)REFRESH VARIABLE

3)EVALUATE VARIABLE

4)SET VARIABLE

we will see this in packages examples.

3)SEQUENCES:

-------------

SEQUENCES we are using to generate sequences of values to inserting data into table.

SEQUENCES will maintain current value and increment by value.

Types of sequences:

------------------

1)Standard SEQUENCE

2)Specific SEQUENCE

3)Native SEQUENCE

1)Standard SEQUENCE

--------------------

This sequence we are creating at odi level and we will provide increment by value and it will start with zero and based on increment it will give us next value.

SEQUENCE Usage:

---------------

Project sequence:  : or #PROJECTNAME.SEQUENCENAME_NEXTVAL

Global Sequence:  : or #GLOBAL.SEQUENCENAME_NEXTVAL

SEQUENCE NAME : SEQ_EMPID

#NEW_PROJECT.SEQ_EMPID_NEXTVAL

Standard sequence it will start with 0 and based on our increment it will give us increment values.

Specific Sequence it will start with based on our own condition and it will give us increment value depending on condition returning a value.

for both standard and specific we will use same increment only. specific we will go for if we want to use some specific start with new value.

Native SEQUENCE:  This sequence available from 11g 11.1.1.5 version and above. this is we are directly integrating Database Sequences.

If we are using Native sequence odi increment will be disabled.

4) USER FUNCTIONS:

------------------

this user functions we are using for our customized parameter based requirements.

example:

if standard functions like SUM,MIN,AVG this functions having some parameter we can pass any parameter and we will values.

for the same if you have any your own requirements we can create new user function and reuse it in your project.

for user functions Syntax and Technology code is mandatory.

user functions parameters we have to give syntax like below.

FUNNAME($(P1),$(P2))

Example:

--------

right click on user functions:  NEw user function

Name:   FUNNAME

Syntax:  FUNNAME($(P1),$(P2))  ( This parameters are mandatory)

Goto=>Implementation tab=> Click on +=>Select Oracle technology=> Write below code in code area.

==================================

CASE

WHEN  $(P1) IS NULL THEN $(P2)

          ELSE

          $(P1)

END

we can use this user functions in mappings (interfaces).

FUNNAME(COL1,COL2)

PROCEDURES:

-----------

Procedures is step by step process we are using for this procedures all technology code execution.

we can write our own code at any technology and multiple steps.

we are using this procedures for writing our own technology code or scipts like,

SQL scritps, plsql scripts, java, jython, Linux, unix  and windows commands etc......

In Procedure there are two commands.

1)Command on source

2)Command on target

1) COMMAND ON SOURCE:

---------------------

command on source we are using for only selection (selecting data only).

2)COMMAND ON TARGET:

--------------------

Command on target we can use for all DML (insert, update and delete), DDL (create table,drop table, truncate and alter table),

select and other common programming language scripts. we can use data selected from command on source.

whatever we are selected columns from COMMAND ON SOURCE we can use in COMMAND ON TARGET with :COLUMNNAME or :ALIASNAME

Command On SOURCE:

-------------------

SELECT EMPNO,ENAME NAME FROM EMP

COMMAND ON TARGET:

------------------

INSERT INTO SAMPLE VALUES(:EMPNO,:NAME)

PACKAGE:

--------

Package is a workflow object. we can use for calling multiple interfaces, procedures, variables and packages in packages.

IN package we can use odi tools as well like,  ftp tools, mailing tool, local file copy, filemove,file delete....

SCENARIO:

---------

A scenario is a executable or compiled object for interface, procedure, package and variables.

after developing  our interfaces, procedures, packages and variables we will generate scenario. this scenario is not editable or readable

this scenario only executable and scheduling as well.

In production we will use only scenarios. we can't use direct interfaces , procedures or packages or variables. we have to use only scenarios for those

objects.

How to generate scenario?

------------------------

after finishing our development for interfaces , procedures , packages and variables we can generate scenario.

right click developed objects (interface,procedure,package and variable) =>Generate scenario.

These scenarios is direct executable and scheduled.

Can i edit a scenario?

No.  we can't edit a scenario and we can edit direct object (interface ...) and we can regenerate scenario or we can create new scenario.

Regenerate scenario means it will overwrite existing scenario

New scenario means it will create new scenario. for this old scenario and new scenario both will be available.

Can i execute a scenario without agent in Production?

------------------------------------------------------

No.  If we want to execute a scenario we need agent that it may be standard alone agent or j2ee agent.

Can i schedule a scenario  without agent?

-----------------------------------------

No. without agent we con't schedule scenario. if we want to schedule a scenario we need at-least one agent , that it may be standard alone or j2ee agent.

STANDALONE AGENT CONFIGURATION:

------------------------------

Step1:

------

Goto=> ODI Home directory=>OracleDI=>AGENT=>Bin folder

C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\bin

edit odiparams.batch file for windows,  odiparams.sh for unix or linux.

Step2:

------

goto => Repository connection information

change your master repository database details

JDBC URL:   localhost:1521:orcl

username:   mclass

password:  encrytped password (mclass)

step3:  Encrypt a password.

---------------------------

open CMD promt

change directory to agent/bin folder.

C:\Oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\bin

> type  encode password

enter

right click=>Mark=>select encrytped password=>righ click it will copy.

replace into odiparams.batch file repository connection information.

step4:

-----

providing SUPERVISOR encrytped password.

encode welcome

mark=>right click=>select=>right click=>Paste into odiparams.bathc file supervisor password.

step5:

------

Change work repository name:   WORKREP1

after these changes Save odiparams.bat file and close.

step6:

------

Goto=> ODI Studio=>Topology=>Physical architecture=>agens=>Right Click=>New agent

Name:  OracleDIAgent

Portno:  20910

and save your agent.

step7:

------

goto=>Agent/bin directory

click on agent.bat file.

it will start standalone agent.

or

we can run agent using commands

goto=>Agent/bin directory in your command line

type below command for starting agent

>agent -NAME="OracleDIAgent" -PORT=20910

step8:

------

creating logical agent for to access in designer.

LOAD PLANS:

-----------

load plan is extension for package and we can schedule all scenario parallel or sequential or hierarchical load.

in load plan we can use only scenarios, we con't use direct objects like interfaces, procedures, packages and variables.

Creating new loadplan.

SOLUTION:

---------

Solution we are using for to create version for entire projects and project dependency objects like models creating version controlling we will go for

solutions.

Create new solutions=>drag n drop your project into new solution area box and it will ask confirmation for creating version click yes to create new

version for all dependency objects.

Import/Export:

--------------

we can use this import/export for moving ODI objects information from one repository to another repository.

we we want to move DEVELOPMENT to TEST enviroment we can use smart import/export entire porject from development and import into test environment.

We we want to move TEST to PRODUCTION environments we can use smart export only scenarios and loadplans and we can import into product using

smart import feature.

after exporting save xml as backup.

open xml fie note below points

1) top of xml file repository version and odi version

2) bottom of xml file note  master repository id and work repository id .

Login into another enviroment like test or QA or PRODUCTION  where we want to import that repository version and odi version we need to verify

both versions should be same

and check development Work and Master repository ID' and Where we are moving that Work and Master repository ID's should not be same.

if Work and master repository id are same we need to change (renumber) ID for QA or TEST or PRODUCTION environments using renumber feature in Topology=>

Repositories=>Master Repository=>Right Click=>Renumber. same as for work repository we need to renumber in Topology.

Now we can import that xml file into new invironment.

IF odi repository versions are different then we need to migrate from old version to new version using UA repository migration utility tool.