Oracle Data Integrator Interview Questions and Answerns part 1

ODI Notes – ORACLE DATA INTEGRATION 11G/12C

=====================================================

SECURITY: (Profiles, Users, Objects) :

1.       This component we are using for to maintain users and user privileges.

2.       In security, We can create new users and we can assign privileges through profiles or objects.

Objects:  List of available objects in ODI that privileges will be available in SECURITY

=> Object tab.

USERS:

Supervisors  

Profiles:

A profile is a set of objects privileges. One profile may contain more than one object and we can create new profile and we can assign objects to the profile.

Or

we can use existing profiles.

 Profile Types:

There are two types of profiles.

1) Generic Profile

2)Non-Generic Profile

1) Generic Profile:

If any profile listed objects have selected Generic Privileges option that is called generic profile.

This generic profiles, oracle providing individual components wise.

1)SECURITY ADMIN -  Security component

2)TOPOLOGY ADMIN - Topology component

3)OPERATOR -  Operator component

4)DESIGNER - Designer component except MODELS & Solutions

5)METADATA ADMIN - MODELS in Designer component

6) VERSION ADMIN - Solutions in Designer component

7)REPOSITORY EXPLORER - ODI Console

8) CONNECT - This is for read only (view) privileges for Topology, Designer & Operator components.

2)Non-Generic Profile:

This Non-Generic profile having objects but does'nt have privileges. We will use this NG  profiles for customized security. This NG profiles only available for DESIGNER Component including ODI Console.

1)NG-Designer

2)NG_MetadataAdmin

3)NG- Version Admin

4)NG-Repository Explorer.

USERS:

Here one Default Installation Admin user SUPERVISOR will be there and we can create new user and we can make that user as a supervisor or using profiles

we can assign privileges.

=====================================================================

TOPOLOGY:

In Topology we maintain Repositories like Master repository and  work repository.

We can create new work repositories  but we can't create master repository here bcz we can maintain only one master repository for one project. Withour master repository we can loginto ODI Studio.

Available objects in Topology:

1) Physical Architecture

   1) Technologies

   2) Agents

In  Physical Architecture objects will be directly connecting physical sources and targets databases or applications or flat files.

1)Technologies:

In physical architecture we can see available or supporting technologies in ODI like Databases, applications, flat files, OS, middleware ...

In technologies we will create Data Server for each technology and for that data server we will create Physical Schema.

Data Server:

Data Server is nothing but for particular technology Driver Details with Credentials.

for example If oracle means

JDBC Driver details and Login Credentials (User name and Password).

We need to create dataserver for our all sources and target databases or files.

Physical Schema:

Physical Schema is mainly for one part of your database. We can call this as a database user or Schema.

Here there are two types of schemas

1)Data Schema : Data Schema means our main tables storage location

2)Work Schema : work Schema means our staging tables storage location

2) Agent:

Agent is a run time component it will interaction between client and servers like ODI studio to source and target databases interactions.

There are two types of agents.

1)Standalone agent

2)J2EE Agent

1)Standalone agent:

This standalone agent will work odi standalone installations. This agent job includes  below list.

1)Establish connection between client to servers and transfer your reqested source

code to source or target databases.

2)Creating session while execution your program or interface

3)Creating complete log for your program session

4)Closing session after finishing your program

2)J2EE Agent:

This agent will be available if you are using middleware like weblogic or websphere.

if you are using j2ee installations we can use this agent and we will below listed features.

1)Load balancing

2)High Availability

3)Multipl Agents

4)and same features as Standalone agent.

Physical Architecutre we will Physical Agents like Standalone or j2ee agents and

we will Data Server for source and target technologies for earch data server we need to create Physical schemas. In Data Server We need create atleast one physical schema.

2: Context:

context is a mediator between Physical architecture and Logical Architecture and Context we are using for Particular environments or Server locations like DEVELOPMENT, TESTING ,  PRODUCTION Environments or US, UK, ASIA ,.... like Server locations.

3: Logical Architecture:

In logical Architecture we will use logical Technologies and agents.

Logical architecture is nothing but alias name (synonym or another name) of your physical architecture.

1)technologies:

Here we will create only Logical Schemas there is no Dataserver concept.

This logical schemas will be assigned your physical schemas through Context.

2)Agents:

In Logical Architecture we will create only logical agents is nothing but alias name

of your physical agent that it may be Standalone or J2ee agent.

This logical agent will connect to your physical agent through Conext.

4: Languages:

In Langauages we can list of supporting languages in ODI. ODI supporting below listed langauages.

1)Groovy

2)JAVA

3)JYTHON

4)SAP

5)SQL

6)SQL FILE

This listed languages we can use in ODI Designer. Like we can run these listed langaues code in Designer Procedure.

5: Repository:

In Repositories we can find Master repository and work repository.

But here we can create only work repositories and we con't create another master  repository here.

1)Master Repository

2)Work Repository

6: Generic Action:

In ODI these generic action we are using for to generate common ODI Jython  code for Oracle Database operations like

Creating table, Alter table, Creating primary key,indexex, modifying columns

These availabe list we will using in designer.

Creating New Data Server for Oracle Technology database.

Right Click on=>Oracle=>New Data Server

we are creating new dataservers for below schemas available in oracle database

DEV

TEST

PROD

These all schemas will be available in target database and we can considar this

schemas like environments DEV,TEST and PROD

After creating Physical DataServer , Physical Schema , Contexts and Logical Schemas we can go to  Designer for creating models.

MODEL:

Model is containing metadata and we are import database objects from database to models using Reverse engineering concept.

Reverse Engineering:

Reverse engineering is a process to import all database objects like tables, columns, Constraints from data base to models.

There are two types Reverse engineering:

1)Standard Reverse Engineering

2)Customised Reverse Engineering

1)Standard Reverse Engineering:

In Standard Reverse Engineering odi will identify Database drivers and it will connect to Database and it will import objects from database to Models.

2)Customised Reverse Engineering:

In Customised Reverse engineering we are providing syntax for our own code to connect database and import selected objects from database to models.

That customised requirement is nothing but Knowledge Module.

We are using RKM Reverse engineering Knowledge Module for Customised Reverse engineering.

Knowledge Module:

Knowledge module is a step by step process template and we can customised this KM for our requirements.

Difference Between Standard and Customised Reverse Engineering?

1)ODI will identify standard database drivers and it will import database objects

to models

1) Customised Reverse engineering will be working on RKM and this RKM having

step by step import objects process.

2)In Standard reverse engineering we wont get Table partitions and indexes

2)In Customised reverse engineering we will partitions and indexes

3)Standard reverse engineering will work withour KM

3)Customised reverse engineering will work only with RKM

4)In Standard  reverse engineering we can import selected objects or tables

4)In customised reverse engineering we can't import select objects or tables

it will all tables from database to models if you want to restric some tables you

have to use filter condition in models.

1) RKM (Reverse Engineering Knowledge Module):

This RKM we are using for customised reverse engineering.

If we are using customised reverse engineering we will get additional objects like  Table partitions, Sub Partitions and partitioned indexes.

2) LKM (Loading Knowledge Module):

-------------------------------

This LKM will load the data from source table to staging table i.e., C$_tablename

example:

Source table: EMP

staging table:  C$_EMP

LKM Process steps:

step1: LKM will drop C$_EMP table if already exists

step2: LKM will create C$_EMP table same structure from source table EMP.

step3: LKM will select data from source table EMP and it will insert into staging

table C$_EMP table.

Note: LKM will always load the data from source table to C$table.

How to identify LKM's for particular databases or files.?

1) IF source is SQL and staging is SQL we can select LKM SQL to SQL

2) If source is file and stagning is SQL we can select LKM File to SQL

3) If source is Oracle and stagning is oracle we can select LKM SQL to SQL

or LKM Oracle to SQL or LKM Oracle to SQL

SQL we can use for any native SQL rdbms supporting databases.

3) IKM (Integration Knowledge Module):

IKM we are using in interface for data integration from Staging table C$table to I$Table and I$table to Target Table.

IKM Process Steps:

step1: drop I$table if already exists in staging.

step2: creating I$table same as target table structure with aditional column as UPDATE_FLAG. this flag having values like U-Update,I-Insert,N-no change

step3: Selecting data from C$table and inserting into I$table with flag as 'N' for all

rows.

step4:  Comparing with I$table and Target table based on Primary key value.

If all values are same with target table it will update flag as 'N'

if there is no PK data in target table it will update flag as 'I'

if there is PK data and there are some differences it will update flag as 'U'

step5: Selecting data from I$table where  flag is'I and it will insert into target table

selecting data from I$table where flag is 'U' it will update into target table

and it won't update  or insert records where flag is 'N' because N means

there is no change in data from target table.

Note :  Above steps is for only if you are using IKM Incremental Update Knowledge module. If you are using Control append knowledge module it wont create UPDATE_FLAG in I$table.

Types of IKM:

There are three types of IKM

1) IKM control Append : it is only for insert in target

2)IKM Incremental Update: Insert or Update

3)IKM Slowly changing Dimention.: this if for SCD Type 1 and Type 2

4) CKM (Checking Knowledge Module):

This CKM we are using Data Quality Check for constraint related.

it will support PK,NOt null and Unique key constraint related data quality checking.

There are two types of Data Quality Check in CKM.

1)FLOW CONTROL

2)STATIC CONTROL

1)FLOW CONTROL:

This flow control we have to enable at IKM level and we need to use CKM knowledge module and this CKM will do below processing.

Flow control will validate data at I$table before inserting into Target table.

If I$table having any duplicate or null records in I$table it will delete from

I$table and it will insert into E$table error table.

CKM Processing Steps: (Flow Control)

step1: Dropping E$table if already exists

step2: Creating SNP_CHECK_TAB table in staging area.

SNP_CHECK_TAB: This table we are using for common error table for all

interfaces. this table contain error message ,error number and interface name,

and schema name.

step3: Creating E$table same as I$table structure with additional columns like

error number,error description,error date and error type(PK or NOTNULL).

step4: Validating I$table if I$table having any duplicate or null rows it will select

and it will insert into E$table with error message,error type and error number

and it will delete from those rows in I$table.

Note:  Flow control will validate data at I$table before inserting into Target table

and it wont insert invalid data into target table it will insert only E$table..

2)STATIC CONTROL:

This static control we are using for data quality check at IKM level using CKM but this static will validate data at Target table level after inserting data into target table this Static control will validate data in target table if target table having any duplicate or null rows it select and insert into E$table but it won't delete from Target table.

Note:  Static control wont validate at I$table level it will  validate only at target table that is after inserting data from I$ table to Target table it will validate in target table. this static control it wont delete from target table. this is we are using for only information to know howmany rows are duplicate or nulls in target table.

Based on our requirement we can use Any FLOW CONTROL or STATIC COTROL or BOTH. this option we can enable at IKM level  options

FLOW_CONTROL=TRUE/FALSE  or STATIC_CONTROL=TRUE/FALSE.

How to reload error records from E$table to Target table?

we can reprocess errored records from E$table to target table using One Option at

IKM level i.e.,  RECYCLE_ERRORS=TRUE.

Note:  This recycle errors will validate agin in E$table if E$table having valid data

it will insert into target table other wise it wont insert into target table.

This option we will user after modifieing or correcting data in E$table.