ODI Interview Questions and Answers

These Oracle Data Integrator Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of Oracle Data Integrator As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer .we are going to cover top 100 Oracle Data Integrator Interview questions along with their detailed answers.

We will be covering Oracle scenario based interview questions Oracle Data Integrator interview questions for freshers as well as interview Oracle questions and answers for experienced

1. What is Oracle Data Integrator (ODI)?

Ans:

• Oracle acquired SUNOPSIS with its ETL tool called “Sunopsis Data Integrator” and renamed to Oracle Data Integrator (ODI) is an E-LT (Extract, Load and Transform) tool used for high-speed data movement between disparate systems.
• The latest version, Oracle Data Integrator Enterprise Edition (ODI-EE 12c) brings together “Oracle Data Integrator” and “Oracle Warehouse Builder” as separate components of a single product with a single licence.

2. Explain what is ODI? why is it different from the other ETL tools

Ans:

ODI stands for Oracle Data Integrator. It is different from another ETL tool in a way that it uses E-LT approach as opposed to ETL approach.

1. 1. This approach eliminates the need of the exclusive Transformation Server between the Source and Target Data server.
2. 2. The power of the target data server can be used to transform the data. i.e. The target data server acts as staging area in addition to its role of target database
3. 3. While loading the data in the target database (from staging area) the transformation logic is implemented.
4. 4. Also, the use of appropriate CKM (Check Knowldege Module) can be made while doing this to implement data quality requirement.

3. What is E-LT? Or What is the difference between ODI and other ETL Tools?

Ans:

• E-LT is an innovative approach to extracting, loading and Transforming data.
• Typically ETL application vendors have relied on costly heavyweight , mid-tier server to perform the transformations required when moving large volumes of data around the enterprise.
• ODI delivers unique next-generation, Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs, even across heterogeneous systems by pushing the processing required down to the typically large and powerful database servers already in place within the enterprise.

4. Components of Oracle Data Integrator?

Ans:

Oracle Data Integrator” comprises of:

1. 1. Oracle Data Integrator + Topology Manager + Designer + Operator + Agent
2. 2. Oracle Data Quality for Data Integrator
3. 3. Oracle Data Profiling

5. What systems can ODI extract and load data into?

Ans:

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

6. What are Knowledge Modules?

Ans:

Knowledge Modules form the basis of ‘plug-ins’ that allow ODI to generate the relevant execution code , across technologies , to perform tasks in one of six areas, the six types of knowledge module consist of:

1. 1. Reverse-engineering knowledge modules are used for reading the table and other object metadata from source databases.
2. 2. Journalizing knowledge modules record the new and changed data within either a single table or view or a consistent set of tables or views
3. 3. Loading knowledge modules are used for efficient extraction of data from source databases for loading into a staging area (database-specific bulk unload utilities can be used where available)
4. 4. Check knowledge modules are used for detecting errors in source data
5. 5. Integration knowledge modules are used for efficiently transforming data from staging area to the target tables, generating the optimized native SQL for the given database
6. 6. Service knowledge modules provide the ability to expose data as Web services
7. 7. ODI ships with many knowledge modules out of the box, these are also extendable, they can modified within the ODI Designer module.
8. 8. Oracle Data Integrator

7. Does ODI support web services?

Ans:

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

• The Oracle Data Integrator Public Web Service, that lets you execute a scenario (a published package) from a web service call Data Services, which provide 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

8. What is the ODI Console?

Ans:

ODI console is a web based navigator to access the Designer, Operator and Topology components through browser.

9. What is load plans and types of load plans?

Ans:

Load plan is a process to run or execute multiple scenarios as a Sequential or parallel or conditional based execution of your scenarios, and same we can call three types of load plans ,

1. 1. Sequential
2. 2. Parallel
3. 3. Condition based load plans.

10. What is profile in ODI?

Ans:

Profile is a set of objective wise privileges. we can assign this profiles to the users. Users will get the privileges from profile

