ETL Tools for Data Warehouses

Designing and maintaining the ETL process is often considered one of the most
difficult and resource-intensive portions of a data warehouse project. Many data
warehousing projects use ETL tools to manage this process. Oracle Warehouse Builder
(OWB), for example, provides ETL capabilities and takes advantage of inherent
database abilities. Other data warehouse builders create their own ETL tools and
processes, either inside or outside the database.

Besides the support of extraction, transformation, and loading, there are some other
tasks that are important for a successful ETL implementation as part of the daily
operations of the data warehouse and its support for further enhancements. Besides
the support for designing a data warehouse and the data flow, these tasks are typically addressed by ETL tools such as OWB.

Oracle is not an ETL tool and does not provide a complete solution for ETL. However,
Oracle does provide a rich set of capabilities that can be used by both ETL tools and
customized ETL solutions. Oracle offers techniques for transporting data between
Oracle databases, for transforming large volumes of data, and for quickly loading new
data into a data warehouse.


Daily Operations in Data Warehouses
The successive loads and transformations must be scheduled and processed in a
specific order. Depending on the success or failure of the operation or parts of it, the result must be tracked and subsequent, alternative processes might be started. The control of the progress as well as the definition of a business workflow of the
operations are typically addressed by ETL tools such as Oracle Warehouse Builder.


Evolution of the Data Warehouse
As the data warehouse is a living IT system, sources and targets might change. Those
changes must be maintained and tracked through the lifespan of the system without
overwriting or deleting the old ETL process flow information. To build and keep a
level of trust about the information in the warehouse, the process flow of each
individual record in the warehouse can be reconstructed at any point in time in the
future in an ideal case.

Posted in: ETL