Fast Data Warehouse Aggregates. CoSort Drill-Down and Roll Up Aggregation, Derived Fields.

Home » Solutions » Data Transformation » Aggregate

Aggregate

Drill-Down and Roll-Up in Parallel. Custom Detail and Summary Reports.

Challenges:

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."

The problem is that "group by" aggregations in databases, star schemas, and summarization tasks in ETL and other tools can drag as data volumes increase.

Solutions:

CoSort's SortCL tool helps you compute fact table, drill-down and roll-up aggregates with extraordinary ease and efficiency. SortCL combines parallel summarization with high-volume sort, join, and report operations in the same job script and I/O pass.

Use SortCL to rapidly produce output values derived from accumulated detail records -- at multiple break levels (including the final aggregate). The related functions are:
• Summary
• Average
• Maximum
• Minimum
• Counting
• Ranking

You can rapidly produce final values at the end of a file; i.e. roll-up aggregations, and exploit SortCL wherever large, simultaneous sorting and grouping work is required.

SortCL also groups data based on inter- and intra-record Boolean break conditions for EIS summary, or drill-down analyses on many types of numeric data. This is a great feature for writing detail and summary reports, as well as aggregate fact table records. You can reflect transaction aggregates based on various field combinations (i.e. total sales by SKU, employee, and location) by spinning off individual sales reports.

You can format summary records differently at each level, write each level to a separate file, or merge the sub-levels into one file to produce a structured report.

Other options include running (accumulating) aggregates, and aggregates on cross-calculated values. You can also use these features for ad hoc presentations and complex trend analyses, in conjunction with SortCL's other transformations (see list at left).

CoSort (IRI, Inc.)