ODI - Oracle 12c database installation

 Steps:

Step1: Go to oracle site from google and download the file.

Step2: open it a d run setup in admin mode

Step3:oracle universal installer will start.

 

Step4: next

Step5: next

Step6: choose create and configure a databse.

Step7: choose Desktop class

Step8: use virtual account.

Step9: provide oracle db name . By default it is orcl

Step10: it checks all prerequisites

Step11: Install

Step12: it will install the db after certain period of time.

ODI - Import and Export techniques

Exporting and importing ODI objects means transferring objects between different repositories. When exporting an ODI object, an XML export file is created. ODI objects have dependencies that will also be exported in the XML export file. 

Exporting one ODI object:

Exporting one ODI object means export one single ODI object in order to transfer it from one repo to another.

Step1: select the object to be exported in the design tab and right click on the object and select export.

Step2: provide the export path, export name and "CHILD components export " this is very important checked it if you want to get the objects linked to the object to be exported. These objects are those visible under the exported object in the tree. And other options as required.

Export Multiple ODI objects:

We can export one or multiple objects at knce using the Export Multiple Objects action. This lets you export ODI objects to a zip file.

Step1: Select Export from the designer, Topology, Security or operator Navigator

Step2: In the export selection dialog, select Export Multiple Options

Step3: Provide directoy path, select Export as a zip file if needed or exported as .xml files directly in the directory. Drag and drop objects to be exported.

Smart Export and Import:

It is recommended to use the Smart Export and Import feature to avoid most of the common issues that are encountered during an export or import such as broken links or ID conflicts. The Smart Export and Import feature is a lightweight and consistent export and import mechanism providing several smart features.

The Smart Export automatically exports an object with all its object dependencies. It is particularly useful when you want to move a consistent lightweight set of objects from one repository to another and when you want to include only a set of modified objects, for example in a patching use case, because Oracle Data Integrator manages all object dependencies automatically while creating a consistent sub-set of the repository.

The Smart Import provides:

• Automatic and customizable object matching rules between the objects to import and the objects already present in the repository

• A set of actions that can be applied to the object to import when a matching object has been found in the repository

• Proactive issue detection and resolution that suggests a default working solution for every broken link or conflict detected during the Smart Import

Perform a smart export:

Step1: Select Export ... from Designer  Topology or Operator tab

Step2:  in the Export selection dialog, select Smart Export. Click ok

Note: This option is only available if you are connected with  a Work repo.

Step3: in the smart export dialog  specify the export parmeters:

Export directory and export file name

Export as Zip.

To Encrypt file

Encoding options: keep default.

Step4: In the Dependencies section, drag and drop the objects you want to add to the smart export from ODI into the selected Objects list on the left. 

The objects to export appears in a tree with all its related parent child objects that are required to support.

Step5: Modify the list of objects to export.

Step6: click export to start the export process

Importing an ODI object:

Step1: In the Navigator, select the object or object node under which you want to import the object. Right click the object and select import and select the type of object to import.

Step2: in the import dialog, select the import type and enter file import directory and select the files to import.

Perform a smart import:

Step1: select import... from the designer, topology or operator tab

Step2: In the import selection dialog, select Smart import. Click ok.

Step3: The smart import wizard opens. In 1st step, select the file and response file(optional).

Step4: In 2nd step, Import actions, verify the result of the matching process and fix eventual issues.

In the Object Match Details section, expand the nodes in the import object column to navigate to the objects available to import during the smart import.

In the Action column, select the action to peform on the object.

MERGE

OVERWRITR

CREATE COPY

REUSE

IGNORE

In the repository Object column, select the required repo objects.

Check the issue details table and to fix any issue, select Action to perform in the Action Column.

Possible actions to fix issues:

Ignore

Change

Do not change

Fix link

Step5: Review the import file name and eventual issues and click Finish.

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.