New Features in Oracle Data Integrator 12c (12.1.2)

New Features in Oracle Data Integrator 12c (12.1.2)

Declarative Flow-Based User Interface

The new declarative flow-based user interface combines the simplicity and ease-of-use of the declarative approach with the flexibility and extensibility of configurable flows. Mappings (the successor of the Interface concept in Oracle Data Integrator 11g) connect sources to targets through a flow of components such as Join, Filter, Aggregate, Set, Split, and so on.

Reusable Mappings

Reusable Mappings can be used to encapsulate flow sections that can then be reused in multiple mappings. A reusable mapping can have input and output signatures to connect to an enclosing flow; it can also contain sources and targets that are encapsulated inside the reusable mapping.

Multiple Target Support

A mapping can now load multiple targets as part of a single flow. The order of target loading can be specified, and the Split component can be optionally used to route rows into different targets, based on one or several conditions.

Step-by-Step Debugger

Mappings, Packages, Procedures, and Scenarios can now be debugged in a step-by-step debugger. Users can manually traverse task execution within these objects and set breakpoints to interrupt execution at pre-defined locations. Values of variables can be introspected and changed during a debugging session, and data of underlying sources and targets can be queried, including the content of uncommitted transactions.

Runtime Performance Enhancements

The runtime execution has been improved to enhance performance. Various changes have been made to reduce overhead of session execution, including the introduction of blueprints, which are cached execution plans for sessions. Performance is improved by loading sources in parallel into the staging area. Parallelism of loads can be customized in the physical view of a map. Users also have the option to use unique names for temporary database objects, allowing parallel execution of the same mapping.

Oracle GoldenGate Integration Improvements

The integration of Oracle GoldenGate as a source for the Change Data Capture (CDC) framework has been improved in the following areas:

·         Oracle GoldenGate source and target systems are now configured as data servers in Topology. Extract and replicate processes are represented by physical and logical schemas. This representation in Topology allows separate configuration of multiple contexts, following the general context philosophy.

·         Most Oracle GoldenGate parameters can now be added to extract and replicate processes in the physical schema configuration. The UI provides support for selecting parameters from lists. This minimizes the need for the modification of Oracle GoldenGate parameter files after generation.

·         A single mapping can now be used for journalized CDC load and bulk load of a target. This is enabled by the Oracle GoldenGate JKM using the source model as opposed to the Oracle GoldenGate replication target, as well as configuration of journalizing in mapping as part of a deployment specification. Multiple deployment specifications can be used in a single mapping for journalized load and bulk load.

·         Oracle GoldenGate parameter files can now be automatically deployed and started to source and target Oracle GoldenGate instances through the JAgent technology.

Standalone Agent Management with WebLogic Management Framework

Oracle Data Integrator standalone agents are now managed through the WebLogic Management Framework. This has the following advantages:

·         UI-driven configuration through Configuration Wizard

·         Multiple configurations can be maintained in separate domains

·         Node Manager can be used to control and automatically restart agents

Integration with OPSS Enterprise Roles

Oracle Data Integrator can now use the authorization model in Oracle Platform Security Services (OPSS) to control access to resources. Enterprise roles can be mapped into Oracle Data Integrator roles to authorize enterprise users across different tools.

XML Improvements

The following XML Schema constructs are now supported:

·         list and union - List or union-based elements are mapped into VARCHAR columns.

·         substitutionGroup - Elements based on substitution groups create a table each for all types of the substitution group.

·         Mixed content - Elements with mixed content map into a VARCHAR column that contains text and markup content of the element.

·         Annotation - Content of XML schema annotations are stored in the table metadata.

Oracle Warehouse Builder Integration

Oracle Warehouse Builder (OWB) jobs can now be executed in Oracle Data Integrator through the OdiStartOwbJob tool. The OWB repository is configured as a data server in Topology. All the details of the OWB job execution are displayed as a session in the Operator tree. For more information about this feature, see "OdiStartOwbJob".

Unique Repository IDs

Master and Work Repositories now use unique IDs following the GUID convention. This avoids collisions during import of artifacts and allows for easier management and consolidation of multiple repositories in an organization.

ODI Lookup Leftouter join and SQL Expression?

ODI Lookup Types :
-------------------------
1) LookUp Left Outer Join
2) LookUp SQL Expression in Select Clause

 Lookup Type Left outer join will generate below query:
--------------------------------------------------------------------

select   
    EMPLOYEES.EMPLOYEE_ID       C1_EMPLOYEE_ID,
    EMPLOYEES.FIRST_NAME       C2_FIRST_NAME,
    EMPLOYEES.LAST_NAME       C3_LAST_NAME,
    EMPLOYEES.EMAIL       C4_EMAIL,
    EMPLOYEES.PHONE_NUMBER       C5_PHONE_NUMBER,
    EMPLOYEES.HIRE_DATE       C6_HIRE_DATE,
    EMPLOYEES.JOB_ID       C7_JOB_ID,
    EMPLOYEES.SALARY       C8_SALARY,
    EMPLOYEES.COMMISSION_PCT       C9_COMMISSION_PCT,
    DEPARTMENTS.MANAGER_ID       C10_MANAGER_ID,
    DEPARTMENTS.DEPARTMENT_ID       C11_DEPARTMENT_ID
from    HR.EMPLOYEES    EMPLOYEES LEFT OUTER JOIN HR.DEPARTMENTS    DEPARTMENTS ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID
where    (1=1)

 Lookup Type SQL expression in select clause will generate below query:
--------------------------------------------------------------------------------------------

select   
    EMPLOYEES.EMPLOYEE_ID       C1_EMPLOYEE_ID,
    EMPLOYEES.FIRST_NAME       C2_FIRST_NAME,
    EMPLOYEES.LAST_NAME       C3_LAST_NAME,
    EMPLOYEES.EMAIL       C4_EMAIL,
    EMPLOYEES.PHONE_NUMBER       C5_PHONE_NUMBER,
    EMPLOYEES.HIRE_DATE       C6_HIRE_DATE,
    EMPLOYEES.JOB_ID       C7_JOB_ID,
    EMPLOYEES.SALARY       C8_SALARY,
    EMPLOYEES.COMMISSION_PCT       C9_COMMISSION_PCT,
    (Select DEPARTMENTS.MANAGER_ID From HR.DEPARTMENTS  DEPARTMENTS where EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID)       C10_MANAGER_ID,
    (Select DEPARTMENTS.DEPARTMENT_ID From HR.DEPARTMENTS  DEPARTMENTS where EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID)       C11_DEPARTMENT_ID
from    HR.EMPLOYEES   EMPLOYEES
where    (1=1)

What Is a Scenario?

What Is a Scenario?

Remember that an interface, procedure, or package can be modified at any time. A scenario is created by generating code from such an object. It thus becomes frozen, but can still be executed on a number of different environments or contexts. Because a scenario is stable and ready to be run immediately, it is the preferred form for releasing objects developed in ODI.

Understanding Interface Quick Edit objects in Interface in ODI 11G?

INTERFACE QUICK EDIT Components List

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.

How to understand Knowledge Modules in ODI or How to Customise Knowledge Module in ODI?

Using Below all parameters we can understand the Knowledge Modules.

ODI Substitution Parameters L,W,D,P,S usage and meanings

Parameter pMode

L:

use the local object mask to build the complete path of the object.

 R:

Uses the object mask to build the complete path of the object.

use the remote object mask to build the complete path of the object.

Note: When using the remote object mask, getObjectName always resolved the object name using the default physical schema of the remote server.

A:

 Automatic: Defines automatically the adequate mask to use.

 Parameter Location

   W:

Returns the complete name of the object in the physical catalog and the "work" physical schema that corresponds to the specified tuple (context, logical schema)

    D:

