Oracle Warehouse Builder Interview Q & A
Part 1:
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.
Part 2:
Q 1. Can Owb Generate Artificial Keys?
Ans:
Yes, relying at the implementation language, OWB makes use of either sequences (PL/SQL implementation) or a data generator (SQL*Loader implementation) to product these synthetic keys.
Q 2. Can The Generated Code Be Modified?
Ans:
Yes, OWB generates code, which may be deployed into the database and it could be deployed into scripts (textual content documents). These text files can be changed after which deployed to the database with the modifications. Note that those changes (if made outdoor of OWB) are not stored within the repository, and that troubles with modified scripts are not supported via Oracle.
Q 3. Does Owb Support Multiple Source Objects In One Mapping?
Ans:
Yes, the user can upload source gadgets to a mapping and join those through a joiner. If the tables are related through key relationships OWB picks these up robotically and populates the be a part of circumstance without the user having to open the joiner and type this in.
Q 4. Is There A Standard Time Dimension In Owb?
Ans:
OWB provides a time dimension primarily based at the Gregorian calendar. The consumer can determine the ranges in this dimension and pick from some of pre constructed hierarchies.
Q 5. Does Owb Support 3rd Normal Form Targets?
Ans:
Yes, OWB permit’s you outline 3rd normal shape schemas with the wizards guiding you thru the table creation technique. It also gives an easy wizard to design and outline keys among the tables.
Q 6. Does Owb Support Dimensional Targets?
Ans:
Yes, OWB has a wizard-pushed layout system for both dimensions and for data. Both may be regarded and printed in a diagramming mode.
Q 7. Which Warehouse Platforms Does Owb Support?
Ans:
OWB is tuned and construct to absolutely make use of the marketplace leading-Oracle database. OWB is a code technology tool and uses the Oracle database as its transformation engine. The purchaser software program should be mounted on both a Windows 2000 and NT 4.0 purchaser
Q 8. Does The Sap Installation Have To Be On An Oracle Database?
Ans:
No, the OWB integrator Ans for SAP isn’t always trusted the underlying database. SAP established on a non-Oracle database (Informix, DB2, SQL Server or Sybase) can be accessed with the OWB SAP integrator.
Q 9. Does Owb Support The Clob And Long Data Types?
Ans:
No, OWB presently doesn’t guide these facts sorts.
Q 10. What Sources Are Supported Through Oracle Pure Extract?
Ans:
Oracle Pure Extract supports local get admission to to OS/390 structures:
IMS
DB2
VSAM
Sequential files
Q 11. Which Sources Are Supported By Owb?
Ans:
OWB helps the following supply environments:
Oracle RDBMS, versions 7.3.4 and better
Flat files (man or woman-delimited, fixed-period, single-file and multi-file)
SAP/R3 (2.1.1.34 helps 3.1X, four.0X, four.5X, 4.6A, 4.6B)
XML files (URL, Advanced Queues, documents and so on.)
Generic Connectivity (ODBC)
Oracle Transparent Gateways (see next Q for a greater particular list)
Q 12. Is Prototyping Supported By Owb?
Ans:
Due to OWB’s graphical nature, prototypes can be built swiftly. OWB gives a graphical layout tool for logical representations of megastar schema and third-regular-form target databases. It contains automatic validation of supply to goal mappings, and automatic code generation of code. SQL DDL for constructing target tables, SQL*Loader control files for loading statistics from flat documents, PL/SQL for extraction and transformation and TCL to permit registration in Oracle Enterprise Manager.
Q 13. How Easy Is It To Create The Warehouse Schema With Owb?
Ans:
OWB offers the person a wizard-driven design surroundings to create the dimensional warehouse schema. Wizards guide the person thru the introduction of dimensions with more than one ranges and hierarchies, the creation of keys on these stages, and the linking of these dimensions to the reality tables. OWB gives wizards for all database gadgets supported in OWB.
Q 14. How Does Oracle Warehouse Builder Facilitate Etl And Warehouse Design?
Ans:
OWB is a code generation tool primarily based on a metadata repository that means that the design is translated internally by OWB, which ends in the generation of code. This code can then be used to create a facts warehouse and the accompanying facts changes.
Q 15. What Is The Oracle Warehouse Builder Repository?
Ans:
The OWB repository is a server-based, low protection, powerful metadata repository based totally at the open standard Common Warehouse Model (CWM). The repository is the valuable nervous system of the Enterprise Business Intelligence Ans provided. All design records concerning sources, adjustments and warehouses is available and can be considered over the internet, utilizing Oracle Portal generation. This includes lineage and impact analysis at some point of the warehouse.
Q 16. What Is Oracle Warehouse Builder?
Ans:
Oracle Warehouse Builder (OWB) is a framework for advent of extraction, transformation, and loading scripts for populating an operational statistics save, a facts mart or a data warehouse. OWB also includes an element to design the goal facts warehouse / statistics mart / operational facts save. The design component can be used to create facts warehouses designed as celebrity schema (together with aggregations / precis degrees with a couple of hierarchies) or warehouses / operational facts stores as 0.33 normal form.