11. How to write the sub-queries in ODI?

Ans:

• Using Yellow interface and sub queries option we can create sub queries in ODI.
• Or Using VIEW we can go for sub queries Or Using ODI Procedure we can call direct database queries in ODI.

12. How to remove the duplicate in ODI?

Ans:

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

13. Suppose having unique and duplicate but i want to load unique record one table and duplicates one table?

Ans:

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

14. How to implement data validations?

Ans:

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

15. How to handle exceptions?

Ans:

Exceptions In packages advanced tab and load plan exception tab we can handle exceptions.

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

Ans:

Make it mail alert or check into SNP_ SESS_ LOG tables for session log details.

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

Ans:

User this query on Command on target Delete from Target_table where not exists.

18. If the Source have total 15 records with 2 records are updated and 3 records are newly inserted at the target side we have to load the newly changed and inserted records

Ans:

Use IKM Incremental Update Knowledge Module for Both Insert n Update operations.

19. Can we implement package in package?

Ans:

Yes, we can call one package into other package.

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

Ans:

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

21. If the source and target are oracle technology tell me the process to achieve this requirement(interfaces, KMS, Models)

Ans:

Use LKM-SQL to SQL or LKM-SQL to Oracle , IKM Oracle Incremental update or Control append.

22. What we specify the in XML data server and parameters for to connect to xml file?

Ans:

1. 1. File name with location
2. 2. F and Schema

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

Ans:

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

24. What are the types of Variables?

Ans:

• Global
• Project

A variable is an object that stores a single value.

1. 1. This value can be a string, a number or a date.
2. 2. The value is stored in Oracle Data Integrator, and can be updated at run-time.
3. 3. The value of a variable can be updated from the result of a query executed on a logical schema.
4. 4. For example, it can retrieve the current date and time from a database.
5. 5. A variable can be created as a global variable or in a project. Global variables can be used in all projects, while project variables can only be used within the project in which they are defined.

25. Where we can use variables?

Ans:

Variables can be used in all Oracle Data Integrator expressions:

• Mapping
• Filters
• Joins
• Constraints

26. What are Variable Steps?

Ans:

The Variable steps are,

Declare Variable : When a variable is used in a Package (or in elements of the topology which are used in the Package), it is strongly recommended that you insert a Declare Variable step in the Package. This step explicitly declares the variable in the Package.

Refresh Variable : This step refreshes the variable by running the query specified in the variable definition.

Set Variable : There are two functions for this

• 1. Assign sets the current value of a variable.
• 2. Increment increases or decreases a numeric value by the specified amount.

Evaluate Variable: This step compares the value of the variable with a given value according to an operator. If the condition is met, then the evaluation step is true, other-wise it is false. This step allows for branching in Packages.

27. What is Work Repository ?

Ans:

Each work repository is attached to a master repository, therefore, information about the physical connection to a work repository is stored in the master repository it is attached to.

Defining a connection to a work repository consists of defining a connection to a master repository, then selecting one of the work repositories attached to this master repository.

28. What is Master Repository ?

Ans:

The Master Repository is a data structure containing information on the topology of a company’s IT resources, on security and on version management of projects and data models. This repository is stored on a relational database accessible in client/server mode from the different modules.

Generally, only one master repository is necessary. However, in exceptional circumstances, it may be necessary to create several master repositories in one of the following cases:

1. 1. Project construction over several sites not linked by a high-speed network (off-site development, for example).
2. 2. 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.

29. What is a Procedure?

Ans:

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 data store from one or more sources).

30.What is Model?

Ans:

An Oracle Model is a set of datastores corresponding to views and tables contained in an Oracle Schema.

A model is always based on a Logical Schema. In a given Context, the Logical Schema corresponds to a Physical Schema. The Data Schema of this Physical Schema contains the Oracle model’s tables and views.

31. What is a Package ?

Ans:

The package is the biggest execution unit in Oracle Data Integrator. A package is made of a sequence of steps organized in an execution diagram.

