ODI - Split component

A split is a selector component that divides into two or more flows based on specified conditions.

Use case: split source table data to 4 regions target tables.

Steps:

Drag and drop a split component from the component palette into logical diagram

Connect the split component to source and target tables.

In the property inspector tab, open the split conditions tab and provide the split condition.

Provide optimization context.

Provide LKM and IKM

ODI - Use of sequence object in a surrogate key

A Surrogate key in SQL server is a unique identifier for each row in the table. This type of key is either db generated using sequence or guid or generated via another application.

Use case: we will create a sequence in target db schema and use it in the mapping.

Step1: Create a sequence in Target shema:

Create sequence TRG.SEQ_JOBS

Minvalue 1

Maxvalue 999999999

Start with 1

Increment by 1;

Step2: once map done , slect the key field which to be unique and add the squence.nextval in the expression.

TRG.SEQ_JOBS.nextval

Use optimization context in the physical tab and Use IKM and LKM as needed.

ODI - Distinct component

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.

Steps:

To select distinct rows from a source datastore:

Drag and drop a Distinct component from the component palette into the logical diagram.

Connect the preceding component to the Distinct component by dragging a line from the preceding component to the Distinct component.

The Attribute Mapping Dialog will appear: select Create Attributes On Target to create all of the attributes in the Distinct component. Alternatively, you can manually map attributes as desired using the Attributes tab in the property inspector.

The distinct component will now filter all rows that have all projected attributes matching.

You can also change the distinct name in the general section of the logical tab.

Provide IKM as IKM SQL Control Append

Choose IKM flow control as False.

ODI - Agents

Agents:

• ODI agent orchestrates the execution of processes created in ODI. 
• At runtime, agents will be used to run Load Plans and Scenarios via an ODI schedule, command line call, web service call, or a third-party scheduler. 
• Agents are accessed via http/https requests, regardless of how they are called into action.
• These agents are multi threaded java programs that support load balancing and can be diatributed across the information system.

There are 3 types of agents are available:

JEE Agent

• Implemented as a deployment in Weblogic Server 12c, 
• the JEE Agent allows you to use the features of WLS, such as clustering for high availability and JDBC connection pooling.

Standalone Agent

The Standalone Agent is a lightweight Java application that is typically installed to run closest to where most of the transformations will occur. In most data warehouse setups, this is on the data warehouse server.

Standalone Colocated Agent

This type of agent is essentially a Standalone Agent that is managed via Weblogic Server. If you want to manage all of your agents via WLS, this is the way to go.

Comparatives of these 3 agents features:

Agents setups steps in ODI:

Step1: Go to the Topology and Physical architecture and right click on agents and add new agent and provide all the details like Name as OracleDIAgent1, Host, port etc.

Step2: Go to Logical Architechture and add add new agents on agents. Here you only need to mention the context and physical agents.

ODI - ETL vs ELT

 ODI 12c supports both the methods.

ETL: (Extract , Transform and load):

• Transform in separate ETL server
• Dedicated server
• Reduced performance
• High Costs

ELT(Extract, load and transform):

• Transform in existing RDBMS
• Leverage resources
• Increased performances
• Easier to manage abd lower costs

ODI - About Load plans

ODI is used to populate very large data warehouses and its common to have thousands of tables using hundreds of scenarios. The execution of these scenarios has to be organized in such that the data throughout from the sources to the target is the most efficient within the batch window. Load plans help the user to organize the execution of scenarios in a hieracrchy of sequential and parallel steps.

The leaf nodes of this hierarchy are scenarios, packages, mappings, variables, and procedures can be added to load plans for execution in the form of scenarios.

Load plans also support exception handling strategies in the event of a scenario ending in error.

Load plans can be started , stopped and restarted from a command line , from ODI studio. They can also be scheduled using the runtime agent's builtin scheduler or an external scheduler.

A load plan can be modified in prod environment and steps can be enabled and disabled accoeding to the prod needs.

Load plans appear in Design Navigator and in Operator in the load plans and scenarios accordion. The load plan runs are displayed in the load plan execution accordion in Operator Navigator.

When running or scheduling a load plan you provide the variable values, the contexts and logical agents used for this load plan execution.

Load plans can not be added to a load plan, however it is possible to add scenarios in form of a run scenarios step that starts another load plan using the OdiStartLoadPlan tool.

Different types of load plan steps:

A load plan is made of a several types of steps. Each step can contain several child steps. By default, a load plan contains an empty root serial step.

Serial step: a child steps is executed when the prevoius one is terminated.

Parallel step: child steps are started immediately in their order of priority.

Run scenario step: Launches the execution of a scenario.

Case step, when step, Else step: the combination of these steps allows conditional branching on the value of a variable.

Exception Step: defines a grouo of steps that is executed when an exccption is encountered in the associated step from the step hierarchy.

For more detailed information, click here

https://docs.oracle.com/middleware/1212/odi/ODIDG/loadplans.htm#ODIDG1349

ODI - Join and filter components

A join is a selector component that creates a join between multiple flows 

Implementation Steps:

Step1: Create physical and logical schemas in topology and context.

Step2: create models on logical schemas in design.

Step3: create a map and drag and drop jobs and job history tables as source in the default. Join them. And provide the join condition.

Step3: drag and drop target table and map them.

Join condition: job id from both source table.

Step4: add a filter on employee id on job history table 

Filter condition used as below.

Step5: Save and run and check operator tab and your target tables.