Returns the complete name of the object in the physical catalog and the data physical schema that corresponds to the specified tuple (context, logical schema)

A:

Lets Oracle Data Integrator determine the default location of the object. This value is used if pLocation is not specified.

    P:

Qualify object for the partition provided in pPartitionName

    S:

Qualify object for the sub-partition provided in pPartitionName

Parameter  pProperty:

    ID: Datastore identifier.

    TARG_NAME: Full name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. If partitioning is used on the target datastore of an interface, this property automatically includes the partitioning clause in the datastore name.

    RES_NAME: Physical name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. This property does not include the partitioning information.

    COLL_NAME: Full name of the loading datastore.

    INT_NAME: Full name of the integration datastore.

    ERR_NAME: Full name of the error datastore.

    CHECK_NAME: Name of the error summary datastore.

    CT_NAME: Full name of the checked datastore.

    FK_PK_TABLE_NAME: Full name of the datastore referenced by a foreign key.

    JRN_NAME: Full name of the journalized datastore.

    JRN_VIEW: Full name of the view linked to the journalized datastore.

    JRN_DATA_VIEW: Full name of the data view linked to the journalized datastore.

    JRN_TRIGGER: Full name of the trigger linked to the journalized datastore.

    JRN_ITRIGGER: Full name of the Insert trigger linked to the journalized datastore.

    JRN _UTRIGGER: Full name of the Update trigger linked to the journalized datastore.

    JRN_DTRIGGER: Full name of the Delete trigger linked to the journalized datastore.

    SUBSCRIBER_TABLE: Full name of the datastore containing the subscribers list.

    CDC_SET_TABLE: Full name of the table containing list of CDC sets.

    CDC_TABLE_TABLE: Full name of the table containing the list of tables journalized through CDC sets.

    CDC_SUBS_TABLE: Full name of the table containing the list of subscribers to CDC sets.

    CDC_OBJECTS_TABLE: Full name of the table containing the journalizing parameters and objects.

    <flexfield_code>: Flexfield value for the current target table.

One Example For Substitution Methods

Procedure Details for Loading Data from a Remote SQL Database

Source Technology	Oracle
Source Logical Schema	SOURCE
Source Command	select ENAME V_ENAME,EMPNO V_EMPNO from   <%=odiRef.getObjectName("L","SCOTT","D")%>
Target Technology	Teradata
Target Logical Schema	TERADATA_DWH
Target Command	insert into PARTS (ENAME,EMPNO) values (:V_ENAME,:V_EMPNO)

Oracle Tools Send Mail Example

Procedure Details for Sending Multiple Emails

Source Technology	Oracle
Source Logical Schema	ORACLE
Source Command	Select FirstName FNAME, EMailaddress EMAIL From <%=odiRef.getObjectName("L","USERS","D")%>
Target Technology	ODITools
Target Logical Schema	None
Target Command	OdiSendMail -MAILHOST= tgrtechnologies.com  -FROM=admin@tgrtechnologies.com “-TO=#EMAIL” “-SUBJECT=Job Failure” Dear #FNAME, This is sample program in TGR Technologies, because session <%=snpRef.getSession(“SESS_NO”)%> has just started! -Admin

Delete Target Table

This task deletes the data from the target table. This command runs in a transaction and is not committed. It is executed if the DELETE_ALL Knowledge Module option is selected.

Command on Target

delete from <%=odiRef.getTable("L","INT_NAME","A")%>

Drop Work Table

This task drops the loading table. This command is executed if the DELETE_TEMPORARY_OBJECTS knowledge module option is selected. This option will allow to preserve the loading table for debugging.

Command on Target

drop table <%=snpRef.getTable("L", "COLL_NAME", "A")%>

Delete Errors from Controlled Table

This task removed from the controlled table (static control) or integration table (flow control) the rows detected as erroneous.

This task is always executed and has the Remove Errors option selected.

Command on Target (Oracle)

delete from       <%=odiRef.getTable("L", "CT_NAME", "A")%>  T