32. What is User Parameters?

Ans:

Oracle Data Integrator saves user parameters such as default directories, windows positions,etc. User parameters are saved in the userpref.xml file in /bin.

33. What is a Project?

Ans:

A project is a group of objects developed using Oracle Data Integrator.

34. What is Folder?

Ans:

Certain objects in a project are organized into folders and sub-folders.

35. What is an Interface?

Ans:

An interface consists of a set of rules that define the loading of a Data store or a temporary target structure from one or more source Data stores.

36. What is Sequence?

Ans:

A sequence is an variable automatically incremented when used. Between two uses the value is persistent.

37. What is User Functions?

Ans:

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

38. What is Marker?

Ans:

Elements of a project may be flagged in order to reflect the methodology or organization of the developments. Flags are defined using the markers. These markers are organized into groups, and can be applied to most objects in a project.

39. What is Context?

Ans:

A context is a set of resources allowing the operation or simulation of one or more data processing applications. Contexts allow the same jobs (Reverse, Data Quality Control, Package, etc) to be executed on different databases and/or schemas

In Oracle Data Integrator, a context allows logical objects (logical agents, logical schemas) to be linked with physical objects (physical agents, physical schemas).

40. What is Sequences?

Ans:

A sequence is a variable that increments itself each time it is used. Between two uses, the value can be stored in the repository or managed within an external RDBMS table.

Oracle Data Integrator supports two types of sequences:

1. 1. Standard sequences – whose last value is stored in the Repository.
2. 2. Specific sequences – whose last value is stored in an RDBMS table cell. Oracle Data Integrator undertakes to read the value, to lock the row (for concurrent updates) and to update the row after the last increment.

41. What is Session?

Ans:

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 which are made up of tasks.

42. What is Session Tasks?

Ans:

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

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

Ans:

ODI 11g	ODI 12c
No Component Palette	Component Palette Added
No Debugger	Debugger
Interface for loading source to target	Mapping for Loading Source to Target
Sub Tabs: 1. Overview 2. Quick Edit 3. Flow 4. Control	Sub Tabs: 1. Overview 2. Logical 3. Physical
In interface we will have only one target data store	Mappings we can have more than one data store (i.e., we can load more than one data store at a time)
OWB jobs can’t execute	OWB jobs can be executed in ODI 12c
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)
No Wallet Password	Wallet Password is available
Temporary Interface	Reusable Mappings

44. Different types of Repositories?

Ans:

They are two types of Repositories

1. 1. Master Repository : It holds information related to Topology and Security
2. 2. Work Repository : It stores information related to Designer and Operator

45. Different types of Work Repositories?

Ans:

They are two types of Work Repositories

1. 1. Execution Work Repository
2. 2. Development Work Repository

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

47. 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 whereas for ELT less network traffic compare to ETL.

48. What is meant by OLAP and OLTP?

Ans:

1. 1. OLAP : On-line Analytic Process – Maintain Historical Data.
2. 2. OLTP : on-line Transaction Process – Daily Data (Business Data).

49. How many types of Navigators and their usage?

Ans:

Four Types of Navigators are available.

1. 1. Designer – Development
2. 2. Operator – Monitoring
3. 3. Topology – Configuration
4. 4. Security – Provide Security

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

51. 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. 1. Standalone Agent – Light Weight
2. 2. J2EE Agent – High Availability , Scalability, Security and better manageability
3. 3. Co-located Standalone Agent – Combination both standalone and J2EE agent

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

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

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

Types of variables,

1. 1. Refresh Variable
2. 2. Set Variable
3. 3. Declare Variable
4. 4. Incremental Variable
5. 5. Evaluation Variable

55. 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)

56. Different types of Reverse Engineering?

Ans:

Two types of Reverse Engineering:

1. 1. Standard Reverse Engineering
2. 2. Customized Reverse Engineering

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

58.  What is difference between scenario and package?

Ans:

