ODI Components

Datasets:
 Datasets provide a
logical container in which you can organize sources, and define joins and filters on
them through an entity-relationship mechanism, rather than the flow mechanism
used elsewhere in mappings. Datasets operate similarly to ODI 11g interfaces, and
if you import 11g interfaces into ODI 12c, ODI will automatically create datasets
based on your interface logic. Datasets act as selector components.

Lookup: - (selector)
A Lookup is a selector component  that returns data from a lookup flow being given a value from a driving flow. The
attributes of both flows are combined, similarly to a join component. A lookup can be
implemented in generated code either through a Left Outer Join or a nested Select
statement.
Lookups can be located in a dataset or directly in a mapping as a flow component.
When used in a dataset, a Lookup is connected to two datastores or reusable mappings
combining the data of the datastores using the selected join type

Reusable Mappings
Reusable mappings are modular, encapsulated flows of components which you
can save and re-use.

Filter:
Filters can be located in a dataset or directly in a mapping as a flow component.

Expression - selector
An expression is a selector component  that
inherits attributes from a preceding component in the flow and adds additional
reusable attributes.

Join: (selector)
A Join is a selector component that creates
a join between multiple flows. A Join can be located in a dataset or directly in a mapping as a flow component. A join
combines data from two or more components, datastores, datasets, or reusable
mappings.

Aggregate - projector
The aggregate component is a projector component  which groups and combines attributes using aggregate functions, such as
average, count, maximum, sum, and so on. ODI will automatically select attributes
without aggregation functions to be used as group-by attributes. You can override this
by using the Is Group By and Manual Group By Clause properties.

Sort:- projector
A Sort is a projector component that will
apply a sort order to the rows of the processed dataset, using the SQL ORDER BY
statement.

Distinct - projector
A distinct is a projector component  that
projects a subset of attributes in the flow. The values of each row have to be unique;
the behavior follows the rules of the SQL DISTINCT clause.

Spilt - (selector)
A Split is a selector component  that divides
a flow into two or more flows based on specified conditions. Split conditions are not
necessarily mutually exclusive: a source row is evaluated against all split conditions
and may be valid for multiple output flows.

Types of Variables in ODI

Variables:

A variable's value is stored in Oracle Data Integrator. This value may change during the execution.
■Declare Variable: When a variable is used in a Package  Oracle strongly recommends that you insert a Declare Variable step in the Package. This step explicitly declares the
variable in the Package.
■ Refresh Variable: This variable step refreshes the variable by running the query specified in the variable definition.
■ Set Variable: There are two functions for this step:
■ Assign sets the current value of a variable.
■ Increment increases or decreases a numeric value by the specified amount.
■ Evaluate Variable: This variable step type compares the value of the variable with a given value according to an operator. If the condition is met, then the evaluation
step is true, otherwise it is false. This step allows for branching in Packages

Example - XML to Table using ODI

How to load data from XML to Table using ODI 12c?

Source File:
<?xml version="1.0" encoding="UTF-8"?>
<employeeDetails>
<employeeInfo>
<age>28</age>
<name>Trinesh</name>
</employeeInfo>
<employeeInfo>
<age>27</age>
<name>Shanthi</name>
</employeeInfo>
<employeeInfo>
<age>29</age>
<name>Tejesh</name>
</employeeInfo>
<employeeInfo>
<age>25</age>
<name>Vasavi</name>
</employeeInfo>
<employeeInfo>
<age>19</age>
<name>Nishith</name>
</employeeInfo>
</employeeDetails>

Target Table Structure:
CREATE TABLE REPLICA.EMPINFO
(EMPID NUMBER(3),
NAME VARCHAR2(50),
AGE NUMBER(3));

Pre-requisites:
Knows how to do topology configuration for Oracle Technology
Knows how to create model for Oracle

Steps:

Topology Configuration

•  Expand Physical Architecture --> expand Technologies --> Right click on XML Technology and create new Dataserver
• In the definition provide the name of Dataserver (for e.g., XML_DS)
• Go to JDBC and Select as mention below

• For JDBC URL: jdbc:snps:xml?f=<filename>[&s=<schema>&<property>=<value>...]

