BACKGROUND: Application Consulting Group (ACG) is a consulting firm based in Morristown, New Jersey, dedicated to building complete business intelligence solutions for Fortune 500 companies. ACG specializes in advanced Web-based analytical systems for sales, marketing and finance.
PLATFORMS: CoSORT is installed on a Dell Dual Pentium II 450 PC running Windows NT Workstation. The PC has 512 megabytes of memory.
PROBLEM SOLVED: As part of a data mart project for a confidential pharmaceutical client, ACG found load times running outside the acceptable maintenance window. Data, from a syndicated source, was taking over eight hours to load into Oracle8i. The data is provided in three separate 1.4GB files. Each file contained different metrics, but the keys to the data are consistent across all files. If the files could be joined together, the load would go much quicker. CoSORT's join option allowed ACG to merge all three files into one and then load the single joined file. This was not a merge where the three files are simply appended. The join facility takes one record from each of the files and creates a single joined record with all the specified metrics. Load times, which were running 8.5 hours, are now 4.5 hours. The join itself takes less than 30 minutes.
PRODUCT FUNCTIONALITY: For this project the requirement was very specific, but the CoSORT product is a fully functional sort utility. In addition to its joining capability, CoSORT also has the ability to do filtering, aggregation, merging and character-type conversion, just to name a few. CoSORT also has an API allowing calls from customer-developed programs. ACG is currently looking into replacing all standard sorts done in the data transformation (currently done by the ETL tool) with CoSORT. ACG is also investigating doing transposition with CoSORT. In transposition, a single record must be converted into multiple records while retaining the keys from the original but placing different field attributes on each record. This was taking a very long time in the ETL tool. Converting it to custom SQL helped, but it is still taking several hours. ACG expects to cut the time to a fraction of the current process by using the multi-file output capability of CoSORT.
STRENGTHS: CoSORT is unique in the sort utility marketplace as its join feature not only allows multiple records to be merged into one but also allows the user to specify the specific keys on which to match.
WEAKNESSES: The system did not have a GUI interface for specifying the merge. ACG was required to write the merge in the CoSORT scripting language. Though this was not difficult, sort scripting languages can be a bit cryptic for those new to such tools. A Java GUI interface is now available in the full licensed version; ACG did the initial work with an evaluation copy.
SELECTION CRITERIA: The main reason for selecting CoSORT for this project was that, to the best of our knowledge, there is no other sort package on the market that does the join processes we needed.
DELIVERABLES: The deliverable in this instance was very specific a single file containing all the metrics (found in the three files) required on the same record.
VENDOR SUPPORT: The vendor has been very responsive from the first request for an evaluation copy to the negotiations for the software license. There was little need for support in this instance; but in previous dealings with CoSORT/IRI, the support personnel have been extremely helpful, even to the point of allowing conversations with the developers.
DOCUMENTATION: The documentation is fairly complete and contains enough examples to allow the consultant on the project, who had no prior knowledge of CoSORT, to build the appropriate join script without requiring calls to support.
Robert Gomez, vice president of Application Consulting Group, Inc.
Be the first to comment on this post using the section below.