Databases Reference
In-Depth Information
whereby updates are provided back to the originating systems. In addition to minimiz‐
ing some of the cleansing that takes place during future extractions, operational reports
become more accurate.
Another viable option is to avoid cleansing by improving the quality of the data at the
time of its input into the operational system. As noted previously in this chapter, this is
critical if OLTP systems are to be directly accessed for business intelligence. Improving
data quality at the source also enables high-speed loading techniques to be used in near
real-time data warehouses (since transformations can be eliminated).
Improving data quality at the source can sometimes be accomplished by not allowing a
“default” condition as allowable input into a data field. Presenting the data-entry person
with an array of valid options, one of which
must
be selected, is often a way to ensure
the most consistent and valid responses. Many companies also provide education to the
data-entry people, showing them how the data they're keying in will be used and what
the significance of it is.
Key Oracle products and database features that often help facilitate this process include:
Oracle Data Integrator (ODI)
Acquired by Oracle in 2007, this extraction, transformation, and loading (ETL) tool
that handles heterogeneous sources and targets is sometimes referenced as an ELT
tool since transformations are pushed into the target data warehouse. This product
has replaced Oracle Warehouse Builder as Oracle's primary offering for ETL. ODI
features Knowledge Modules that define integration capabilities, including extrac‐
tion with change data capture, loading and unloading utilities, SQL-based loading
and unloading, and transformation logic SQL. Data Quality options include data
quality profiling, batch processing, and address verification. The Knowledge Mod‐
ules are modifiable. The product architecture includes a development environment
that makes use of the Knowledge Modules as templates in declarative design pro‐
cesses and an orchestration agent. ODI can be used for data-based, event-based,
and service-based data integration.
Oracle GoldenGate
Acquired by Oracle in 2009, GoldenGate has replaced Oracle Streams as the pri‐
mary software recommended for log-based replication. Often used for zero down‐
time software upgrades, during software migrations, and for low latency transaction
replication and recovery, GoldenGate supports a wide variety of data sources and
targets. It is often used to load Oracle-based data warehouses where the need for
data transformations is minimal and near real-time updates of the data in the data
warehouse are desired.
Transparent Gateways and Heterogeneous Services
Provide a bridge to retrieve data from non-Oracle sources using Oracle SQL to load
an Oracle Database. Heterogeneous Services provide ODBC connectivity to non-
