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.
No comments:
Post a Comment