ODI Interview Questions

[Note: If you want answer for question which is not here (ODI only) please comment below. So that we will update the Interview questions]

General Questions:

1. What is difference between ODI 11g and ODI 12c?

Ans:

S.No	ODI 11g	ODI 12c
1	No Component Palette	Component Palette Added
2	No Debugger	Debugger
3	Interface for loading source to target Sub Tabs: ·         Overview ·         Quick Edit ·         Flow ·         Control	Mapping for Loading Source to Target Sub Tabs: ·         Overview ·         Logical ·         Physical
4	In interface we will have only one target datastore	Mappings we can have more than one data store (i.e., we can load more than one data store at a time)
5	OWB jobs can’t execute	OWB jobs can be executed in ODI 12c
6	To perform Incremental and Initial Load we need to create two different interfaces	To Perform Incremental and initial Load we can accomplish with single mapping (Using Deployment Specification)
7	No Wallet Password	Wallet Password is available
8	Temporary Interface	Reusable Mappings

2.Different types of Repositories?

Ans: They are two types of Repositories
1. Master Repository and
2. Work Repository

Master Repository:
It holds information related to Topology and Security

Work Repository:
It stores information related to Designer and Operator

3.Different types of Work Repositories?

Ans: They are two types of Work Repositories

• Execution Work Repository
• Development Work Repository

4.What is the hierarchy of Master and Work Repositories?

Ans: We can have one Master and 'n' Work repositories. But each Work repository should be attach to only one Master Repository

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

6.What is meant by OLAP and OLTP?

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

7.How many types of Navigators and their usage?

Ans: Four Types of Navigators are available.

• Designer - Development
• Operator - Monitoring
• Topology - Configuration
• Security - Provide Security

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

9.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:

• Standalone Agent - Light Weight
• J2EE Agent - High Availability , Scalability, Security and better manageability
• Colocated Standalone Agent - Combination both standalone and J2EE agent

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

11.What is an interface/Mapping?

Ans:Mappings are the logical and physical organization of your data sources, targets, and the transformations through which the data flows from source to target. We can create and manage mappings using the mapping editor, a new feature of ODI 12c.

12.What is a variable and different types of variables?

Ans:A variable is an object that stores a single value. This value can be a string, a number or a date. The variable value is stored in Oracle Data Integrator. It can be used in several places in your projects, and its value can be updated at run-time

• Refresh Variable
• Set Variable
• Declare Variable
• Incremental Variable
• Evaluation Variable

13.What is knowledge module and different types of KM's?

Ans:Knowledge Modules (KMs) are code templates. Each KM is dedicated to an individual task in the overall data integration process

Types of KM's:

• LKM - Loads heterogeneous data to staging area
• IKM - Integrate Data from staging area to target area
• RKM - Retrieves metadata to Oracle Data Integrator work repository
• SKM - Generates Data Manipulates web services
• CKM - Checks the constraints
• JKM - Used for CDC (Change Data Capture)

14.Different types of Reverse Engineering?

Ans:  Two types of Reverse Engineering:

• Standard Reverse Engineering
• Customized Reverse Engineering

15.What is scenario?

Ans: The scenario code (the language generated) is frozen, and all subsequent modifications of the components which contributed to creating it will not change it in any way

16. What is difference between scenario and package?

Ans: Scenario is froze code we can't do any modifications

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

18.What is difference between Package and Load Plan?

Ans:

S.No	Load Plans	S.No	Packages
1	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	1	There is no native support
2	Load plans are moved to production as it is	2	Packages need to convert into scenario and then promote to production
3	We can create load plans in Execution Work repository also (like PROD Environment)	3	We can’t create packages in Execution Work Repository (like production environment)
4	When we execute/restart Load Plans it will create new instances	4	When we execute package it will create new session and for restart it will override the execute session

19. How to perform exception Handling in ODI?

Ans: Click Here

20. How to perform parallel execution and serial execution in packages?

Ans:

21.Can we reverse engineer Flat Files?

Ans: Yes, we can reverse engineer the flat files.

22.What are the advantages of standalone and J2EE agent?

Ans: J2EE Agent is high available.

23. What is difference between Static and Flow control?

Ans:

Serial No	Static Control	Flow Control
1	Data validation is done after loaded to target table	Data validation is done before loaded to target table
2	Data validation is done on Target Table	Data Validation is done on I$ table
3	If there is any constraints on Target Data server then we need to disable the constraint before load to avoid failure	No need to disable any constraint
4	It won’t delete record from I$ table	It will delete record from I$ table

24. What is the difference between lookup and Join?
Ans: Click Here

25. How to load data from XML file to Relational database table?
Ans: Click Here

26. Different types of Variables available in ODI?
Ans: Click Here

