ODI 12C interview questions for experienced/ODI 12C Interview Questions and Answers for Freshers & Experienced

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

Different types of Facts?

a. Transaction Fact
b. Snapshot Fact
c. Accumulate Fact
d. Factless Fact

What is Fact and Dimension?

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

How to load valid records into one table and invalid records into another table?

Using Split component in ODI 12c we can achieve it.

What are different ways to promoting code ?

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

How promote code from one environment another environment?

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

What is meant by load balancing and how to do setup for it?

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.

What is sequence and different types of Sequence available in ODI?

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.

Can we reverse engineer Flat Files?

Yes, we can reverse engineer the flat files.

Different types of Reverse Engineering?

Two types of Reverse Engineering:

<> Standard Reverse Engineering
<> Customized Reverse Engineering

What are the prime responsibilities of the Data Integration Administrator?

1. Scheduling and executing batch jobs.

2. Configuring, starting, and stopping the real-time services

3. Adapters configuration and managing them.

4. Repository usage, Job Server configuration.

5. Access Server configuration.

6. Batch job publishing.

7. Real-time services publishing through web services.

What is a procedure and how to write the procedures in ODI?

A Procedure is a reusable component that allows you to group actions that do not fit in the Interface framework (That is load a target datastore from one or more sources).

A Procedure is a sequence of commands launched on logical schemas. It has a group of associated options.

These options parameterize whether or not the command should be executed as well as the code of the commands.

Suppose having unique and duplicates but I want to load a unique record one table and duplicates one table?

Create two interfaces or once the procedure and use two queries one for Unique values and one for duplicate values.

What systems can ODI extract and load data into?

ODI brings true heterogeneous connectivity out-of-the-box, it can connect natively to Oracle, Sybase, MS SQL Server, MySQL, LDAP, DB2, PostgreSQL, Netezza.

It can also connect to any data source supporting JDBC, its possible even to use the Oracle BI Server as a data source using the JDBC driver that ships with BI Publisher

How to reverse engineer views(how to load the data from views)?

In Models Go to the Reverse engineering tab and select the Reverse engineering object as VIEW.

How to load the data with one flat file and one RDBMS table using joins?

Drag and drop both File and table into source area and join as in Staging area.

If the source has a total of 15 records with 2 records are updated and 3 records are newly inserted. Which knowledge module we should use to get these changes on the target side.

We have to load the newly changed and inserted records Use IKM Incremental Update Knowledge Module for Both Insert n Update operations.

How to implement the logic in procedures if the source side data deleted that will reflect the target side table?

User this query on Command on target Delete from Target_table where not exists (Select ‘X’ From Source_table Where Source_table.ID=Target_table.ID).

In the package, one interface got failed how to know which interface got failed if we no access to an operator?

Make it mail alert or check into SNP_SESS_LOG tables for session log details.

How to implement data validations?

Use Filters & Mapping Area AND Data Quality related to constraints use CKM Flow control.

Suppose having unique and duplicates but I want to load a unique record one table and duplicates one table?

Create two interfaces or once the procedure and use two queries one for Unique values and one for duplicate values.

Remove the duplicate in ODI?

Use DISTINCT, at the IKM level. it will remove the duplicate rows while loading into the target.

How to write the sub-queries in ODI?

We can follow anyone of the following to create a subquery.

1. Using the Yellow interface and sub-queries option we can create subqueries in ODI.

2. Using a VIEW we can go for subqueries.

3. Using ODI Procedure we can call direct database queries in ODI.

Suppose I having 10 interfaces and running the interface 5th one failed how to run the remaining interfaces?

If you are running Sequential load it will stop the other interfaces. so go to operator navigator and right-click on the failed interface and click on restart.

If you are running all the interfaces are parallel only one interface will fail and other interfaces will finish.

Does ODI support web services?

Yes. ODI supports web services, ODI is ‘SOA’ enabled and its web services can be used in 3 ways:

The Oracle Data Integrator Public Web Service, lets you execute a scenario (a published package) from a web service call Data Services, which provides a web service over an ODI data store (i.e. a table, view or other data source registered in ODI).

The ODI Invoke Web Service tool that you can add to a package to request a response from a web service.

Explain step-by-step procedure to enable Journalization?

The first step is to import proper JKM. After creating a model and reverse engineering we have to add the model to CDC and then we need to subscribe to the table we want. This will enable Journalization.

How to pass a variable more than one values?

You can't.
During 1 session, 1 ODI variable can have only 1 value at a time.
If you need more than 1 value, you will have to do somethink like a loop inside your package, and refresh the variable value each time.

What is Journalization and why we are using it in ODI?

It is the way to implement change data capture in ODI. We use JKM for this purpose.

What is the J$ table in ODI?

This is where all changes are recorded. Journals contain references to the changed records along with the type of change (insert, update or delete).

What is the I$ table in ODI?

This is a flow table created by IKM while integrating data in the datamart. This is a temporary table used by ODI.

What is the E$ table in ODI?

Temporary Error table created by ODI. This is created by CKM.

What do you mean by "moving data" ?

If you're talking about moving the ODI object, like scenario, projects, interfaces... then use "export / import" tool to move your source into other environment.

If you're talking about data that are in your database table, then use "context". Create 1 context for development, 1 context for Testing and so on.
Just change the context when you want to change data / environment in order to do your QA tests.

what is the purpose of defining the OLAP type (dimension, fact table, SCD) in the data store properties in ODI?

I'm already familiar with dimensions & fact tables as used in data warehousing modeling.
I am just wondering how specifying the OLAP type of a data store affects its behavior.


