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: