ETL Tool Acceleration
Optimizing Performance for Extract, Transform, and Load
Challenges
Most ETL and ELT tools, and the database modules they use, cannot efficiently transform large amounts of data without:
- an expensive parallel processing edition
- Extraction of database or system resources from third parties
- a complex, hard-to-maintain Hadoop environment
- a 6 or 7-digit hardware appliance or server upgrades
- transferring the problem to an even more expensive database
It's the large sorting, joining, and aggregation jobs that can take too long. Subsequent tasks such as loading, analysis, or BI displays also suffer. And these E, T, and L steps are typically performed in separate steps, I/O passes, products, or constantly changing cloud configurations.
Solutions
If you have a data warehouse, the efficiency of your ETL tools is likely a concern. IRI extraction and transformation tools such as FACT or CoSort – or the IRI Voracity ETL and data management platform that supports them by easily running within or alongside ETL tools, whether they are on-premises or in the cloud.
Operation | IRI Product | Unterssupport | Advantages |
IRI FACT (Quick Extract) | Oracle, DB2, Sybase, MySQL, SQL Server, Altibase, Greenplum, Teradata, Tibero | Native DB drivers, parallel unloading, portable flat-file output data, simple job scripts, easily callable | |
Database-agnostic, all flat files, Informatica, DataStage, and all ’system command‘ calls. | Multi-Threading, task and I/O consolidation, local and remote execution in LUW file systems or Hadoop, as well as automatic metadata and job creation. | ||
All RDBMS loaders, ODBC, and JDBC | Streaming pre-coordinated data based on E or T orders to reduce loading time by up to 90%. | ||
More than 125 old and modern small and large Data sources and targets. | All of the above in a comprehensive data management environment that combines data acquisition, integration, migration, governance, and analysis in Eclipse. |
Optimize sort, join, and aggregation transformations in Informatica, DataStage, Talend, Pentaho, ODI and other tools with the SortCL-Engine in CoSort Product or the Voracity Platform. Many SortCL jobs can also seamlessly integrated into Hadoop be executed and called with other tools at the API or script level, for example, in Kalido, ETI, Software AG Natural, SAS, and TeraStream.
Use the metadata and workflows you have and simply call the IRI software from your tool to increase speed and/or unload, Data transformations and operations such as:
· Sorting
· Joins
· Aggregate
· Lookups
· Perl-compatible regular expressions
· Data Type and File Format Conversions
· Field/Column Encryption and Masking
· Detail, delta (CDC), and summary reports
· Pivoting Rows and Columns
· Slowly Changing Dimensions
· Test data generation
You can also invoke IRI jobs from the shell (as a batch execution or ETL tool command) via API or the Eclipse GUI, and move data back and forth as needed via files, pipelines, or procedures. In the GUI environment of IRI Workbench Can you create individual job specifications or complete ELT or ETL flows that connect CoSort (and FACT) with your sources and destinations?.
DataSwitch, Quest (formerly Erwin and AnalytiX DS), and Meta Integration Model Bridge (MIMB) software or services can also convert metadata defined in common ETL tools (such as Informatica’s .xml and DataStage .dsx repositories) into equivalent Voracity data (and/or job) specifications, if you have these mappings in Voracity relocate want to save money and time. This automatic metadata replication preserves your existing design investments, facilitates job creation, and reduces migration costs.
Other Resources