ODI - Reusable mappings

Reusable mappings allow us to encapsulate a multi step integration or a portion of an integration into a single component which we can save and use as any other components in our mappings. Reusable mappings are a convenient way to avoid the labor of creating a similar or identical subroutine of data manipulation that we will use many times in our mappings.

Steps:

Step1: Projects - Reusable mappings - New reusable mapping. - ok.

Step2: Create new mapping and drag and drop the reusable component and add source to resuable component and outputs to target.

ODI - Expression component

An expression is a selector component that inherits attributes from a preceding component in the flow and adds additional resuabale attributes. Attributes can be renamed and transformed from source attributes using SQL expressions.

The best use of an expression component is in cases where intermediate transformations are used multiple times such as when pre-calculating fields that are used in multiple targets.

Use case: 

1. First use a filter on Regions table(where Region_id ='4' )
2.  and then join them between countries and Regions based on join condition(countries.region_id =regions.region_id)
3.  and then connect the join with an expression component which inherits all fields from countries and regions tables. 
4. Now we will use a subquery filter component with expression and locations tables based on conditon  - Expression.country_id =Locations.country_id. 
5. And then use the subquery result to target table Locations_EXP.

ODI - Subquery filter component

A subquery filter component is a projector component that lets us to filter rows based on the results of a subquery. The conditions that we can use to filter rows are Exists, Not Exists, IN and Not In.

For example, the EMP datastore contains employee data and the dept datastore contains dept data. We can use a subquery to fetch a set of records from the Dept datastore and then filter rows from the EMP datastore by using one of the subquery conditions.

Implementation steps:

Drag and drop 2 source datastores and drag and drop subquery filter component and then connect them. Here countries table acts as a driver table and Regions table acts as a subquery filter input table.

Provide subquery filter condition and role as Exist or in or not in etc.

Drag and drop target datastore and connect it with the subquery filter.

Provide LKM and IKM.

We can also use data set as a driver input.

Note: when you use a dataset as driver and if we have same Regions tables name in dataset and outside of it, then it will fail. So change the one of them to have unique names in the logical diagram.

ODI - Staging area and different temporary tables

ODI knowledge Modules often use temporary objects to stage temporary data for jobs optimization. These temporary objects are always stored in a particular schema or directory called work schema or staging area. These work schemas should be kept separate from the schema containing the application data.

ODI will create temprary tables during the data trasformation, loading and integration.

Different temp tables:

E$ table: This is generated by Check control knowledge module(CKM) if flow control is enabled.

C$ table: The LKM creates the C$ temp tables in the staging area. This table will hold the records loaded from the source server.

I$ table: Created by IKM. After LKM loads the data onto the C$ , the IKM will load these into I$ table.

J$ table: Permanent Journal tables created when the target is to be used as a source with CHANGE DATA CAPTURE enabled by using JKM.

ODI - Static vs Flow control

 Flow control:

• Flow control checks or validates the data in the incoming floe before it gets integrated into a target table.
• Check control knowledge module(CKM) will create E$ and SNP_CHECK_TAB tables foe data quality check.
• It will validate data in I$ table before inserting data into target table. If it has any errors then it will delete from I$ and insert into E$ and common erroe message and interface name into SNP_CHECK_TAB.

Static control:

• Static control checks or validates constraints on the target data store adter integration.
• CKM will validate data on Target table and if any error is detected then it will be inserted to E$ and SNP_CHECK_TAB. But incorrext data entry will not be deleted as in flow control.

ODI - Sort Component

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.

Create Steps:

Step1: Drag and drop a sort component from the comnent pallete and connect with source and target.

Step2: select the sort component and select condition tab in the property inspector. Multiple fields can be listed separated by commas and ASC and DESC can be appended after each field to define if thw sort will be ascending or descending.

By default, Its ascending order.

Go to physical tab and provide LKM( SQL TO SQL) and IKM(ORACLE APPEND) and flow control as False and save and run.

ODI - Join component

Join:

A join is a slector component that create a join between multiple flows. A join can be located in a dataset or directly in a mapping as a flow component. 

By default, the join condition will be an equi-join between the two atrributes. But we can also do left outer join, right right join , cross or natural joins etc.

Difference between lookup and join:

• Lookup handles less volume of data where as join handles huge data.
• Lookup have 1 input link where as join can have multiple source data links.
• The lookup component gives us some additional functionality compared to a join. We can supply a default value for a key that does not exist in our lookup table. Using a join we might have to do an outer join followed by an expression operator providing a case to replace the NULL from the unmatched key.

Usecase: we will join two tables jobs and job_history based on join condition where jobs.job_id =job_history.job_id and then store them ina  target table.

Implementation steps: 

Step1: I hope you have created model db stores as tables.

Step2: Drag and drop source tables to map and drag and drop join component from component pallete.

Step3: provide the join condition.

Step4: join the target table

Step5: Go to physical tab and provide LKM and IKM and flow control as False.

Save and run it.

ODI - sets conponent - Union operarion

A set component is a projector component that combines multiple input flows into one using set operation such as Union, Intersect, except, Minus and others.

Use case: We will same 2 Location tables as source tables and then use a filter on them and then union and store to Target table.

Steps:

Step1: Drag and drop 2 same locations table and 2 filters and define the filter conditions.

Step2: Drag and drop a set component from the component palette into the logical diagram.

Step3: Drag and drop Target table and connect the set to the target. In the properry inspector, select the Operators tab and select cells in the operator column to choose the appropriate set operators. UNION is choosen by default.

ODI - Aggregate component

• 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.

To create an aggregate component:

• Drag and drop the aggregate component from the component palette into the logical diagram.

• Define the attributes of the aggregate if the attributes will be different from the source components. To do this, select the Attributes tab in the property inspector, and click the green plus icon to add attributes. Enter new attribute names in the Target column and assign them appropriate values. If attributes in the aggregate component will be the same as those in a source component, use attribute matching.
• Create a connection from a source component by dragging a line from the connector port of the source to the connector port of the aggregate component.
• The Attribute Matching dialog will be shown. If attributes in the aggregate component will be the same as those in a source component, check the Create Attributes on Target box. If necessary, map all attributes from source to target that were not mapped though attribute matching, and create transformation expressions as necessary .
• In the property inspector, the attributes are listed in a table on the Attributes tab. Specify aggregation functions for each attribute as needed. By default all attributes not mapped using aggregation functions (such as sum, count, avg, max, min, and so on) will be used as Group By.

You can modify an aggregation expression by clicking the attribute. For example, if you want to calculate average salary per department, you might have two attributes: the first attribute called AVG_SAL, which you give the expression AVG(EMP.SAL), while the second attribute called DEPTNO has no expression. If Is Group By is set to Auto, DEPTNO will be automatically included in the GROUP BY clause of the generated code.

You can override this default by changing the property Is Group By on a given attribute from Auto to Yes or No, by double-clicking on the table cell and selecting the desired option from the drop down list.

You can set a different GROUP BY clause other than the default for the entire aggregate component. Select the General tab in the property inspector, and then set a Manual Group by Clause. For example, set the Manual Group by Clause to YEAR(customer.birthdate) to group by birthday year.

Optionally, add a HAVING clause by setting the HAVING property of the aggregate component: for example, SUM(order.amount) > 1000.

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