Oracle Warehouse Builder

The product of Oracle Warehouse Builder (OWB) enters a product family of Oracle Developer Suite and represents the integrated CASE environment intended for development and deployment of storages and data marts. Means of this product it is possible to project, create and administer storages and data marts, to develop and generate procedures of extraction, conversion and data loading from different sources, to manage metadata. The Common Warehouse Model standards which are the cornerstone of a repository of Oracle Warehouse Builder provide its integration with different analytical work benches both Oracle, and other firms.

Sources and direct systems

OWB offers simple mechanisms for work with sources and tselevymy systems. Dialogue wizards simply allow to pass and conveniently necessary stages step by step. Can act as sources:

• RDBMS of Oracle;
• Flat files;
• ERP system of SAP R/3;
• DB2, Informix, MS SQL, Sybase (access through Oracle Transparent Server);
• ODBC sources;
• Mainframes (access through EDA SQL).

Built in OWB mechanisms allow to automatically determine changes in data sources and to display them in the repository of metadata.

OWB is the product which is deeply integrated into the Oracle DBMS platform. Proceeding from it, the main direct system is Oracle DBMS. The supported direct systems:

• Oracle DBMS;
• Flat files.

Design of ETL processes

After metadata of sources and the developed systems are described, the user can start creation of processes on data transfer in a direct system. Such processes of ETL in terminology of OWB are called processes of conversion (mappings). OWB provides different graphic design tools of procedures of conversion.

These means support conversions of a set of sources to a set of the purposes that allows the user to execute data translations consistently on a chain, using the complex PL/SQL conversions. For achievement of flexibility in manipulations with data, the user can define and build in the logical expressions using the built-in visual design tools of logical expressions. To reuse the processing procedures of data developed earlier, OWB allows to organize the user libraries of conversions. Procedures and functions of the user libraries it is possible to include at any time further in procedures of conversions or to perform before and after them.

In quality a component, making conversions, it is possible to use also the general join operators, filterings, aggregations and sortings of data. Later all these operators will automatically be transformed to the generated PL/SQL code. Also there are such operators, specific to ETL, as "search in a key" (key lookup), transaction over sets (union (all), minus, intersect) and a turn of tables (pivot and un-pivot). If the user selects the flat text file as data source, OWB automatically creates the managing file for loading through SQL*Loader. Design of flows of processes

OWB provides the mechanism of the organization of consolidation of the developed processes of conversions to the generalized flows of processes. Such flows of processes can manage the sequence of execution of processes, transfer parameters between processes, synchronize execution, perform processes depending on result of the previous transaction, perform branching of a flow by sign, connection of several flows in one and so forth. The user can develop completely ready mechanisms of loading, including notifications the message on the screen or by e-mail, verification of presence of the file in a source under the FTP protocol, etc.

Source code control

After design of storage and loading procedures is complete, automatically generated code can be placed in a direct system. This code can be the following types: SQL DDL for creation of the data warehouse; PL/SQL for processes of ETL of relational sources; Managing files of SQL*Loader for sources of flat files; The ABAP procedures for an izvelcheniye of data from SAP R/3 sources; For determination of control flows processes the code in the XML language.

Management of optimization of the source code

For management of optimization at generation of the source code following features can be used:

• Loading type selection – line by line or a rowset;
• Line code execution of PL/SQL;
• Manipulations from konstreynta;
• Fast loading from remote tables I use the built-in representations;
• Loading of changes on partition;
• Foreign tables;
• Table valued functions;
• Insert at the same time in several tables;
• Use of merge operator.

Management of deployment of the target scheme

For management of such variety of transactions with initial year as a part of OWB there is a built-in Deployment Manager utility.

Deployment Manager provides the unified interface for creation and object management and procedures of the developed storage. Using Deployment Manager it is possible to create, update, delete objects from the target scheme of storage equally regardless of the specific code. Also Deployment Manager allows to start on accomplishment of the procedure of conversions as separately, and as a part of flows of processes.