Innovative Routines International (IRI), Inc., The CoSort Company

Faster Sorts, Push Out Optimization, Test Data

Challenges:

PowerCenter transforms of very large data volumes can sometimes run slower than desired, even after consulting and tuning are employed. Bottlenecks may occur during large sort, join, aggregation, load, or unload operations. "Pushdown optimization" to other tools may not be that much faster, and at a minimum, shifts the burden onto a database or more expensive / complex platform.

Another serious need is the protection of sensitive production data moving through ETL processes. You may need to apply role based access controls or generate large volumes of realistic, referentially correct test data you can use to prototype and populate certain applications and targets.

Solutions:

1) Faster Sorts
With CoSort, you can dramatically speed sorting directly within Informatica using CoSort's unique (plug'n'play) Sorter TX AEP (for PowerCenter 7) or CT (for v8). This seamless CoSort component has improved PowerCenter sort performance up to 10X with no interface changes. Subsequent join, aggregation, and load runtimes should also benefit.

CoSort v8 vs. Informatica v7 Sort Benchmarks
Fixed-key, ASCII Sorting on 4-CPU IBM p650

Input >>	26.7MB	267MB	2.67GB
Sorter Tx	8s	1m 48s	20m 35s
CoSort AEP	3s	16s	2m 1s
CoSort SortCL	1s	7s	1m 19s

2) Push Out Optimization
To speed transforms, reports, and field-level protections in general, consider the use of CoSort Sort Control Language SortCL programs alongside your PowerCenter or PowerMart operations. The American Stock Exchange uses CoSort as a "push out optimization" solution to triple runtime performance.

With CoSort, you can easily run large sorts, joins, aggregations, and loads in the file system, where it's much faster. Plus, CoSort allows you to convert file and data types, protect fields at risk with encryption, etc., and generate custom reports -- all at the same time (in the same job script and I/O pass).

3) Test Data Generation
Do you need test data for Informatica ETL prototyping? Consider IRI's test data package called RowGen. With RowGen, you can build realistic, referentially correct test data to populate target tables, marts, files, and reports while leveraging your data models and Informatica metadata. In fact, through tools like RapidACE and the Meta Integration Model Bridget (MIMB), you can easily use the .xml data layouts you already have within CoSort (transformation) and RowGen (test data) operations!