CoSORT: State-of-the-Art Aggregation Functionality and Performance

According to "The Data Warehouse Toolkit" by Dr. Ralph Kimball, "The use of pre-sorted summaries (aggregates) is the single most effective tool the data warehouse designer has to control performance." CoSORT's sort control language (SortCL) program can generate fact table and other aggregates faster than any other method, combining summarization with sorting (or joining) operations in the same script and I/O pass.

CoSORT's SortCL can count, summarize (total), average, and find the maximum and minimum values based on inter- and intra-record break conditions to produce sophisticated EIS summary, or "drill-down" analysis on multiple classes of field data, including numeric values and IP addresses. Also uniquely featured are running (accumulating) aggregates, and aggregates on cross-calculated values. These grouping features are also used for ad hoc presentations and complex trend analyses in conjunction with sorts, joins, and/or reports.

CoSORT's SortCL can produce output records containing summary fields derived from accumulated detail records. Multiple levels of summary records can be created in the same pass. Depending on the different levels of BREAKs you specify, multiple levels of subtotals can be provided, along with a grand total. One or more summary fields may be derived using the following SortCL features:

Summary records may be produced on a given BREAK condition and/or at the end of processing. Any number of intermediate BREAK levels can also be specified. This is particularly useful in data-warehousing environments where complex drill-down aggregation and grouping are required.

Summary records can be formatted differently at each level. Each of these levels can be written to a separate file, or merged into one file to produce a structured report. With CoSORT, you can also create RUNNING summary fields in the detail records. These running, or accumulating, summary fields are updated at each record. Finally, it is possible to use CoSORT's /ROUNDING statement to change the way in which numeric values with several decimal places are rounded after an arithmetic SortCL operation.