Siebel Analytics Performance Tuning Guide > Siebel Analytics > Add Aggregate Tables >

Identify Candidates

You will need to identify a set of candidates for aggregate tables as it is unrealistic to create and load every combination of possible aggregate tables. To see this, enumerate the number of possible aggregate tables for a simple fact table that has three dimensions, and each dimension has four possible aggregations.

To identify candidates for aggregate tables, you need to consider two factors:

Query access patterns directly point to data that is actually being summarized and how often this summarization occurs. If the summarization occurs infrequently and the query has little importance, then this would not point to a situation where a aggregate table would provide much value.

TIP:  If a table has fewer than a half million rows, you might try indexing the table before creating one or more aggregates.

Once you have identified a set of candidates that promise value, you can evaluate each according to how much it compresses the data. For example, an aggregate table that allows a query to read one row that sums one hundred rows of detail data, would provide great value. On the other hand, a aggregate table that saves the query from reading three rows would provide little.

You can quickly calculate the compression factor using the Siebel Analytics Administrative Tool's Update Row Count function which is illustrated in Figure 4.

Figure 4. Update Row Count

Click for full size image

The degree to which a aggregate table compresses detail data depends on the distribution of data. For more information on how to characterize the density and sparsity of these distributions and aggregate tables, see Chapter 14 of Ralph Kimball's book, The Data Warehouse Lifecycle Toolkit (Wiley, 1998). Identifying and qualifying candidates for aggregate tables is a matter of studying the query workload and determining the savings. In many cases, it is useful to simply experiment because the actual benefits are sometimes difficult to quantify with a pencil and paper.


 Siebel Analytics Performance Tuning Guide 
 Published: 18 April 2003