Compression ratio

The process of data aggregation applies an aggregate function, such as sum or average, to a set of child records to produce a single parent record. The average number of child records combined to calculate one parent record is called the compression ratio. One measure of effectiveness of an aggregate table can be estimated from this number, since it represents the decrease in records that must be read to respond to a query at that level.

Recall that some of the reasons to build aggregate tables include the reduction of disk I/O and the number of records that must be dynamically sorted and aggregated. Therefore, pre-aggregating data is effective only if the compression ratio is significant. For example, if the compression ratio is 3:2, the aggregate table requires 2/3 of the base table's storage space but yields only a 1/3 reduction in the number of records. In contrast, if the compression ratio is 4:1, the aggregate table reduces the number of records by 3/4 and uses only 1/4 of the storage space.

When the number of elements differs significantly between two attributes in the same hierarchy, the compression ratio suggests that an aggregate table can provide more efficient queries. Also, for smaller base tables, the resource demands placed on the database server by dynamic aggregations decrease and therefore so does the effectiveness of pre-aggregation. To determine when pre-aggregation is worthwhile for your system, you must balance the importance of speed of query response time and the availability of disk space and resources to maintain the schema.

For more information on ratios, refer to Cardinalities and ratios.