| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
Oracle Warehouse Builder provides enterprise solutions for end-to-end data integration. This chapter introduces you to the range of functionality provided by Warehouse Builder.
This chapter includes the following topics:
Oracle Warehouse Builder is a single, comprehensive tool for all aspects of data integration. Warehouse Builder leverages Oracle Database to transform data into high-quality information. It provides data quality, data auditing, fully integrated relational and dimensional modeling, and full lifecycle management of data and metadata. Warehouse Builder enables you to create data warehouses, migrate data from legacy systems, consolidate data from disparate data sources, clean and transform data to provide quality information, and manage corporate metadata.
Many global corporations have data dispersed on different platforms using a wide variety of data reporting and analysis tools. Customer and supplier data may be stored in applications, databases, spreadsheets, flat files, and legacy systems. This diversity may be caused by organizational units working independently over a period of time, or it may be the result of business mergers. Whatever the cause of diversity, this diversity typically results in poor quality data that provides an incomplete and inconsistent view of the business.
Transforming poor quality data into high quality information requires:
Access to a wide variety of data sources
Warehouse Builder leverages Oracle Database to establish transparent connections to numerous third-party databases, applications, files, and data stores as listed in "Supported Sources and Targets".
Ability to profile, transform, and cleanse data
Warehouse Builder provides an extensive library of data transformations for data types such as text, numeric, date, and others. Use these transformations to reconcile the data from many different sources as described in "Introducing Oracle Warehouse Builder Transformations".
Before loading data into a new data store, you can optionally profile the data to evaluate its quality and appropriateness. Subsequently, you can match and merge records using rules that you devise. You can validate name and address data against postal databases. This process of changing poor quality data into high quality information is introduced in "About the Data Quality Management Process".
Ability to implement designs for diverse applications
Using Warehouse Builder, you can design and implement any data store required by your applications, whether relational or dimensional. The process of designing your data store is described in "Designing Target Schemas".
Audit trails
After consolidating data from a variety of sources into a single data store, you are likely to face the challenge of verifying the validity of the output information. For instance, can you track and verify how a particular number was derived? This is a question often posed by decision makers within your organization and by government regulators.
A significant portion but not all of the Warehouse Builder features are included in Oracle Database editions at no additional cost and enable you to design, deploy, and manage a basic Oracle data warehouse. If you intend to extract from applications or intend to perform data profiling or advanced Extraction, Transform, and Load (ETL) processes, consider licensing additional options available only with the Oracle Database Enterprise Edition.
Table 1-1 can help you understand the difference between the options and determine the combination of database edition and Warehouse Builder options that addresses your requirements. The table lists the features available in Oracle Database Standard Edition One (SE1), Standard Edition (SE), and Enterprise Edition (EE). The Y value in a column indicates that the feature is available in the specified release; N indicates that it is not available.
Note:
Depending on how you utilize Warehouse Builder, you may require licenses for additional database options and, or technologies such as Oracle Partitioning, Oracle OLAP, and Oracle Transparent Gateways.Table 1-1 Warehouse Builder Options Availability in Oracle Database Editions
| Option/ Feature | SE1 | SE | EE | Comments |
|---|---|---|---|---|
|
Warehouse Builder Core Functionality |
Y |
Y |
Y |
Enables the design, deployment, execution, and management of common data integration or data warehouse projects. |
|
Warehouse Builder Enterprise ETL Option |
N |
N |
Y |
Enables higher developer productivity in (larger) projects. Also allows for reuse of transformation logic and for certain fast extraction methods in large volume data movements. |
|
Warehouse Builder Data Quality Option |
N |
N |
Y |
Enables profiling of data to detect information quality issues in the source. Once the issues are documented, developers can generate business rules and automatically cleanse data using these business rules in the data integration process. In addition to this, the Data Quality option allows monitoring of quality on a regular basis using methods such as Six Sigma. |
|
Warehouse Builder Connector - E-Business Suite |
N |
N |
Y |
Enables access to technical and business metadata within Oracle E-Business Suite. Facilitates deployment to Oracle Concurrent Manager and access to Oracle E-Business Suite at execution-time. |
|
Warehouse Builder Connector - PeopleSoft |
N |
N |
Y |
Enables access to data and metadata in PeopleSoft applications. |
|
Warehouse Builder Connector - SAP R/3 Connector |
N |
N |
Y |
Enables uploading of generated ABAP code to the SAP system and executing ABAP programs from the Control Center Manager. For production systems, it allows the execution of registered ABAP programs from process flows. |
|
Warehouse Builder Connector - Siebel |
N |
N |
Y |
Enables access to data and metadata in Siebel applications. |
The core Oracle Warehouse Builder functionality enables Extraction, Transformation, and Loading (ETL) of data from heterogeneous sources into heterogeneous targets. You can load data into relational, multidimensional, flat file, and XML storage systems.
If you licensed and used earlier versions of this product, note that the core functionality equates to the functionality available in Oracle Warehouse Builder 10g Release 1.
The core Warehouse Builder functionality is included in the Oracle Database license at no additional cost. If a feature is not specifically mentioned in one of the following options, you can safely assume that the feature is part of the core functionality:
Warehouse Builder Enterprise ETL Option
Warehouse Builder Data Quality Option
Warehouse Builder Connector - E-Business Suite
Warehouse Builder Connector - PeopleSoft
The Enterprise ETL option enables large-scale, complex ETL deployments. Developers can incorporate advanced functionality, such as retaining history for dimensions, reusing mapping code, performing interactive lineage and impact analysis, and defining custom types of objects in a repository. This option also enables the rapid movement of large amounts of data, and the construction of advanced process flows.
Table 1-2 lists the functionality available with the Enterprise ETL option. The functionality is grouped into areas. For example, the area Schema Modeling includes functionality for slowly changing dimensions and business intelligence.
Table 1-2 Warehouse Builder Enterprise ETL Option
| Area and Functionality | Comments |
|---|---|
|
Schema Modeling |
Available in the Data Object Editor. |
|
Slowly changing dimensions |
Includes support for Slowly Changing Dimension (SCD) types 2 and 3. |
|
Sources and Targets |
Available in the Design Center |
|
XML file as target |
Supported through the flat file operator. |
|
ETL Design |
Available in the Mapping, Process Flow, and Schedule Editors |
|
Advanced ETL features |
Includes the following ETL features: transportable modules, multiple configurations, and pluggable mappings. Includes the following operators associated with reusing mapping code: pluggable mapping, pluggable mapping input signature, pluggable mapping output signature. |
|
Real Applications Cluster (RAC) support |
Includes maintaining the Warehouse Builder design environment in a Real Applications Cluster environment. Without the Enterprise ETL option, you can install the Warehouse Builder repository in a Real Applications Cluster environment for the limited purposes of executing in that environment. |
|
Mapping operators |
Includes the operators for handling complex types: varray iterator, construct object, and expand object. |
|
Target load ordering |
For mappings with multiple targets, includes functionality to specify the order in which the targets are loaded. |
|
Transformations |
Seeded Spatial and Streams transformations. |
|
Process flows |
Includes the following advanced process flow functionality:
The Data Auditor activity requires the Warehouse Builder Data Quality Option. |
|
Metadata Management |
Available in the Design Center |
|
Lineage and impact analysis |
Includes interactive analysis available in the Design Center. |
|
Change propagation |
Includes automatic propagation of property changes to impacted objects through the Lineage and Impact Analyzer. |
|
Extensibility |
Includes project based and public based user-defined objects, user-defined associations, and user-defined modules. Includes creating icon sets and assigning custom icons to objects. |
|
Deployment and Execution |
Available in the Control Center Manager |
|
Schedules |
Includes functionality to model schedules for mappings and process flows. |
|
Business intelligence deployment targets |
Includes direct deployment to the Discoverer End User Layer (EUL). |
The Data Quality option enables you to convert raw data into quality information. Developers and data librarians can gain insight into their data and identify previously unknown data quality problems. Subsequently, developers can define rules and generate mappings that correct the data. Based on the data rules, developers can also create data auditors to ensure the quality of incoming data on a repeated basis.
Table 1-3 lists the Warehouse Builder functionality available in the Data Quality option.
Table 1-3 Warehouse Builder Functionality in the Data Quality Option
| Area and Functionality | Comments |
|---|---|
|
Data Profiling |
Available in the Data Profile Editor and the Mapping Editor |
|
Data profiling |
Includes functionality for data profiling and data drill-down. |
|
Data rules |
Includes functionality for data rule derivation and data rule profiling. Includes support for custom and predefined data rules and support for apply data rules to data objects. |
|
Data corrections |
Includes the generation of mappings that correct data based on data rules. |
|
ETL Design |
Available in the Process Flow Editor |
|
Process flows |
Includes the use of the Data Auditor Monitor activity in the Process Flow Editor. |
The Warehouse Builder Connector to E-Business Suite provides access to the technical and business metadata within Oracle E-Business Suite. Subsequently, you can build mappings and process flows that either source or target Oracle E-Business Suite. The connector also facilitates deployment to Oracle Concurrent Manager and access to Oracle E-Business Suite at execution-time.
With the E-Business Suite Connector for Warehouse Builder, you can use the functionality listed in Table 1-4 in addition to the Warehouse Builder Core Functionality.
Table 1-4 Warehouse Builder Functionality in the E-Business Suite Connector
| Area and Functionality | Comments |
|---|---|
|
Metadata Management |
Available in the Design Center |
|
Oracle E-Business Suite |
Includes access to technical and business metadata in E-Business Suite. |
|
ETL Design |
Available in the Mapping Editor and Process Flow Editor |
|
ETL support |
Enables the inclusion of E-Business Suite data objects into mappings and process flows. |
|
Deployment and Execution |
Available in the Control Center Manager |
|
Deploying ETL objects |
Includes deploying mappings and process flows designed with E-Business Suite objects. |
|
Deployment targets |
Includes deployment to Oracle Concurrent Manager. This also available in the Warehouse Builder Enterprise ETL Option. |
With Warehouse Builder Connector to PeopleSoft, you can connect to and then extract data and metadata from PeopleSoft applications. The connection to the PeopleSoft application using database users with the appropriate privileges set by the DBA.
After you import metadata from PeopleSoft applications, you can work with packaged applications as you would with other SQL based systems. You can include PeopleSoft objects as sources or targets in Warehouse Builder mappings, create process flows, and generate SQL code.
This connector can operate with non-Oracle databases after you establish a connection to those databases. Table 1-5 lists the functionality available in the Warehouse Builder Connector to PeopleSoft.
Table 1-5 Warehouse Builder Functionality in the PeopleSoft Connector
| Area and Functionality | Comments |
|---|---|
|
Metadata Management |
Available in the Design Center |
|
PeopleSoft |
Includes access to technical and business metadata in PeopleSoft. |
|
ETL Design |
Available in the Mapping Editor and Process Flow Editor |
|
ETL support |
Enables the inclusion of PeopleSoft objects into mappings and process flows. |
|
Deployment and Execution |
Available in the Control Center Manager |
|
Deploying ETL objects |
Includes deploying mappings and process flows designed with PeopleSoft objects. |
With the Warehouse Builder Connector to SAP R/3, you can connect to and then extract data and metadata from SAP R/3. You can access both the technical and business metadata in the SAP R/3 application. The connector masks the complexities of the SAP metadata by displaying pool tables and cluster tables as regular tables. To access SAP metadata, you use an RFC call with a SAP GUI account as authentication.
After you import SAP metadata and understand relationships, you can use the SAP objects like any other objects in Warehouse Builder. You can include SAP R/3 objects in Warehouse Builder mappings and process flows and generate ABAP code. The connector allows direct deployment and execution of ABAP in SAP and execution of generated and uploaded ABAP from production process flows. The connector also supports the use of substitution variables to facilitate transporting of ABAP code between development and production systems by the SAP administrator. Variable support in ABAP also allows you to easily create change data capture flows, either based on dates or based on ID ranges.
Table 1-6 lists the functionality available in the Warehouse Builder Connector to SAP R/3.
Table 1-6 Warehouse Builder Functionality in the SAP R/3Connector
| Area and Functionality | Comments |
|---|---|
|
Metadata Management |
Available in the Design Center |
|
SAP R/3 |
Includes access to technical and business metadata in SAP R/3. |
|
ETL Design |
Available in the Mapping Editor and Process Flow Editor |
|
ETL support |
Enables the inclusion of SAP R/3 objects in mappings and process flows. Generates ABAP code. Includes parameterization and tuning of ABAP code. To enable migration between SAP R/3 environments such as development versus production environments, this connector supports the use of substitution variables to facilitate transporting ABAP code. |
|
Deployment and Execution |
Available in the Control Center Manager |
|
Deploying ETL objects |
Includes deploying mappings and process flows designed with SAP R/3 objects. |
The Warehouse Builder Connector to Siebel enables you to connect to and extract data and metadata from Siebel applications. The connection to the Siebel applications is using database users with the appropriate privileges set by the DBA.
After you import metadata from Siebel applications, you can use Siebel objects in mappings, create process flows containing Siebel objects, and generate SQL code.
You can use this connector with non-Oracle databases after you establish a connection to those databases. Table 1-7 lists the functionality available in the Warehouse Builder Connector to Siebel.
Table 1-7 Warehouse Builder Functionality in the Siebel Connector
| Area and Functionality | Comments |
|---|---|
|
Metadata Management |
Available in the Design Center |
|
Siebel |
Includes access to technical and business metadata in Siebel. |
|
ETL Design |
Available in the Mapping Editor and Process Flow Editor |
|
ETL support |
Enables the inclusion of Siebel objects into mappings and process flows. |
|
Deployment and Execution |
Available in the Control Center Manager |
|
Deploying ETL objects |
Includes deploying mappings and process flows designed with Siebel objects. |
