ODI - Topology steps and model creations

 Step1: Create Repository connection

Step2: Go to Physical architecrure and choose 1 technology for example Oracle and new data server.

Step3: provide db connection details

Step4: provide jdbc details.

Step5: right click on the created data server and create new physical schema.

Step6:provide schema and work schema details. Context part we have to do once we created the logical schema.

Step7: Create context

Step8: create new logical schema

Step9: and map context to physical schema

Step10: Now go to design and tab create new model based on logical schema. And reverse enggineer to get all the datastores.

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.