|
The first step in building a successful
Business Intelligence (BI) infrastructure is effective consolidation
of the data across all transactional systems. Once the data
is consolidated, users can perform an array of reporting,
ad-hoc querying, and sophisticated analysis. A central data
warehouse guarantees a consistent view across the enterprise,
ensuring accurate decision-making.
Oracle9i Warehouse Builder is a tool for designing,
deploying and managing enterprise data warehouses, data marts
and e-Business intelligence applications. Oracle9i
Warehouse Builder includes the following capabilities: Dimensional
design, Extract Transform and Load (ETL) process design, extraction
from disparate source systems, extensive metadata reporting
and integration with Oracle 9iAS Discoverer, Oracle
Workflow and Oracle Enterprise Manager. Oracle Warehouse Builder
is a component of the Oracle9i Developer Suite.
The previous daily feature focused on the design capabilities.
This daily feature focuses on the ETL process.
Designing
the ETL processes
Once the metadata for the sources is captured
in Oracle9i Warehouse Builder, users can start creating
the processes to move the data into the target schema. These
ETL processes are called mappings in Oracle Warehouse Builder.
Mapping
Sources and Targets
Oracle Warehouse Builder provides the user with a graphical
environment to model the logical ETL process. This tool supports
mapping multiple sources into multiple targets. It allows
users to specify chained transformations on the data flow
and apply complex PL/SQL transformations to the data. To provide
optimal flexibility users can define their own inline expressions
using a point and click Expression Builder with validation
capabilities. To enable reuse (of legacy code but also of
newly developed code), a transformation library is available
in which PL/SQL code is stored. This code can then be used
within mappings or ran in pre or post mapping processes. To
ensure a high standard in usability, the mapping component
allows for automated mapping between sources and targets.
Users can create new mapping objects using auto mapping, or
map by name or position to create mappings in a quick and
efficient manner.
The mapping tool also allows you to perform common actions
as joining, filtering, aggregating and ordering data. All
of these actions are then transformed into generated PL/SQL
code. More advanced actions as key lookup and union are supported
as well in this release. Should the user have chosen a flat
file as a source, Warehouse Builder will automatically detect
this and will generate the appropriate SQL*Loader code.
Complex tasks such as name and address cleansing are also
supported within the generic concept of Warehouse Builder.
Warehouse Builder provides containers (external processes)
in the Mapping environment if designers need to embed external
code (this can be any code that can be called from PL/SQL).
Generating
Code
To deploy the actual code to the database engine,
Warehouse Builder enables users to generate code for the logical
models stored in the repository. The first step to create
the generated code is to ensure all metadata is valid. Validation
verifies the metadata and reports defects in the logical model.
If the model is valid, the code can be generated and this
code may be deployed to a file system for later access. To
instantiate the warehouse and the ETL processes the following
types of code are generated:
- SQL DDL for warehouse creation
- PL/SQL for relational sources
- SQL*Loader control files for flat file
sources
- ABAP to extract from SAP R/3 sources
- Tcl registration scripts for Oracle Enterprise
Manager
The generated code embodies the following optimizations
for the Oracle Database platform:
- Set-based or row-based
- PL/SQL bulk processing
- Foreign key constraint manipulation
- Faster loading from remote tables using
inline views
- Partition exchange loading
Managing
the Warehouse
Once all the code is deployed in the target system
the day-to-day activities include scheduling the ETL processes
and verifying whether these processes ran successfully.
Scheduling The scheduling is provided by Oracle Enterprise
Manager (OEM). Warehouse Builder exchanges metadata with OEM
to provide the job entries within OEMs job list.
Error Reporting Errors can occur while loading data.
To allow easy access to the error information, Warehouse Builder’s
Runtime Audit Viewer, allows users to drill down from a job
to individual errors detected.
Life-Cycle Management A Data Warehouse is an ever-evolving
system. Sources may be added, existing sources may change, and
the target must be adapted to new business questions and so
on. To allow the warehouse to grow and evolve, Warehouse Builder
offers life-cycle management capabilities. Besides the abilities
to handle source object changes, Change Management allows Warehouse
Builder to adapt the warehouse to changes in the logical model.
To deploy these changes, the user simply chooses the "generate
upgrade" mode while generating code. This generation mode creates
the new scripts and an impact analysis report for the user.
The user can then choose to deploy these changes. All this is
done without jeopardizing the data in the warehouse environment.
More Info
Oracle9iDS Daily Features
|
 |
Bookmark
