Challenges
The Oracle DataPump and export utilities can unload data from Oracle, but not much else. The speed of data acquisition from other sources may be insufficient.
Transformations in a single database are inefficient. Large jobs consume database resources, and can degrade query performance. OWB and ODI data mappings can be complex, and may require SQL procedures to modify.
Oracle recognizes that "flat files are frequently use for simple and high performance data movement in ETL applications." However, Oracle is not a flat file data movement or sorting specialist, and, as such, cannot accelerate bulk loads alone.
Beyond performance and functional concerns, ODI pricing may be prohibitive, especially relative to more modern, nimble vendor data warehousing platforms which support more data sources and include more data management capabilities.
Solutions
Bulk Extract to Flat Files
IRI FACT (Fast Extract) software speeds Oracle and other very large database (VLDB) unloads. This is important in both ETL and ELT environments. FACT is faster and less complicated than the "parallel table function" in OWB. See:
Speed ETL & SQL Transforms
IRI CoSort software performs the same transformations as SQL, only 10x faster. The CoSort SortCL program runs in the file system, bypassing one or more transformations stages in OWB and ODI, including sorting, joining, and aggregating.
Remove the overhead of bulk data transformation from Oracle by specifying and combining these operations in a single SortCL job script and I/O pass. For more information, see:
Blog > Structured Data Transformations
In addition to efficiency, the data and mapping definitions in SortCL are in simple 4GL text files anyone can understand and easily modify. The language of SortCL is also modeled in the IRI Workbench GUI, built on Eclipse. Metadata and job script creation are also automated and supported in this rich, familiar IDE.
The IRI Voracity data management platform which includes CoSort is powered by SortCL in full-on data warehouse ETL operations, and includes support for the discovery, integration, migration, governance, and analytics of Oracle and other database and file sources, including graphical and metadata support for configuring and executing FACT in ETL and other data movement operations.
Speed Bulk Loads into Oracle and Other RDBs
Any of the sort utilities included in the IRI CoSort package will speed bulk loads into most relational DBs, including Oracle, DB2, MySQL, PostgreSQL, Sybase, and SQL*Server. For more information on speeding Oracle loads, refer to:
Re-platform ODI ETL Operations
You can now migrate ETL jobs in ODI to Voracity automatically through AnalytiX DS (now erwin) Code-Automation Frameworks (CATfx) technology. Both Informatica and Voracity metadata are modeled in erwin DS Mapping Manager so you can move projects between the two platforms with ease. Voracity is both several times faster and less expensive than ODI, and with proven erwin CATfx technology and Lite Speed Conversion services, it is finally possible (and cost-effective) to switch.
