Success Stories
Pharmaceutical Data Warehouse - ACG
(published January 2000, DM Review)
Background
Application Consulting Group (ACG) is a consulting firm based in
Morristown, NJ dedicated to building complete Business Intelligence
solutions for Fortune 500 companies. ACG specializes in advanced
web-based analytical systems for sales, marketing and finance. Bob
Gomez is the Vice President of Consulting Services and directs the
data warehousing project discussed here.
Hardware
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 8 hours
to load into Oracle 8i. The data is provided in three separate 1.4
gig 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, it also
allows the user to specify the specific keys on which to match.
Weaknesses
The system did not have 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 there
is no other sort package on the market that does the join processes
we needed.
Deliverables
The deliverable in this instance as 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 personal have been extremely
helpful, even to the point of allowing conversations with the developers.
Documentation
The documentation is fairly complete and had 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.
|
