What are list of new components added in ODI 12c in Component Palette.?

What are list of new components added in ODI 12c in Component Palette.?

The following are the available component in the component palette:

1)     Expression

2)     Aggregate

3)     Distinct

4)     Set

5)     Filter

6)     Join

7)     Lookup

8)     Sort

9)     Split 

10) Pivot  (with new patch set  17053768)

11) Un-Pivot (with new patch set  17053768)

12) Table Function  (with new patch set  17053768)

13)   Sub-query Filter  (with new patch set  17053768)

Please find the below article for applying new patch for above new components.

http://oditraining.blogspot.in/2014/06/added-pivot-unpivot-table-functions-and_6.html

ODI SNP & Temporary Tables list ?

ODI SNP & Temporary Tables. 

1)     RKM will store the metadata into below following tables.

1.     Cleans up the SNP_REV_xx tables from previous executions using the OdiReverseResetTable tool.

2.     Retrieves sub models, datastores, attributes, unique keys, foreign keys, conditions from the metadata provider to SNP_REV_SUB_MODEL, SNP_REV_TABLE, SNP_REV_COL, SNP_REV_KEY, SNP_REV_KEY_COL, SNP_REV_JOIN, SNP_REV_JOIN_COL, SNP_REV_COND tables.

2)     LKM Temporary Tables.

a.     C$ table will be created by LKM for load the data from Source table to C$ staging table.

3)     CKM Temporary Tables.

a.     E$ Table for store error records while checking the Dataquality at Mapping level

b.    SNP_CHECK_TAB is a common error table to maintain the high level error details like. Schema name, table name, Error , Error type.

4)     IKM Temporary Tables.

a.     I$ table using for to load the data for integration before going to target table

5)     JKM Temporary Tables.

a.     J$ table for CDC concept to store the PK value into J$ table

b.    T$ Trigger for CDC concept is enabled at source table for DML trigger

c.     V$ View  will display the data for both Source and J$ table join query

d.    V$ DView will be joined both Source and J$table for filter condition with subscriber

What are the different types IKM Knowledge Module?

What are the different types IKM Knowledge Module?

1)     Append: Insert records from the flow into the target. It is possible to optionally delete all records from the target before the insert. Existing records are not updated.

      Using options like  TRUNCATE or DELETE_ALL =ture

2)     Control Append: Same as above, but in addition the data flow is checked in the process.

3)     Incremental Update: Same as above. In addition, it is possible to update existing records with data from the flow.

4)     Slowly Changing Dimension: Integrate data into a table using Type 2 slowly changing dimensions (SCD).

Oracle Data Integrator Project Components in ODI 12c

Oracle Data Integrator Project Components in ODI 12c

The following components are stored into a project. They appear in the in the Project accordion in the Designer Navigator, under the project's node.

Folder

Folders are components that help organizing the work into a project. Folders contain packages, mappings, procedures, and subfolders.

Packages

A package is a workflow, made up of a sequence of steps organized into an execution diagram. Packages assemble and reference other components from a project such as mappings, procedure or variable.

Mappings

A mapping is a reusable dataflow. It is a set of declarative rules that describes the loading of one or several target datastores from one or more source datastores. See 

Procedure

A Procedure is a reusable component that groups a sequence of operations that do not fit in the mapping concept.

Examples of procedures:

1)     Wait and unzip a file

2)     Send a batch of files via FTP

3)     Receive emails

4)     Purge a database

Variable

A variable's value is stored in Oracle Data Integrator. This value may change during the execution.

Sequence

A sequence is a variable automatically incremented when used. Between two uses the value is persistent.

User Functions

User functions allow you to define customized functions or "function aliases," for which you will define technology-dependent implementations. They are usable in mappings and procedures.

Knowledge Modules

Oracle Data Integrator uses Knowledge Modules at several points of a project design. A Knowledge Module is a code template related to a given technology that provides a specific function (loading data, reverse-engineering, journalizing).

Markers

A component of a project may be flagged in order to reflect a methodology or organization. Flags are defined using markers. These markers are organized into groups, and can be applied to most objects in a project.

Scenarios

When a package, mapping, procedure, or variable component has been fully developed, it is compiled in a scenario. A scenario is the execution unit for production. Scenarios can be scheduled for automated execution. See 

ODI Topology Coponent List of Objects

Topology Navigator

Topology Navigator is used to manage the data describing the information system's physical and logical architecture. Through Topology Navigator you can manage the topology of your information system, the technologies and their data types, the data servers linked to these technologies and the schemas they contain, the contexts, the language and the agents, as well as the repositories. The site, machine, and data server descriptions will enable Oracle Data Integrator to execute the same mappings in different environments.
This section contains these objects:

1.       Physical Architecture

2.       Contexts

3.       Logical Architecture

4.       Agents

5.       Languages

6.       Repositories

Physical Architecture

The physical architecture defines the different elements of the information system, as well as their characteristics taken into account by Oracle Data Integrator. Each type of database (Oracle, DB2, etc.), file format (XML, Flat File), or application software is represented in Oracle Data Integrator by a technology.

Contexts

Contexts bring together components of the physical architecture (the real Architecture) of the information system with components of the Oracle Data Integrator logical architecture (the Architecture on which the user works).
For example, contexts may correspond to different execution environments (Development, Test and Production) or different execution locations (Boston Site, New-York Site, and so forth.) where similar physical resource exist.

Logical Architecture

The logical architecture allows a user to identify as a single Logical Schema a group of similar physical schemas - that is containing data stores that are structurally identical - but located in different physical locations. Logical Schemas, like their physical counterpart, are attached to a technology.

Languages