where    exists         (

                select   1

                from    <%=odiRef.getTable("L","ERR_NAME", "W")%> E

                where ODI_SESS_NO = <%=odiRef.getSession("SESS_NO")%>

                and T.rowid = E.ODI_ROW_ID

                )

The following Action Call Methods are available for Actions:

    addAKs(): Call the Add Alternate Key action for all alternate keys of the current table.

    dropAKs(): Call the Drop Alternate Key action for all alternate keys of the current table.

    addPK(): Call the Add Primary Key for the primary key of the current table.

    dropPK(): Call the Drop Primary Key for the primary key of the current table.

    createTable(): Call the Create Table action for the current table.

    dropTable(): Call the Drop Table action for the current table.

    addFKs(): Call the Add Foreign Key action for all the foreign keys of the current table.

    dropFKs(): Call the Drop Foreign Key action for all the foreign keys of the current table.

    enableFKs(): Call the Enable Foreign Key action for all the foreign keys of the current table.

    disableFKs(): Call the Disable Foreign Key action for all the foreign keys of the current table.

    addReferringFKs(): Call the Add Foreign Key action for all the foreign keys pointing to the current table.

    dropReferringFKs(): Call the Drop Foreign Key action for all the foreign keys pointing to the current table.

    enableReferringFKs(): Call the Enable Foreign Key action for all the foreign keys pointing to the current table.

    disableReferringFKs(): Call the Disable Foreign Key action for all the foreign keys pointing to the current table.

    addChecks(): Call the Add Check Constraint action for all check constraints of the current table.

    dropChecks(): Call the Drop Check Constraint action for all check constraints of the current table.

    addIndexes(): Call the Add Index action for all the indexes of the current table.

    dropIndexes(): Call the Drop Index action for all the indexes of the current table.

    modifyTableComment(): Call the Modify Table Comment for the current table.

    AddColumnsComment(): Call the Modify Column Comment for all the columns of the current table.

getObjectName(“L”, “MY_OBJECT”, “D”)

All KMs and procedures

The target datastore getTable(“L”, “TARG_NAME”, “A”) LKM, CKM, IKM, JKM

The “I$” datastore getTable(“L”, “INT_NAME”, “A”) LKM, IKM

The “C$” datastore getTable(“L”, “COLL_NAME”, “A”) LKM

The “E$” datastore getTable(“L”, “ERR_NAME”, “A”) LKM, CKM, IKM

The checked datastore getTable(“L”, “CT_NAME”, “A”) CKM

The datastore referenced  by a foreign key

getTable(“L”, “FK_PK_TABLE_NAME”, “A”) CKM

Is It Necessary To Regenerate A Scenario After Modifying The Refresh Statement Of An ODI Variable?

ODI Interview Questions and Answers:

Is It Necessary To Regenerate A Scenario After Modifying The Refresh Statement Of An ODI Variable?

Yes.

This operation must be carried out in the ODI Repository in which the complete set of ODI Objects are present (Package, Variables, User-defined Functions, Integration Interfaces, etc.).

The resulting Scenario must be exported and imported into an Execution Repository in the production environment.

How to Get Current Repository in using Substitution Method?

ODI Interview Questions and Answers:

How to Get Current Repository in using Substitution Method?

In master repository we can find all work repository names in SNP_REM_REP table.  Using getSession method we can get REP_ID and using below query we can get  repository name

select REP_NAME
from <%=odiRef.getObjectName("L", "SNP_REM_REP", "D")%>
where REP_ID = to_number(substr(to_char(<%=odiRef.getSession("SESS_NO")%>), length(to_char(<%=odiRef.getSession("SESS_NO")%>))-2, 3))

How to use ODI Variables in Substitution Methods Such As 'getObjectName' ?

We can use variables in Substitution methods  using object called  GetObjectName.


<%=odiRef.getObjectName( "L" , "#MYPROJECT.MYTABLE", ... "D" )%>