Scenario is froze code we can’t do any modifications

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

60. What is difference between Package and Load Plan?

Ans:

Load Plans	Packages
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	There is no native support
Load plans are moved to production as it is	Packages need to convert into scenario and then promote to production
Interface for loading source to target	Mapping for Loading Source to Target
We can create load plans in Execution Work repository also (like PROD Environment)	We can’t create packages in Execution Work Repository (like production environment)
In interface we will have only one target data store	Mappings we can have more than one data store (i.e., we can load more than one data store at a time)
When we execute/restart Load Plans it will create new instances	When we execute package it will create new session and for restart it will override the execute session

61. How to perform exception Handling in ODI?

Ans:

Click here

62. Can we reverse engineer Flat Files?

Ans:

Yes, we can reverse engineer the flat files.

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

Ans:

J2EE Agent is high available.

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

1. 1. Standard sequences – whose current values are stored in the Repository.
2. 2. 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.
3. 3. Native sequence – that maps a RDBMS-managed sequence.

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

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

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

1. 1. The Java EE Agent can be deployed as a web application and benefit from the features of an application server.
2. 2. The Standalone Agent runs in a simple Java Machine and can be deployed where needed to perform the integration flows.
3. 3. 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

67. How promote code from one environment another environment? (or) The code of these scenarios is then retrieved from the repository by the Run-Time Agent.

Ans:

We can promote the code in 2 ways

1. 1. Import and export scenarios
2. 2. Smart Import and Smart Export Objects

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

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

Ans:

Using Un-Pivot

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

71. Difference between case and decode?

Ans:

CASE	DECODE
CASE can work with logical operators other than ‘=’	DECODE performs an equality check only
CASE can work with predicates and searchable subqueries	DECODE works with expressions that are scalar values only
CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL	DECODE can work as a function inside SQL only
CASE expects datatype consistency	DECODE does not
CASE complies with ANSI SQL	Mappings we can have more than one data store (i.e., we can load more than one data store at a time)
When we execute/restart Load Plans it will create new instances	DECODE is proprietary to Oracle
Case handles NULL’s differently	Decode handles without ‘is’ operator

Example:

Result:

         NULL_TES

         ——–

         NOT NULL

Decode handles without ‘is’ operator

Example:

Result:

             NULL

                —-

             NULL

72. How to identify whether string is only alpha numeric?

Ans:

Output: x

Output:

abc123 is alphanumeric

abc123@  not alphanumeric

Data Engineer :

73. What are different types of Schemas?

Ans:

• Star Schema
• Snow Flake schema

74. What is Flexi fields?

Ans:

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 Data store object for SAP and HIVE data integration tasks.

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

1. 1. Business Intelligence and Data Warehousing
2. 2. Data Migrations and Data Consolidations
3. 3. Master Data Management

76. 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. 1. Master Repository
2. 2. Work Repository

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

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

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

80. What is Yellow Interface?

Ans:

Yellow interface we are using for without target table and we can create dynamically target table if we don’t have target table. For this we need to enable the below condition at IKM level.

This yellow interface we can use for source table as well and we are calling Reusable mappings in 12c.

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

82. 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 don’t 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 separate 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 don’t  have source data that data only we will select and we will insert.

For this requirement we need two interfaces 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.

83. What is SCD?

Ans:

SCD Slowly changing dimension we are using for to load changing dimension information data. There are three types of SCD’same

SCD TYPE1:  No history, we will all columns SCD property as OVERWRITE ON CHANGE in ODI.

SCD TYPE2: we can use IKM Oracle Incremental update KM or we can IKM Slowly Changing Dimension both we can use.

SCD TYPE3:  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.

SCD TYPE 4:  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.

84. Components of Oracle Data Integrator?

Ans:

“Oracle Data Integrator” comprises of:

• Oracle Data Integrator + Topology Manager + Designer + Operator + Agent
• Oracle Data Quality for Data Integrator
• Oracle Data Profiling