Oracle Warehouse Builder
Encyclopedia
Oracle Warehouse Builder (OWB) is an ETL
tool produced by Oracle
that offers a graphical environment to build, manage and maintain data integration
processes in business intelligence
systems.
and data migration
from legacy system
s. Further it offers capabilities for relational
, dimensional
and metadata
data modeling
, data profiling
, data cleansing
and data auditing
. Whereas the core functionality is part of the Oracle database
since version 10gR2, some of the latter features are sold separately as options. OWB uses a variant of Tcl
over Java
and PL/SQL
called OMB+.
The packaging as part of the Oracle Developer Suite
ended in May 2006 with the release of OWB 10gR2 (10g Release 2), when the core functions were included in Oracle 10gR2
Standard Edition and Enterprise Edition.
With the introduction of Oracle 11g in July 2007 the OWB version was updated to the current 11gR1 (11g Release 1).
Version 11.2 (11g Release 2) was released with the 11gR2 Oracle Database
in September 2009. It will feature improved usability, support for Oracle OBI EE, and native access to an extensible set of non-Oracle platforms using customizable and user-definable code templates.
in 2006. In the short term, OWB and ODI will continue to be released independently, with each release being closer to integrating the two products. The Warehouse Builder 11.2 code template support is in fact derived from and largely compatible with the Oracle Data Integrator Knowledge Module framework. Oracle has publicly commented on plans to release a data integration product that will unify features from both of the current offerings.
Use older versions of Oracle documentation
Think lists
Extract, transform, load
Extract, transform and load is a process in database usage and especially in data warehousing that involves:* Extracting data from outside sources* Transforming it to fit operational needs...
tool produced by Oracle
Oracle Corporation
Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems...
that offers a graphical environment to build, manage and maintain data integration
Data integration
Data integration involves combining data residing in different sources and providing users with a unified view of these data.This process becomes significant in a variety of situations, which include both commercial and scientific domains...
processes in business intelligence
Business intelligence
Business intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....
systems.
Features
The primary use for OWB is consolidation of heterogeneous data sources in data warehousingData warehouse
In computing, a data warehouse is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.A data warehouse...
and data migration
Data migration
Data migration is the process of transferring data between storage types, formats, or computer systems. Data migration is usually performed programmatically to achieve an automated migration, freeing up human resources from tedious tasks...
from legacy system
Legacy system
A legacy system is an old method, technology, computer system, or application program that continues to be used, typically because it still functions for the users' needs, even though newer technology or more efficient methods of performing a task are now available...
s. Further it offers capabilities for relational
Relational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
, dimensional
Dimensional modeling
Dimensional modeling is the name of a set of techniques and concepts used in data warehouse design. It is considered to be different from entity-relationship modeling . Dimensional Modeling does not necessarily involve a relational database. The same modeling approach, at the logical level, can be...
and metadata
Metadata modeling
Metadata modeling is a type of metamodeling used in software engineering and systems engineering for the analysis and construction of models applicable and useful some predefined class of problems....
data modeling
Data modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.- Overview :...
, data profiling
Data profiling
Data profiling is the process of examining the data available in an existing data source and collecting statistics and information about that data...
, data cleansing
Data cleansing
Data cleansing, data cleaning, or data scrubbing is the process of detecting and correcting corrupt or inaccurate records from a record set, table, or database. Used mainly in databases, the term refers to identifying incomplete, incorrect, inaccurate, irrelevant, etc...
and data auditing
Data auditing
Data auditing is the process of conducting a data audit to assess how company's data is fit for given purpose. This involves profiling the data and assessing the impact of poor quality data on the organization's performance and profits....
. Whereas the core functionality is part of the Oracle database
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
since version 10gR2, some of the latter features are sold separately as options. OWB uses a variant of Tcl
Tcl
Tcl is a scripting language created by John Ousterhout. Originally "born out of frustration", according to the author, with programmers devising their own languages intended to be embedded into applications, Tcl gained acceptance on its own...
over Java
Java (programming language)
Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...
and PL/SQL
PL/SQL
PL/SQL is Oracle Corporation's procedural extension language for SQL and the Oracle relational database...
called OMB+.
History
Oracle Warehouse Builder was built from the ground up in Oracle, it was first released in January 2000 (release 2.0.4). The 3i release significantly enhanced the ETL mapping designer, then 9i in 2003 introduced the mapping debugger, process flow editing, integrated match/merging and name/address cleansing, multi-table insert, scripting, RAC certification to name a few. The 10gR1 release was essentially a certification of the 10g database, and the 10gR2 release (code named Paris) was a huge release incorporating a wide spectrum of functionality from dimensional modelling to data profiling and quality. The OWB 11gR1 release was a move into the database release stack, and included the server components being installed with the database and MDM connectors.The packaging as part of the Oracle Developer Suite
Oracle Developer Suite
Oracle Developer Suite is a suite of development tools released by the Oracle Corporation. The principal components were initially Oracle Forms and Oracle Reports, although the suite was later expanded to include JDeveloper amongst others.-History:...
ended in May 2006 with the release of OWB 10gR2 (10g Release 2), when the core functions were included in Oracle 10gR2
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
Standard Edition and Enterprise Edition.
With the introduction of Oracle 11g in July 2007 the OWB version was updated to the current 11gR1 (11g Release 1).
Version 11.2 (11g Release 2) was released with the 11gR2 Oracle Database
Oracle Database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
in September 2009. It will feature improved usability, support for Oracle OBI EE, and native access to an extensible set of non-Oracle platforms using customizable and user-definable code templates.
Future integration with Oracle Data Integrator
, Oracle plans to integrate OWB with Oracle Data Integrator (ODI), an ETL tool Oracle acquired when it took over SunopsisSunopsis
Sunopsis is software company based near Lyon, France. It also has a United States headquarters in Burlington, Massachusetts. The company was bought by Oracle in October 2006....
in 2006. In the short term, OWB and ODI will continue to be released independently, with each release being closer to integrating the two products. The Warehouse Builder 11.2 code template support is in fact derived from and largely compatible with the Oracle Data Integrator Knowledge Module framework. Oracle has publicly commented on plans to release a data integration product that will unify features from both of the current offerings.
OMB+
Script everything.Use older versions of Oracle documentation
Think lists
OMBCREATE PROJECT 'TEST_PROJECT';
OMBCREATE ORACLE_MODULE 'MOD1';
OMBCREATE MAPPING 'MAP1';
OMBALTER MAPPING 'MAP1' \
ADD TABLE OPERATOR 'EMP_1' \
BOUND TO TABLE '/TEST_PROJECT/SOURCE_MODULE/EMP';
OMBALTER MAPPING 'MAP1' \
ADD CONNECTION \
FROM ATTRIBUTE 'EMPNO' OF GROUP 'INOUTGRP1' OF OPERATOR 'EMP_1' \
TO ATTRIBUTE 'X' OF GROUP 'INOUTGRP1' OF OPERATOR 'DEST_EMP';
set LIST_OF_OPERATORS [OMBRETRIEVE MAPPING 'MAP1' GET OPERATORS];
foreach THIS_OPERATOR $LIST_OF_OPERATORS {
puts $THIS_OPERATOR;
}
set LIST_OF_ATTRIBUTES [OMBRETRIEVE MAPPING 'MAP1' GROUP 'INOUTGRP1' GET ATTRIBUTES];
foreach THIS_ATTR $LIST_OF_ATTRIBUTES {
# Use attribute name from list
OMBALTER MAPPING 'MAP1' \
ADD CONNECTION \
FROM ATTRIBUTE '$THIS_ATTR' OF GROUP 'INOUTGRP1' OF OPERATOR 'EMP_1' \
TO ATTRIBUTE '$THIS_ATTR' OF GROUP 'INOUTGRP1' OF OPERATOR 'DEST_EMP';
}