27. What is sequence and different types of Sequence available in ODI?
Ans: A sequence is a variable automatically incremented when used. Between two uses the value is persistent.
■Standard sequences - whose current values are stored in the Repository.
■ Specific sequences- whose current values are stored in an RDBMS table cell. Odi reads the value, locks the row and updates the row after the last increment.
■ Native sequence - that maps a RDBMS-managed sequence.

28. What is User Functions?
Ans: User functions allow you to define customized functions  for
which you will define technology-dependent implementations. They are usable in
mappings and procedures.

29.What is Marker?
Ans: A component of a project may be flagged in order to reflect a methodology or
organization. Flags are defined using markers. These markers are organized into
groups, and can be applied to most objects in a project

30.What is Scenario?
Ans: When a package, mapping, procedure, or variable component has been fully
developed, it is compiled in a scenario. A scenario is the execution unit for production.
Scenarios can be scheduled for automated execution

31.What is meant by load balancing and how to do setup for it?
Ans: Load Balancing:
Oracle Data Integrator allows you to load balance parallel session execution between
physical agents.
An agent's load is determined at a given time by the ratio (Number of running
sessions / Maximum number of sessions) for this agent.

To setup load balancing:
1. Define a set of physical agents, and link them in a hierarchy of agents
2. Start all the physical agents corresponding to the agents defined in the topology.
3. Run the executions on the root agent of your hierarchy. Oracle Data Integrator will
balance the load of the executions between its linked agents.

32. What is an agent and types of agents available in ODI?
Ans: At design time, developers generate scenarios from the business rules that they have designed. The code of these scenarios is then retrieved from the repository by the Run-Time Agent.
This agent then connects to the data servers and orchestrates the code execution on these servers. It retrieves the return codes and messages for the
execution, as well as additional logging information – such as the number of processed records, execution time etc. - in the Repository.
The Agent comes in two different flavors:
■The Java EE Agent can be deployed as a web application and benefit from the features of an application server.
■ The Standalone Agent runs in a simple Java Machine and can be deployed where needed to perform the integration flows.
■Colocated Standalone Agents can be installed on the source or target systems . They can be managed using Oracle Enterprise Manager and must be configured with
an Oracle WebLogic domain. Colocated Standalone Agents can run on a separate machine from the Oracle WebLogic Administration Server

Migration:

1. How promote code from one environment another environment?

Ans: We can promote the code in 2 ways
a.Import and export scenarios
b. Smart Import and Smart Export Objects

2. What are different ways to promoting code ?

Ans:
a. Import and export scenarios
b. Smart Import and Smart Export Objects

3. What is difference between Smart import/Export and import/Export Scenario?

Ans: Import and Export we usually import or export that particular object or scenario but not the depend objects. Import and Export Scenarios is recommended for higher environment because we are no longer do development in higher environment (like UAT , PROD)

Smart Import and Smart Export is used to move code along with dependency object. This time of migration is recommended for lower environments like (DEV,QA) where we need to do development.

Real Time Scenario:

1. How to move duplicate records into another table using ODI?

Ans: Clikc Here

2.What is SQL query to remove duplicate records from table?

Ans: Click Here

3. In sales table I have year,Q1,Q2,Q3 and Q4 columns, now we need to convert those into Year,Quarter,Sales as below? What is approach you will follow?

For Example

Year	Q1	Q2	Q3	Q4
2014	10	11	12	9.5
2015	8.9	12	11.2	10

Year	Quarter	Sales
2014	Q1	10
2014	Q2	11
2014	Q3	12
2014	Q4	9.5
2015	Q1	8.9
2015	Q2	12
2015	Q3	11.2
2015	Q4	10

Ans: Click Here similar example

 

4. What is the approach you will follow for converting columns into rows?

Ans: Using Un-Pivot

5. How to load valid records into one table and invalid records into another table?
Ans: Using Split component in ODI 12c we can achieve it. click here

6.How to load multiple files to single target table using single interface/Mapping in ODI?
Ans: Click Here

7.How to populate one value based on context selection (FLEXIFIELDS)?
Ans: Click Here


SQL Interview Questions: (In one of the Product based company)

When to you use In Operator and Exists Operator in Oracle SQL?
Ans. Click Here

Difference between case and decode?
Ans.

S.No	CASE	DECODE
1	CASE can work with logical operators other than ‘=’	DECODE performs an equality check only
2	CASE can work with predicates and searchable subqueries	DECODE works with expressions that are scalar values only
3	CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL	DECODE can work as a function inside SQL only
4	Case handles NULL’s differently Example: select case null           when null           then 'NULL'           else 'NOT NULL'           end null_test    from dual; Result: NULL_TES -------- NOT NULL	Decode handles without ‘is’ operator Example: select decode(null                , null, 'NULL'                       , 'NOT NULL'                  ) null_test     from dual; Result: NULL ---- NULL
5	CASE expects datatype consistency	DECODE does not
6	CASE complies with ANSI SQL.	DECODE is proprietary to Oracle.

Difference between functions and Procedures?
Ans.

