ODI Interview Questions and Answers

Q1. What is Oracle Data Integrator (What is ODI)?

Ans: Oracle Data Integrator (ODI), Oracle’s ETL tool, is a comprehensive data integration platform, capable of high performance, high volume data loads and data transformations. It can be used to extract data from a variety of data sources (relational databases, flat files, etc.), perform complex data transformations, and load data into a variety of target data stores (data warehouses, application databases, flat files, etc.).  ODI can be used to support a variety of data integration projects, including the following:

• Business Intelligence and Data Warehousing
• Data Migrations and Data Consolidations
• Master Data Management

Q2. What is the difference between ETL and ELT?

Ans:In ETL we should have Middle Tier Server Engine where as in ELT shouldn't require Middle-Tier Server Engine. So it reduce cost. Network traffic is more for ETL where as for ELT less network traffic compare to ETL

Q3. What is Repository and Types of Repositories?

Ans:A repository is a metadata matianance database user or schema. in this schema we will maintain all odi components information like topology, security, designer and operator components information.
There are two types of repositories.

1. Master Repository
2. Work Repository

Q4. What is meant by OLAP and OLTP?

Ans:OLAP - OnLine Analytic Process - Maintain Historical Data
OLTP - OnLine Transaction Process - Daily Data (Business Data)

Q5. What is significant difference between Physical Schema and Logical Schema?

Ans:Physical Schema = Logical Schema + Context
For Example: Consider an Organization A whose Branches are A1,A2 and A3.
Consider the Structure of A1,A2 and A3 Schema's are same but located in different Servers. By the EOD all the data stored in A1,A2 and A3 to be stored in A.
For above scenario, developer develops one mapping with one logical Schema , 3 Physical Schema (A1,A2 and A3)and 3 Context (A1_CTX,A2_CTX and A3_CTX) . While executing the mapping if he selects A1_CTX it loads to A1. (i.e., Logical_schema+A1_CTX = A1... ) . That means we can reuse same code to pick data from different schema's.

Q6.What is an Agent and different types of Agents?

Ans:ODI agent is run time component which orchestrates the data integration process.
They are three types of Agents:

1. Standalone Agent - Light Weight
2. J2EE Agent - High Availability , Scalability, Security and better manageability
3. Colocated Standalone Agent - Combination both standalone and J2EE agent

Q7.What is Context and it's purpose?

Ans:Contexts bring together components of the physical architecture (the real Architecture) of the information system with components of the Oracle Data Integrator logical architecture (the Architecture on which the user works).
For example, contexts may correspond to different execution environments (Development, Test and Production) or different execution locations (Boston Site, New-York Site, and so forth.) where similar physical resource exist.

Q8.What is Logical Schema?

Ans:Logical schema is alias for your physical schemas and it will connect to your physical schemas throgh contexts. In Designer  developer can access only logical schemas he con not access physical schemas, developer can access logical schemas and context combination he
can connect physical schemas.

Q9. What is Designer?

Ans:Designer component we are using for complete development area. in design we will work for development like creating interfaces, packages, procedures,
variables, sequences and other objects, after finishing development we will generate schenarios and we can move this all scenarios  in to production.

Q10.What is Scenario?

Ans:A scenario is a compiled or executable object and it is not editable, in odi production environemnt we will use only scenarios for mataining
security and hiding. because this scenarios is not readeable  and not editable. this scenarios only schedules or executable.

Q11. What is Interface ?

Ans:An interface we are using to load data from source to target , here we can apply all the transformations and validations and aggregations.In odi 11g interface we can use only one target in one interface, if we want load data into multiple target tables we have to go for multiple
interfaces and use IKM Multiple table insert knowledge module.

In ODI 12c Interfaces we are calling Mappings and we can use multiple target tables in one mapping.

Q12. What is Yellow Interface?

Ans:Yellow interface we are using for without target table and we can create dynamically target table  if we dont have target table.for this we need to enable CREATE_TARGET_TABLE option=> TRUE at IKM level.This yellow interface we can use for source table as well. This yellow interfaces we are calling Reusable mappings in 12c.

Q13. What is Simulation option at interface?

Ans:This option we are using for to show complete interface process like LKM and IKM and CKM process like creating all $tables and inserting into target table queries everything it will give a report.
Note:  Simulation will give only report it wont run in session or program.

Q14. What is CDC?

Ans:CDC change data capture we are using for to extract changed data from source. if source accepting Triggers on source table we will use this CDC feature if source system they are not allowing to create a triggers we can't use this CDC feature we will go for timestamp based filtering.
Note:  If source table having any timestamp or modified data column we can filtering if source table dont have timestamp or modified date column we have to
use CDC feature (journalization concept) if they are not allowing to create triggers on source table we have to select everying and we need to maintain seperate
staging table for checking data from source and our staging table data.
Note: If source table dont have timestamp column and they are not allowing to create CDc (triggers) then we need to create one staging table STG_ORDERS
with timestamp column first we will load full data from source table (SOURCE_ORDERS) table to our stagning table, but we will maintain this
staging data permanently and we will create one more interface to load this STG_ORDERS table to TARGET_ORDERS table based on timestamp column.
from next time onwards while select data from SOURCE_ORDERS table we will check STG_ORDERS table if STG_ORDERS table dont  have source data
that data only we will select and we will insert.

SELECT * FROM SOURCE_ORDERS  WHERE NOT EXITS ( SELECT 'X' FROM STG_ORDERS);
for this requirement we need two inerfaces one for SOURCE_ORDERS table STG_ORDERS table and another interface for
STG_ORDERS table to TARGET_ORDERS table.
STG_ORDERS to TARGET_ORDERS table we can use timestamp filter condition on STG_ORDERS and it will get selected only modified data.
we cannot delete data from STG_ORDERS table.

Q15. What is SCD?

Ans:SCD Slowly changing dimension we are using for to load changing dimension information data.
where are three types of SCD'same
1) SCD TYPE1:  No history, we will all columns SCD property as OVERWRITE ON CHANGE in ODI.
SCD TYPE1: we can use IKM Oracle Incremental update KM or we can IKM Slowly Changing Dimension both we can use.
2) SCD TYPE2:  Here we can maintain no of times history, so we need to maintain below SCD properties in SCD TYPE 2 table.
1) ADD Row On CHANGE: this property will add a row whenever we are receiving changed data to maintain history.
2) STARTING_ TIME STAMP:  This will show record starting date and time
3) Ending Time stamp:  this will show record ending date and time
4) RECORD FLAG:  this will show active record if it is Y and if it is N for old rows or history rows.
3) SCD TYPE 3:  As of now ODI is not supporting we have to customise our IKM Slowly Changing Dimension. it will maintain only one time history in different column like , CURRENT SALARY and PREVIOUS SALARY two columns.

Q16.  What is Load Plan and different types of Load Plans?

Ans:A Load Plan is an executable object in Oracle Data Integrator that can contain a hierarchy of steps that can be executed conditionally, in parallel or in series
Types of Load Plans

• Parallel
• Serial
• Conditional

Q17. What is difference between Package and Load Plan?

Ans: