Data aggregation
Aggregate functions for aggregate data analysis
Challenges
„The use of pre-sorted summaries (aggregates) is the most effective tool that the data warehouse designer uses to monitor performance.“ „The Data Warehouse Toolkit“ - Dr. Ralph Kimball
The main problem with processing aggregate data is the speed of large volumes of data. The SQL aggregation „group by“ in databases for star schemas and ETL processes become slower with increasing data volumes.
Another problem with performing aggregate data analysis is that most COTS products, including ETL tools, do not support advanced data aggregation techniques such as running totals or combining aggregate functions with cross-calculations or custom report formatting.
Solutions
The SortCL program in the IRI CoSort package and the IRI Voracity platform (ETL +) computes huge fact tables, drill-down and roll-up aggregates with exceptional simplicity 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 quickly generate output values derived from accumulated detail records - at multiple holding levels (including the final aggregate). The associated functions are:
Summarization (totals)
Averaging
Maximum
Minimum
Counting
Multiplication
Ranking list
Sequencing
Standard deviation
Here is an example for a data aggregation job in the IRI Workbench graphical IDE for Voracity ETL operations, showing the script of the CoSort SortCL job and its transform mapping diagram:
Here's more of what you can do:
- Display of final values at the end of a file, i.e. roll-up aggregations, and use of SortCL wherever extensive, simultaneous sorting and grouping work is required.
- Group data based on Boolean break conditions between and within data sets for EIS summaries or drill-down analysis for many types of numeric data. This is a great feature for creating detail and summary reports as well as for aggregating factual datasets.
- Reflect transaction aggregates based on different field combinations (e.g. total sales by SKU, employee and location) by separating out individual sales reports.
- Format summary records differently at each level. Write each level in a separate target table or file and merge the sub-levels into a structured report for analyzing the aggregated data.
Other options include running (accumulating) aggregates, Sybase-style window-based aggregates and cross-calculated value aggregates. Use these features for ad hoc presentations and complex trend analysis, with (or without) all other transformations and formatting options.