Functions	Stored procedures
Functions must always return a value.	Stored procedures do not have this requirement.
Functions cannot alter data or objects in a server.	Stored procedures can alter data and objects in database and server.
You can embed functions within a SELECT statement.	Stored procedures cannot be embedded within a SELECT statement.
Both functions and stored procedures can accept parameters. Functions can accept input parameters but can return only a single return value.	Stored procedures can also accept OUTPUT parameters.
TEMP tables can’t be used in functions.	Both TEMP tables and Table variables can be used in stored procedures.

How to delete the duplicate records and retaining one record among all?
Ans.
DELETE FROM TESTTABLE WHERE ROWID IN (SELECT ROWID FROM (
SELECT RN,ROWID FROM (
SELECT CODE,N,ROW_NUMBER() OVER(PARTITION BY CODE,N ORDER BY CODE DESC) RN
FROM TESTTABLE) WHERE RN>1));

Analytical Functions:

LAG
The LAG function is used to access data from a previous row.

LEAD
The LEAD function is used to return data from the next row.

Syntax:
LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

value_expression - Can be a column or a built-in function, except for other analytic functions.
offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default - The value returned if the offset is outside the scope of the window. The default value is NULL.


The LISTAGG analytic function making it very easy to aggregate strings. it allows us to order the elements in the concatenated list. this function is used for string aggregation.

RANK:
RANK function is a built in analytic function which is used to rank a record within a group of rows. Its return type is number and serves for both aggregate and analytic purpose in SQL.

RANK (expression) WITHIN GROUP (ORDER_BY expression [ASC | DESC] NULLS
RANK () OVER (PARTITION BY expression ORDER_BY expression)

ROW_NUMBER:
 assigns unique numbers to each row within the PARTITION given the ORDER BY clause

RANK() - behaves like ROW_NUMBER(), except that “equal” rows are ranked the same.

Primary Key Vs Unique Key
Primary Key:

There can only be one primary key in a table
In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
Primary Key is a unique key identifier of the record

Unique Key:

Can be more than one unique key in one table
Unique key can have NULL values
It can be a candidate key
Unique key can be NULL and may not be unique

Surrogate and Natural Key

 A surrogate key is an artificial or synthetic key that is used as a substitute for a natural key.
A natural key is a key from within the data.
A surrogate key is a key that we introduce in the data so as to be able to
identify particular piece of information  easily.
Usually, it is the unique identifier for each record.

How to identify whether string is only alpha numeric?
SELECT * FROM DUAL where regexp_like('abc123','^[0-9a-zA-Z]+$')
Output:
x

SELECT * FROM DUAL where regexp_like('abc123@','^[0-9a-zA-Z]+$')
Output:

abc123 is alphanumeric
abc123@  not alphanumeric

Data Engineer :

Written Test Questions (Product based Company)
Click Here


Data WareHouse:

1. What are different types of Schemas?
Ans: Star Schema and Snow Flake schema

2. What is different between SnowFlake and Star Schema?
Ans: 

Snowflake is Normalized when compare to Star Schema.

In Snowflake schema, dimensions splitted inot multiple dimension tables whereas in Star Schema dimensions are not Normalized further.

In Snowflake schema we may need more number of joins to fetch records whereas in Star Schema we don't require any complex joins.

In Snowflake schema won't occupy more space compare to Star Schema.

3.What is Fact and Dimension?
Ans: Fact table contains measurements, metrics, and facts about a business process while the Dimension table is a companion to the fact table which contains descriptive attributes to be used as query constraining

4.Different types of Facts?
Ans: 

a. Transaction Fact

b. Snapshot Fact

c. Accumulate Fact

d. Factless Fact

5.Different Types of Dimensions?
Ans: 

a. Confirmed Dimension

b. Junk Dimension

c. Degenerated Dimension

d. Role Play Dimension

e. Slowly Change Dimension

f. Static Dimension

g. Inferred Dimension

6.What is meant by CDC?
Ans: CDC - Change Data Capture i.e., whenever there is any change happens to source system we need to capture only those changes and process to Target system.

7. Different types of CDC?
Ans: 

a. Simple Journalization

b. Consistent Set Journalization

8.What is meant by SCD?
Ans: SCD - Slowly Change Dimension

9. Different types of SCD?
Ans: We usually consider three types of SCD 

Type 1 : Overwrite all the changes from Source to Target

Type 2 : Capture all the changes from Source to Target as new record altogether

Type 3 : Capture a few changes from source to Target as new attribute (Limited history)    

10. What is Flexifields?
In ODI you can create user-defined fields on certain objects. You can think of these fields as additional attributes for certain objects.
At design time you populate these attributes with values that are then used at runtime, e.g. by a Knowledge Module.
There are various Flexfields defined out of the box for very specific requirements, e.g. there are Flexfields defined on the Datastore object for SAP and HIVE data integration tasks