Languages defines the languages and language elements available when editing expressions at design-time. Languages provided by default in Oracle Data Integrator do not require any user change.

Repositories (Master & Work Repositories)

The topology contains information about the Oracle Data Integrator repositories. Repository definition, configuration and installation is covered in the Installation and Upgrade Guide for Oracle Data Integrator.

Two source tables to one target table interface example

Create one table for combination of both emp and dept tables.



CREATE TABLE EMP_DEPT AS
  (SELECT E.EMPNO,
      E.ENAME,
      E.JOB,
      E.MGR,
      E.SAL,
      E.HIREDATE,
      E.COMM,
      E.DEPTNO,
      D.DNAME,
      D.LOC
    FROM SCOTT.EMP E,
      SCOTT.DEPT D
    WHERE E.DEPTNO=D.DEPTNO
    AND 1=2
  );

ODI Interface Mapping Quick Edit All The fields information

Parameter Value Description

 INS

• LKM: Not applicable (*)
• IKM: Only for mapping expressions marked with insertion
  
• CKM: Not applicable
  

UPD

• LKM: Not applicable (*)
• IKM: Only for mapping expressions marked with update
  
• CKM: Not applicable
  

TRG

• LKM: Not applicable (*)
• IKM: Only for mapping expressions executed on the target
  
• CKM: Not applicable
  

NULL

• LKM: Not applicable (*)
• IKM: All mapping expressions loading not nullable columns
  
• CKM: All target columns that do not accept null values
  

PK

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the primary key columns
  
• CKM: All the target columns that are part of the primary key
  

UK

• LKM: Not applicable (*)
• IKM: All the mapping expressions loading the update key column chosen for the current interface
  
• CKM: Not applicable
  

REW

• LKM: Not applicable (*)
• IKM: All the mapping expressions loading the columns with read only flag not selected
  
• CKM: All the target columns with read only flag not selected
  

UD1

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD1
  
• CKM: Not applicable
  

UD2

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD2
  
• CKM: Not applicable
  

UD3

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD3
  
• CKM: Not applicable
  

UD4

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD4
  
• CKM: Not applicable
  

UD5

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD5
  
• CKM: Not applicable
  

MAP

• LKM: Not applicable
• IKM: Not applicable
  
• CKM:
  

Flow control:   All columns of the target table loaded with expressions in the current interface


Static control:    All columns of the target table

SCD_SK	LKM, CKM, IKM: All columns marked SCD Behavior: Surrogate Key in the data model definition.
SCD_NK	LKM, CKM, IKM: All columns marked SCD Behavior: Natural Key in the data model definition.
SCD_UPD	LKM, CKM, IKM: All columns marked SCD Behavior: Overwrite on Change in the data model definition.
SCD_INS	LKM, CKM, IKM: All columns marked SCD Behavior: Add Row on Change in the data model definition.
SCD_FLAG	LKM, CKM, IKM: All columns marked SCD Behavior: Current Record Flag in the data model definition.
SCD_START	LKM, CKM, IKM: All columns marked SCD Behavior: Starting Timestamp in the data model definition.
SCD_END	LKM, CKM, IKM: All columns marked SCD Behavior: Ending Timestamp in the data model definition.
NEW	Actions: the column added to a table, the new version of the modified column of a table.
OLD	Actions: The column dropped from a table, the old version of the modified column of a table.
WS_INS	SKM: The column is flagged as allowing INSERT using Data Services.
WS_UPD	SKM: The column is flagged as allowing UDATE using Data Services.
WS_SEL	SKM: The column is flagged as allowing SELECT using Data Services.

Loading (LKM)

All active mapping expressions that are executed in the current source set, as well as all the columns from the current source set used in the mapping, filters and joins expressions executed in the staging area appear in this list. The list is sorted by POS, FILE_POS.
If there is a journalized datastore in the source of the interface, the three journalizing pseudo columns JRN_FLAG, JRN_DATE, and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Integration (IKM)

All current active mapping expressionsin the current interface appear in the list.
The list contains one element for each column that is loaded in the target table of the current interface. The list is sorted by POS, FILE_POS, except when the target table is temporary. In this case it is not sorted.
If there is a journalized datastore in the source of the interface, and it is located in the staging area, the three journalizing pseudo columns JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Check (CKM)

All the columns of the target table (with static or flow control) appear in this list.
To distinguish columns mapped in the current interface, you must use the MAP selector.

Define the Interface Flow

Define the Interface Flow


In the Flow tab, you define the loading and integration strategies for mapped data. Oracle Data Integrator automatically computes the flow depending on the configuration in the interface's diagram. It proposes default KMs (global and project KMs) for the data flow. The Flow tab enables you to view the data flow and select the KMs used to load and integrate data.

In the flow, the following items appear:

Source Sets: Source Datastores that are within the same dataset, located on the same physical data server and which are joined with Joins located on the Source are grouped in a single source set in the flow diagram. A source set represents a group of datastores that can be extracted at the same time.

DataSets: Datasets appear as yellow boxes in the Staging Area.

Staging Area: It appears as a box that includes the different datasets, the target (if located on the same data server), and possibly some of the sources (if located on the same data server).

Target: It appears as a separate box if it is located in a different schema from the staging area (If the Staging Area Different from Target option is selected).

You use the following KMs in the flow:

LKM: They define how data is moved. One LKM is selected for each Source Set for moving data from the sources to the staging area. It can be also selected to move data from the Staging Area - when different from the Target - to the Target, when a single technology IKM is selected for the Staging Area.

IKM: They define how data is integrated into the target. One IKM is typically selected on the Target. When the staging area is different from the target, the selected IKM can be a multi-technology IKM that moves and integrates data from the Staging Area into the Targ