For example: jdbc:snps:xml?f=G:\ODI_FILES\ODI_XML\employee.xml&re=employeeDetails

Property	Description
f	XML file name. Use slash "/" in the path name instead of back slash "\". It is possible to use an HTTP, FTP or File URL to locate the file. Files located by URL are read-only.
d	This file may be a DTD or XSD file. It is possible to use an HTTP, FTP or File URL to locate the file. Files located by URL are read-only. Note that when no DTD or XSD file is present, the relational schema is built using only the XML file content. It is not recommended to reverse-engineer the data model from such a structure as one XML file instance may not contain all the possible elements described in the DTD or XSD, and data model may be incomplete
re	Name of the element to take as the root table of the schema. This value is case sensitive. This property can be used for reverse-engineering for example a specific message definition from a WSDL file, or when several possible root elements exist in a XSD file.
ro	If true, the XML file is opened in read only mode
S	Name of the relational schema where the XML file will be loaded. If this property is missing, a schema named after the five first letters of the XML file name will automatically be created. This schema will be selected when creating the physical schema under the XML data server.
cs	Load the XML file in case sensitive or insensitive mode. For case insensitive mode, all element names in the DTD file should be distinct (For example: Abc and abc in the same file will result in name collisions)

• Save it and test the connection. You will get successful connection prompt
• Right click on Data server and create Physical Schema (Provide the first five characters of file name if you didn't provide property of s in JDBC URL)

Logical Architecture

• Expand Technologies -- Expand XML -- Right click and new logical Schema (XML_LS)

Context

• Link Logical Schema to Physical Schema

Designer:

Model 

• Create Model for XML technology as mention below

• Right click on Model and say click on reverse Engineering
• It will create two datastores as mention below

The standard reverse-engineering process will automatically reverse-engineer the table from the relational schema generated by the XML driver. Note that these tables automatically include:

Primary keys (PK columns) to preserve parent-child elements relationships

Foreign keys (FK columns) to preserve parent-child elements relationships

Order identifier (ORDER columns) to preserve the order of elements in the XML file

Project:

• Import Knowledge modules - LKM SQL to SQL  and IKM SQL Control Append
• Create mapping -- Drag drop the XML datastore (EMPLOYEEINFO) and Table datastore from Oracle Model (EMPINFO) and auto map it.
• For Employee Id map to the EMPLOYEEINFOORDER
• Go to physical tab and select above mention KM's
• Execute the Mapping

Output:

How specify order of loading target tables in ODI 12c?

Specifying Target Order load

Scenario:

Target Order is useful when a mapping has multiple targets and there are foreign key (FK) relationships between the targets. 

For example, suppose a mapping has two targets called EMPLOYEES and DEPARTMENTS, and EMPLOYEES.DEPTID is a FK to DEPARTMENTS.DEPTID. If the source data contains information about the employee and the department, the information about the department (DEPARTMENTS) must be loaded first before any rows about the employee can be loaded (EMPLOYEES). To ensure this happens, the target load order should be set to DEPARTMENTS, EMPLOYEES

Approach:

Mappings with multiple targets do not, by default, follow a defined order of loading data to targets. You can define a partial or complete order by using the Target Load Order property. Targets which you do not explicitly assign an order will be loaded in an arbitrary order by ODI.

Note:

Target load order also applies to reusable mappings. If a reusable mapping contains a source or a target datastore, you can include the reusable mapping component in the target load order property of the parent mapping.

The order of processing multiple targets can be set in the Target Load Order property of the mapping:

1. Click the background in the logical diagram to deselect objects in the mapping. The property inspector displays the properties for the mapping.

2. In the property inspector, enter a target load order in the Target Load Order field:

Select or hover over the Target Load Order field and click the gear icon to open the Target Load Order Dialog. This dialog displays all available datastores (and reusable mappings containing datastores) that can be targets, allowing you to move one or more to the Ordered Targets field. In the Ordered Targets field, use the icons on the right to rearrange the order of processing.

TableFunction Component in ODI 12c (12.1.3 onward)

Table Function Component (Available from 12.1.3 version onward)

Scripts:

CREATE TABLE DEV.DIM_DATE ( DATE_KEY NUMBER, DATE_TIME_START DATE, DAY_OF_WEEK_NUMBER NUMBER(3), DAY_OF_WEEK_DESC  VARCHAR2(25), DAY_OF_WEEK_SDESC VARCHAR2(10), WEEK_IN_MONTH_NUMBER NUMBER(4), WEEK_IN_YEAR_NUMBER NUMBER(4) , DAY_OF_MONTH_NUMBER NUMBER(4), MONTH_VALUE NUMBER(4), MONTH_DESC VARCHAR2(25) , MONTH_SDESC VARCHAR2 (25), MONTH_START_DATE DATE, MONTH_END_DATE DATE , DAYS_IN_MONTH NUMBER(5), DAY_OF_QUARTER_NUMBER NUMBER(5), QUARTER_VALUE NUMBER(5), QUARTER_DESC VARCHAR2(25), QUARTER_START_DATE DATE, QUARTER_END_DATE DATE, DAYS_IN_QUARTER NUMBER(5), DAY_OF_YEAR_NUMBER NUMBER(5), YEAR_VALUE NUMBER(5)); / DROP TYPE DEV.T_TF_TAB; DROP TYPE DEV.T_TF_ROW; / CREATE TYPE DEV.T_TF_ROW AS OBJECT (   ID           NUMBER,   CALLDATE DATE ); / CREATE TYPE DEV.T_TF_TAB IS TABLE OF T_TF_ROW; / CREATE OR REPLACE FUNCTION DEV.GET_TAB_TF(STARTDATE VARCHAR2, ENDDATE VARCHAR2) RETURN T_TF_TAB PIPELINED AS NO_OF_DAYS NUMBER; BEGIN   select to_date(ENDDATE,'DD-MON-YYYY')-TO_DATE(STARTDATE,'DD-MON-YYYY') INTO NO_OF_DAYS FROM DUAL;   FOR i IN 0 .. NO_OF_DAYS   LOOP     PIPE ROW(T_TF_ROW(i,TO_DATE(STARTDATE,'DD-MON-YYYY')+i));    END LOOP;   RETURN; END; /

Pre-requisites

·         Need to have knowledge on Oracle Table Function

·         Need to have knowledge on creating function in Oracle DB

Approach:

1.      Create Mapping and Drag and drop the Datastore from Model (i.e., DIM_DATE)

2.      Drag and drop the Table function from components palette into logic editor

3.      Connect Output connector of Table Function to input connector of Data store (i.e., DIM_DATE) as follows

4.      Select table function component and go to property inspector and expand connector points and edit as mention below

5.      Select DIM_DATE datastore and go to property inspector and map each field as follows

TO_NUMBER(TO_CHAR(TABLEFUNCTION.CALLDATE, 'YYYYMMDD')) AS DATE_KEY, TABLEFUNCTION.CALLDATE AS DATE_TIME_START, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'D'))  AS DAY_OF_WEEK_NUMBER, TO_CHAR (TABLEFUNCTION.CALLDATE, 'DAY')  AS DAY_OF_WEEK_DESC, TO_CHAR (TABLEFUNCTION.CALLDATE, 'DY') AS DAY_OF_WEEK_SDESC, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'W')) AS WEEK_IN_MONTH_NUMBER, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'WW')) AS WEEK_IN_YEAR_NUMBER, TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'DD'))  AS DAY_OF_MONTH_NUMBER, TO_CHAR (TABLEFUNCTION.CALLDATE, 'MM') AS MONTH_VALUE, TO_CHAR (TABLEFUNCTION.CALLDATE, 'MONTH')AS MONTH_DESC, TO_CHAR (TABLEFUNCTION.CALLDATE, 'MON') AS MONTH_SDESC, TRUNC (TABLEFUNCTION.CALLDATE, 'MM')  AS MONTH_START_DATE, LAST_DAY (TABLEFUNCTION.CALLDATE) AS MONTH_END_DATE, TO_NUMBER ( TO_CHAR( LAST_DAY (TABLEFUNCTION.CALLDATE), 'DD')) AS DAYS_IN_MONTH, TRUNC(TABLEFUNCTION.CALLDATE) – TRUNC(TABLEFUNCTION.CALLDATE, 'Q') + 1 AS DAY_OF_QUARTER_NUMBER, TO_CHAR(TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_VALUE, 'Q' || TO_CHAR (TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_DESC, TRUNC (TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_START_DATE, ADD_MONTHS(TRUNC (TABLEFUNCTION.CALLDATE, 'Q'), 3) – 1 AS QUARTER_END_DATE, ADD_MONTHS(TRUNC (TABLEFUNCTION.CALLDATE, 'Q'), 3) – TRUNC (TABLEFUNCTION.CALLDATE, 'Q') AS DAYS_IN_QUARTER, TO_NUMBER(TO_CHAR (TABLEFUNCTION.CALLDATE, 'DDD')) AS DAY_OF_YEAR_NUMBER, TO_CHAR(TABLEFUNCTION.CALLDATE, 'YYYY') AS YEAR_VALUE

Output:

Customization of Knowledge Module

Customization of Knowledge Module

Scenario:

Client want to know which all records are inserting and which all records are loaded into target table. For this reason, we customized knowledge module and adding audit table to it

Approach:

1.      Import IKM Oracle Incremental Update knowledge module

2.      Add two steps

a.      Drop audit table

drop table <%=odiRef.getTable( "L" , "TARG_NAME" , "W" )%>||'_AUDIT'

b.      Create audit table and populate Unique Key along with Flag indicator

CREATE TABLE <%=odiRef.getTable( "L" , "TARG_NAME" , "W" )%>|| '_AUDIT'

AS

SELECT <%=odiRef.getColList( "" , "[COL_NAME] , ",nt" , "" , "UK" )%> , IND_UPDATE

FROM <%=odiRef.getTable( "L" , "INT_NAME" , "W" )%>

3.      Create option to enable and disabling the Audit table information

Output:

SCD Type 3

Type 3 - Adding a new column.

In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current' column and the old one into 'previous' column.

Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed technique. 

Pre-requisites:

EMPLOYEES table

EMP table with PREV_SALARY as extra column

Incremental Knowledge Module (IKM Oracle Incremental KM)

Approach:

1.    Reverse Engineer both Employees datastore as well as EMP datastore into model

2.    Import IKM Oracle Incremental KM

3.    Create Mapping and Drag and Drop EMP and EMPLOYEES datastores into logical editor

4.    Connect EMPLOYEES datastore to EMP

5.    Select PREV_SAL attribute in EMP datastore and go to property inspector, disable update checkbox and enable UD2 checkbox as below

6.    Select SAL attribute in EMP datastore and go to property inspector, enable UD1 checkbox as below

7.    Customize the KM whichever we imported earlier as below. (i.e., by adding one more step to it)

update <%=odiRef.getTable("L" , "TARG_NAME" , "D" )%> T

                      set <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD2)" )%> = 

                        <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>

                      where <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> in 

                                      (SELECT <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> 

                              FROM <%=odiRef.getTable("L" , "TARG_NAME" , "D" )%> T,

              <%=odiRef.getTable("L" , "INT_NAME" , "W" )%> S

                                   WHERE <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> =

                  <%=odiRef.getColList( "" , "S.[COL_NAME]" , ",nt" , "" ,"UK" )%>

                               AND <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%> 

                           <> <%=odiRef.getColList( "" , "S.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>)

8.    Execute the mapping

9.    Update the employees table and re-execute the mapping

UPDATE REPLICA.EMPLOYEES

SET SALARY = 22200

WHERE EMPLOYEE_ID = 100;

COMMIT;

10. Output as below

Detection Strategies

Detection Strategies

Detection Strategies:

A.      NOT EXISTS

B.      MINUS

C.      POST_FLOW

D.      NONE

A.      NOT EXISTS

a.       Load data from source table to C$

b.       Compare to C$ vs Target table with all fields and inserts into I$ if it is not matching with IND_UPDATE flag as ‘I’

c.       Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d.       Whose flags are ‘U’ those records are updated to target table

e.       Whose flags are ‘I’ those records are inserted to target table

Sample code for step b. as follows:

/* DETECTION_STRATEGY = NOT_EXISTS */ insert into                                                                                                    ODI_TEMP.I$_DEPT (                                                                                                                         DEPARTMENT_ID,                                                                                                                         MANAGER_ID,                                                                                                                         DEPARTMENT_NAME,                                                                                                                         IND_UPDATE ) select DEPARTMENT_ID,                                                                                                                         MANAGER_ID,                                                                                                                         DEPARTMENT_NAME,                                                                                                                         IND_UPDATE  from ( select                                                                                                                                                                                                                                       DEPARTMENTS.DEPARTMENT_ID DEPARTMENT_ID,                                                                                                                         DEPARTMENTS.MANAGER_ID MANAGER_ID,                                                                                                                         DEPARTMENTS.DEPARTMENT_NAME DEPARTMENT_NAME,                                                                                                                         'I' IND_UPDATE from                                                                                                              REPLICA.DEPARTMENTS DEPARTMENTS where                                                                                                           (1=1) ) S where NOT EXISTS                                                                                                                         ( select 1 from REPLICA.DEPT T                                                                                                                         where          T.DEPARTMENT_ID         =  S.DEPARTMENT_ID                                                                                                                                  and (( T.MANAGER_ID = S.MANAGER_ID) or (T.MANAGER_ID IS NULL and S.MANAGER_ID IS NULL)) and                                                                                                                                 ((T.DEPARTMENT_NAME = S.DEPARTMENT_NAME) or (T.DEPARTMENT_NAME IS NULL and S.DEPARTMENT_NAME IS NULL))         )

  

B.      MINUS

a.       Load data from source table to C$

b.       Compare to C$ vs Target table with minus operator and inserts into I$ if it is not matching with IND_UPDATE flag as ‘I’

c.       Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d.       Whose flags are ‘U’ those records are updated to target table

e.       Whose flags are ‘I’ those records are inserted to target table

Sample code for step b. as follows:

/* DETECTION_STRATEGY = MINUS */ insert into                                                                                                    ODI_TEMP.I$_DEPT (                                                                                                                         DEPARTMENT_ID,                                                                                                                         MANAGER_ID,                                                                                                                         DEPARTMENT_NAME,                                                                                                                         IND_UPDATE ) select                                                                                                                                                                                                                                       DEPARTMENTS.DEPARTMENT_ID,                                                                                                                         DEPARTMENTS.MANAGER_ID,                                                                                                                         DEPARTMENTS.DEPARTMENT_NAME,                                                                                                                         'I' IND_UPDATE from                                                                                                              REPLICA.DEPARTMENTS DEPARTMENTS where                                                                                                           (1=1) minus select                                                                                                                         DEPARTMENT_ID,                                                                                                                         MANAGER_ID,                                                                                                                         DEPARTMENT_NAME,                                                                                                                         'I'    IND_UPDATE from                                                                                                              REPLICA.DEPT

C.      POST_FLOW

a.       Load data from source table to C$

b.       Copy from C$ to I$ with IND_UPDATE flag as ‘I’

c.       Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d.       Identify not updated records by comparing fields with target and update the IND_UPDATE flags as ‘N’

e.       Whose flags are ‘U’ those records are updated to target table

f.        Whose flags are ‘I’ those records are inserted to target table

Sample code for step d. as follows:

/* DETECTION_STRATEGY = POST_FLOW */ update                                                                                                          ODI_TEMP.I$_DEPT S set                                                                                                                  IND_UPDATE = 'N' where                                                                                                           exists (                                                                                                                         select            'X'                                                                                                                         from              REPLICA.DEPT    T                                                                                                                         where          T.DEPARTMENT_ID         = S.DEPARTMENT_ID                                                                                                                                 and        ((T.MANAGER_ID = S.MANAGER_ID) or (T.MANAGER_ID IS NULL and S.MANAGER_ID IS NULL)) and                                                                                                                 ((T.DEPARTMENT_NAME = S.DEPARTMENT_NAME) or (T.DEPARTMENT_NAME IS NULL and S.DEPARTMENT_NAME IS NULL))                                                                                                                         )

D.      NONE

a.       Load data from source table to C$

b.       Copy from C$ to I$ with IND_UPDATE flag as ‘I’

c.       Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d.       Whose flags are ‘U’ those records are updated to target table

e.       Whose flags are ‘I’ those records are inserted to target table

[Note: It will update all the records whether it got changed or not changed]