Optimizing OWB and ODI Performance
Accelerating Oracle Unloads, Loads, and Transformations
Challenges
The Oracle DataPump and Export tools can unload data from Oracle, but not much more. The speed of data ingestion from other sources can be insufficient.
Transformations in a single database are inefficient. Large jobs consume database resources and can affect query performance. OWB and ODI data mappings can be complex and may require SQL procedures for modifications.
Oracle recognizes that „flat files are often used for simple and powerful data movement in ETL applications.“ However, Oracle is not a specialist in moving or sorting flat file data, and therefore cannot accelerate bulk loads alone.
Solutions
Bulk Extract to Flat Files
The IRI FACT (Fast Extract) Software accelerates the unloading of Oracle and other very large databases (VLDBs). This is important in both ETL and ELT environments. FACT is faster and less complicated than the „parallel tables feature“ in OWB. Details here: Blog > VLDB > Unloading Large Data Volumes from Oracle et al.
Accelerating ETL and SQL transformations
The IRI CoSort Software performs the same Transformations How SQL, just 10x faster. The program CoSort SortCL is executed in the file system and bypasses one or more transformation stages in OWB and ODI, including sorting, joining, and aggregating.
Remove the overhead of mass data transformation from Oracle by specifying and combining these operations within a single SortCL job script and I/O pass. For more information, see: Blog > Structured Data Transformations
In addition to its efficiency, SortCL’s data and mapping definitions are stored in simple 4GL text files that anyone can understand and easily modify. The SortCL language is also available in the IRI Workbench GUI modeled, which is based on Eclipse. Metadata and the creation of job scripts are also automated and supported within this extensive, familiar IDE.
The data management platform IRI Voracity, which includes CoSort, is powered by SortCL in Full-On Data Warehouse ETL operations and provides support for the discovery, integration, migration, management, and analysis of Oracle and other database and file sources, including graphical and metadata support for the configuration and execution of FACT in ETL and other data transfer processes.
Accelerating Bulk Loads in Oracle and Other RDBs
Each of the IRI CoSort- Included sorting programs accelerate mass loads into most relational databases, including Oracle, DB2, MySQL, PostgreSQL, Sybase, and SQL*Server. For more information on accelerating Oracle loads, see: Blog > VLDB Operations > High Speed DB Loading
Replatforming of ODI ETL Operations
You can schedule ETL jobs in ODI by Voracity now automatically via AnalytiX DS (now ErwinMigrate Code-Automation Frameworks (CATfx) Technology. Both Informatica and Voracity metadata are modeled in erwin DS Mapping Manager, allowing you to move projects between the two platforms with ease. Voracity is both orders of magnitude faster than ODI and more cost-effective, and with proven erwin CATfx technology and Lite Speed Conversion Services, it is finally possible (and affordable) to switch.