Top 16 Oracle Warehouse Builder (OWB) Interview Questions & Answers

1) Mention what is oracle warehouse builder?

Oracle warehouse builder is an ETL tool produced by Oracle that provides a graphical environment to create, maintain and manage data integration process in business intelligence systems.

2) Mention what are the new features of oracle OWE-EE 11gR2?

The new features of oracles OWE-EE 11gR2 includes

• Code template mappings using ODI knowledge modules
• Native heterogeneous connectivity
• Charged data capture mappings
• Advanced queue support in mappings
• SOA integration publishing and consuming web services

3) Mention what are the enhancements made in OWB 11gR2?

In OWB 11gR2 enhancements made includes

• Improved support for watch points
• Disabling and enabling of individual break points
• Support for user-defined type columns
• Improved support for numerous operators such as Array, Expand and Construct
• Support for table function operators and key lookup
• Support for correlated joints
• Enhanced cleanup of debugger specific objects

4) Mention what are the important components of Oracle warehouse builder?

The oracle warehouse builder is comprised of two section Client side and Server side

• Client side: Design Center and Repository browser
• Server side: Control center service, Workspace, Warehouse Builder Repository and Target Schema (Oracle database)

5) Mention what is the general steps for importing Metadata from sources?

For importing meta-data from sources

• Review the list of supported targets and sources
• Create a location for imported data
• Create a module for the source meta-data as described in “creating modules”
• Right click the module and select import
• Follow the instruction in the import data wizard
• For oracle data objects, view the data saved in the data object using the data viewer. Right click the object and select Data

6) Mention what are the types of locations you can deploy data in OWB?

You can deploy data to several different types of locations like

• Databases: Targets for either dimensional or relational business intelligence systems, including objects such as views and tables, or cubes and dimensions
• Files: Target for storing data in XML format or in comma-delimited
• Applications: Targets for SAP systems
• Process flows and schedules: Targets for managing ETL
• Business Intelligence: Targets for meta-data derived from oracle modules or databases

7) Explain how you can design objects in the project in OWB (Oracle Warehouse Builder)?

Within a warehouse builder workspace, PROJECTS are the largest storage object.  You have to include all the objects in a project that you think you can or will share information.  This definition includes data objects, mappings and transformation operations.

8) How can you delete a project in OWB?

To delete a project in OWB there are some restriction as projects are the primary design components. You cannot delete the projects that are currently active or the only project in the workspace.  In order to delete a project,

• Collapse the project you want to delete
• Choose and expand any other project
• Trace the project you want to delete and from the Edit menu, select DELETE or right click and select delete
• Click OK to delete the project

9) Mention what are the meta-data security strategies in OWB?

Security strategies in OWB includes

• Minimal Meta-data Security Strategy
• Multi-user Security Strategy
• Full Meta-data Security Strategy

10) In oracle data-base what does target schema consist of?

Target schema consists of

• Generated Code
• Cubes
• Dimensions
• Tables
• Views
• Mapping
• Packages to execute ETL processes

11) Mention how connectors work in OWB?

A connector is a logical link formed by a mapping between the target location and a source location.  The connector between schemas in two distinct oracle databases is executed as a database link, and the connector between a schema and an operating system directory is executed as a database directory.  You don’t need to create connectors manually if your user ID has the credential for creating these database objects. OWB will make them automatically, the first time you deploy the mapping.

12) Explain what is modules in OWB?

Modules are grouping activity or mechanism in the project explorer that corresponds to locations in the connection explorer.  A single location can address to one or more modules.  However, a given module can relate to only a single location at a time.

13) Explain how you can import Meta-data form flat files?

To import meta-data from flat files, you have to create locations that reference directories in which source data is stored. Number of locations depend upon the number of drives and directories that have flat files stored in it.  Now, in the project explorer, right click the files node and choose NEW to create a new module.  Repeat this for each of the directories having flat files. Now for each of the modules created, select import.  A wizard poinyd you on how to import one or more files into each module.

14) Mention what are the types of activities does OWB includes?

OWB includes following types of activities

• Oracle Warehouse Builder Specific Activities: These activities allows you to start oracle warehouse builder objects such as mapping, transformation or other process flows. The process flow executes  the objects and provides a committed statement
• Utility Activities: These activities allows you to execute services such as transferring e-mails and files
• Control Activities: These activities allows you to control the advancement and direction of the process flow. For example, use the fork activity to run multiple activities concurrently

15) Mention what is the role of Match-Merge Operator in OWB?

The match-merger operator in OWB allows you to identify matching records and merge them into a single record.  Thus eliminating duplicate records from the data.

Match Merge operator allows you to

• Use weights to identify matches between records
• Identifies matches using built-in algorithms, including edit distance algorithms and Jaro-Winkler
• Cross reference data to audit and track matches
• Create custom rules combining built in rules for merging and matching

16) Explain how debugging of mapping can be done in OWB?

Each source or target operator must be bound or attached to a database object and test data must be identified for the database object.  Debugging of mapping consists of two important steps

• Define the test data
• And to run one step at a time of mapping

Once you have determined the test data connections for each of the data operators, you can initialize the debug code by selecting re-initialize from the debug menu or by selecting the Re-initialize button on the tool-bar.