A)Pretty sure it opens up the UI to set more options depending on that option, not at PC so cant check.
e.g SCD - Surrogate Key, Current Record, Start Date, End Date etc.

How to tune a mapping in ODI ?

Choose between various knowledge modules (KMs) for loading and integration.
Each one performs differently based on the source/target technology and the type of data that you're dealing with.
Some perform better than others depending on the situation. Some KMs use set-based transactions, others use PL/SQL blocks to commit data in small batches.

Choose where ODI should execute each join/filter/transform. You can usually choose between the source, target or a separate staging area.
What you choose here can influence how much source data you will extract and process.

The ODI KMs utilize temporary tables to perform the loading, checking and transform operations and will create the necessary indexes for performance.
It will also analyse these tables as part of the flow to enable the DB engine to generate accurate execution plans.

What are the types of data quality control?

There are two ways to data quality control:

<> Static: We will run the constraints on existing target data. This is done after loading the data into the target.

<> Flow: We will run the constraints on incoming data. This is done before loading the data into the target.

What is SKM and when we will use this SKM?

SKM (Service Knowledge Module) is used to generate code required for data services. These are used in data models.

Data Services are specialized web services that enable access to application data in datastores and to the changes captured for these datastores using Changed Data Capture.

Explain some differences between ODI 10g and ODI 11g?

ODI 11g provides a Java API to manipulate both the design-time and run-time artifacts of the product. This API allows you, for example, to create or modify interfaces programmatically, create your topology, perform import or export operations, launch or monitor sessions.

This API can be used in any Java SE and Java EE applications, or in the context of Java-based scripting languages like Groovy or Jython.

External Password Storage, to have source/target data servers (and contexts) passwords stored in an enterprise credential store.

External Authentication, to have user/password information stored in an enterprise identity store (e.g.: LDAP, Oracle Directory, Active Directory), and ODI authenticating against this store. These two features let you optionally store critical information in dedicated storage and not within the ODI repository.

The ODI Console may also use Oracle’s single-sign-on systems with ODI.

What are the types of Knowledge Modules?

LKM(used to extract data from heterogeneous source systems (files, middleware, databases, etc.) to a staging area).

IKM(used to integrate (load) data from staging to target tables)

RKM(used to perform a customized reverse-engineering of data models for a specific technology. It extracts metadata from a metadata provider to ODI repository. These are used in data models.)
JKM(used to create a journal of data modifications (insert, update and delete) of the source databases to keep track of changes. These are used in data models and used for Changed Data Capture.)
CKM( used to check data consistency i.e. constraints on the sources and targets are not violated. These are used in the data model’s static checks and interface flow checks. Static check refers to constraints or rules defined in the data model to verify the integrity of source or application data.
Flow check refers to declarative rules defined in interfaces to verify an application’s incoming data before loading into target tables.)

Can I create more than one Master Repository in ODI?

Yes. In general, you need only one master repository. However, it may be necessary to create several master repositories if the Project construction over several sites not linked by a high-speed network (off-site development, for example).

The necessity to clearly separate the interfaces operating environments (development, test, production), including on the database containing the master repository. This may be the case if these environments are on several sites.

What are Session Tasks?

The task is the smallest execution unit. It corresponds to a procedure command in a KM, a procedure, assignment of a variable, etc

What is a Session?

A session is an execution (of a scenario, an interface, a package, or a procedure, …) undertaken by an execution agent. A session is made up of steps that are made up of tasks.

how to prevent overwrite existing data in table

In target propertices of flow tab if you are using Incremental update tab

Set Option UPDATE = false.
OR
Use Control append KM

Is it possible to place a variable as a parameter in an API Reference?

I have a project variable (var1) which uses an API Reference <%=odiRef.getContext("CTX_CODE")%> to get the context code.
I also have another project variable (var2) that uses the API reference <%=odiRef.getSchemaName ()%>.
Then I want to use var1 as a parameter in API <%=odiRef.getSchemaName("SCHEMA_NAME", "VAR1", "W")%>... is this possible?

A)Though i doubt on this still Check it once.

<%=odiRef.getSchemaName("SCHEMA_NAME", "#VAR1", "W")%>

I'd like to ask if it possible to use a stored procedure (maybe in cursor) as a data source?

As far as i know i dont think you can.
Either you can use a table, or view or synonym.
If you have a sql query convert it to a view or use a temporary interface.

Iam not very sure the codes used in your procedure. If you can put the code here then it would be better to understand and develop the logic.
If its a direct insert to your table you can use odi procedure with java code and jython codes.
We can write the return resultset of your procedure to a table with help help java/jython without taking the help of interface.

We have an options recyle_errors on target table Flow tab. how exactly it works?

ODI KM’s option called Recycle Errors does the following

ODI reads from the E$ tables and loads into the I$ capturing all the records

which are not present in the I$ by matching on the Primary Key or Unique Keys.

What are the User Functions?

User functions enable you to define customized functions or “functions aliases”, for which you will define technology-dependant implementations. They are usable in the interfaces and procedures.

Search
R4R Team
R4R provides ODI 12C Freshers questions and answers (ODI 12C Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,ODI 12C interview questions for experienced,ODI 12C Freshers & Experienced Interview Questions and Answers,ODI 12C Objetive choice questions and answers,ODI 12C Multiple choice questions and answers,ODI 12C objective, ODI 12C questions , ODI 12C answers,ODI 12C MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for ODI 12C fresher interview questions ,ODI 12C Experienced interview questions,ODI 12C fresher interview questions and answers ,ODI 12C Experienced interview questions and answers,tricky ODI 12C queries for interview pdf,complex ODI 12C for practice with answers,ODI 12C for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .