300+ Oracle Warehouse Builder Interview Questions and Answers

Oracle Warehouse Builder Interview Questions for freshers experienced :-

1. 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. 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. 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. 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. 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. 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. 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. What are the meta-data security strategies in OWB?

Security strategies in OWB includes

  1. Minimal Meta-data Security Strategy
  2. Multi-user Security Strategy
  3. 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
Oracle Warehouse Builder Interview Questions
Oracle Warehouse Builder Interview Questions

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

Oracle Warehouse Builder Questions and Answers Pdf Download

300+[LATEST] Oracle Warehouse Builder Interview Questions and Answers

Q1. What Sources Are Supported Through Oracle Pure Extract?

Oracle Pure Extract supports native access to OS/390 platforms: 

  • IMS 
  • DB2 
  • VSAM 
  • Sequential files

Q2. What Is The Oracle Warehouse Builder Repository?

The OWB repository is a server-based, low maintenance, powerful metadata repository based on the openstandard  Common Warehouse Model (CWM). The repository is the central nervous system of the  Enterprise Business Intelligence solution provided. All design information regarding sources,  trformations and warehouses is available and can be viewed over the web, utilizing Oracle Portal  technology. This includes lineage and impact analysis throughout the warehouse.

Q3. Does Owb Support The Clob And Long Data Types?

No, OWB currently doesn’t support these data types.

Q4. Which Sources Are Supported By Owb?

OWB supports the following source environments: 

  • Oracle RDBMS, versions 7.3.4 and higher 
  • Flat files (character-delimited, fixed-length, single-record and multi-record) 
  • SAP/R3 (2.1.1.34 supports 3.1X, 4.0X, 4.5X, 4.6A, 4.6B) 
  • XML documents (URL, Advanced Queues, documents etc.) 
  • Generic Connectivity (ODBC) 
  • Oracle Trparent Gateways (see next question for a more detailed list)

Q5. What Is Oracle Warehouse Builder?

Oracle Warehouse Builder (OWB) is a framework for creation of extraction, trformation, and loading  scripts for populating an operational data store, a data mart or a data warehouse. OWB also includes a  component to design the target data warehouse / data mart / operational data store. The design component  can be used to create data warehouses designed as star schema (including aggregations / summary levels  with multiple hierarchies) or warehouses / operational data stores as third normal form.

Q6. Can Owb Generate Artificial Keys?

Yes, depending on the implementation language, OWB uses either sequences (PL/SQL implementation) or a data generator (SQL*Loader implementation) to product these artificial keys.

Q7. Does Owb Support Dimensional Targets?

Yes, OWB has a wizard-driven design process for both dimensions and for facts. Both can be viewed and printed in a diagramming mode.

Q8. Can The Generated Code Be Modified?

Yes, OWB generates code, which can be deployed into the database and it can be deployed into scripts  (text files). These text files can be modified and then deployed to the database with the changes. Note that  these changes (if made outside of OWB) are not stored in the repository, and that problems with modified  scripts are not supported by Oracle.

Q9. Does Owb Support 3rd Normal Form Targets?

Yes, OWB let’s you define 3rd normal form schemas with the wizards guiding you through the table creation  process. It also provides a simple wizard to design and define keys between the tables.

Q10. Does The Sap Installation Have To Be On An Oracle Database?

No, the OWB integrator solution for SAP is not depended on the underlying database. SAP installed on a non Oracle database (Informix, DB2, SQL Server or Sybase) can be accessed with the OWB SAP integrator.

Q11. Which Warehouse Platforms Does Owb Support?

OWB is tuned and build to fully utilize the market leading-Oracle database. OWB is a code generation tool  and uses the Oracle database as its trformation engine. The client software must be installed on either a  Windows 2000 or NT 4.0 client

Q12. Does Owb Support Multiple Source Objects In One Mapping?

Yes, the user can add source objects to a mapping and join these via a joiner. If the tables are connected by key relationships OWB picks these up automatically and populates the join condition without the user  having to open the joiner and type this in.

Q13. How Does Oracle Warehouse Builder Facilitate Etl And Warehouse Design?

OWB is a code generation tool based on a metadata repository, meaning that the design is trlated  internally by OWB, which results in the generation of code. This code can then be used to create a data  warehouse and the accompanying data trformations.

Q14. Is There A Standard Time Dimension In Owb?

OWB delivers a time dimension based on the Gregorian calendar. The user can determine the levels in this  dimension and choose from a number of pre built hierarchies.

Q15. Is Prototyping Supported By Owb?

Due to OWB’s graphical nature, prototypes can be built rapidly. OWB provides a graphical design tool for  logical representations of star schema and third-normal-form target databases. It contains automated  validation of source to target mappings, and automated code generation of code. SQL DDL for building  target tables, SQL*Loader control files for loading data from flat files, PL/SQL for extraction and  trformation and TCL to enable registration in Oracle Enterprise Manager.

Q16. How Easy Is It To Create The Warehouse Schema With Owb?

OWB gives the user a wizard-driven design environment to create the dimensional warehouse schema. Wizards guide the user through the creation of dimensions with multiple levels and hierarchies, the creation  of keys on these levels, and the linking of these dimensions to the fact tables. OWB provides wizards for all  database objects supported in OWB.