7 Aggregating Data

What is Aggregation?

Aggregation is the process of consolidating multiple values into a single value. For example, data can be collected on a daily basis and aggregated into a value for the week, the weekly data can be aggregated into a value for the month, and so on. Aggregation allows patterns in the data to emerge, and these patterns are the basis for analysis and decision making. When you define a data model with hierarchical dimensions, you are providing the framework in which aggregate data can be calculated.

Aggregation is frequently called summarization, and aggregate data is called summary data. While the most frequently used aggregation operator is Sum, there are many other operators, such as Average, First, Last, Minimum, and Maximum. Oracle OLAP also supports weighted and hierarchical methods. Following are some simple diagrams showing how the basic types of operators work. For descriptions of all the operators, refer to "Aggregation Operators".

Figure 7-1 shows a simple hierarchy with four children and one parent value. Three of the children have values, while the fourth is empty. This empty cell has a null or NA value. The Sum operator calculates a value of 12 (2 + 4 + 6) for the parent value.

Figure 7-1 Summary Aggregation in a Simple Hierarchy

Description of "Figure 7-1 Summary Aggregation in a Simple Hierarchy"

The Average operator calculates the average of all real data, producing an aggregate value of 4 ((2 + 4 + 6)/3), as shown in Figure 7-2.

Figure 7-2 Average Aggregation in a Simple Hierarchy

Description of "Figure 7-2 Average Aggregation in a Simple Hierarchy"

The hierarchical operators include null values in the count of cells. In Figure 7-3, the Hierarchical Average operator produces an aggregate value of 3 ((2 + 4 + 6 +NA)/4).

Figure 7-3 Hierarchical Average Aggregation in a Simple Hierarchy

Description of "Figure 7-3 Hierarchical Average Aggregation in a Simple Hierarchy"

The weighted operators use the values in another measure to generate weighted values before performing the aggregation. Figure 7-4 shows how the simple sum of Figure 7-1 is altered by using weights ((3*2) + (2*4) + (1*6) +(4*NA)).

Figure 7-4 Weighted Sum Aggregation in a Simple Hierarchy

Description of "Figure 7-4 Weighted Sum Aggregation in a Simple Hierarchy"

Managing Aggregate Data

The creation and maintenance of summary data is a serious issue for DBAs. If no summary data is stored, then all summarizations must be performed in response to individual queries. This may slow the response time. At the other extreme, if all summary data is stored, then the database multiplies in size.

Basic Strategies for Aggregating Data

In an analytic workspace, a data load typically fetches data only at the lowest, or base, level. The data cells at the higher levels are empty until the values are calculated from the base values.

Table 7-1 shows a small portion of the Global Sales measure before aggregation.

• The Channel dimension is set to a single base-level member named Catalog.

• The Product dimension is set to a single base-level member named Sentinel Financial.

• The Time dimension is set to Jul-04 and its ancestors, Q3-04 and 2004.

• The Customer dimension is set to KOSH Enterprises Boston and its ancestors, United States, North America, and All Customers.

Out of the 12 cells shown, only one has a data value. If the three ancestors of Sentinel Financial and the one ancestor of Catalog were added, then this number would expand to one out of 96 cells.

Nonetheless, the data is always presented to the application as fully solved; that is, both detail and summary values are provided, without requiring that calculations be specified in the query.

In an analytic workspace, aggregate data is generated at two distinct times:

• As part of the build procedure. You can calculate and store all of the aggregates, none, or a portion of them. Calculated values are stored in the analytic workspace and shared by all sessions. This is often called pre-aggregation.

• In response to a query. Any "missing" aggregate data will be calculated for the query, even if the analytic workspace has no stored aggregate data. Calculated values may be cached for use throughout a session, but they are not shared among sessions. This is often called aggregating on the fly.

If your dimensions have multiple hierarchies or if the hierarchies have many levels, then fully aggregating the measures can increase the size of your analytic workspace (and thus your database) geometrically. At the same time, much of the intermediate level data may be accessed infrequently or not at all.

The most effective method of summarizing data in any analytic workspace is by storing some aggregates and calculating others on the fly. When choosing which aggregate values to store, your goal is to select those that require time- and resource-intensive calculations. Calculations that can be performed quickly can be left until runtime.

Table 7-2 shows the same portion of the Global Sales measure after it has been aggregated as part of the data maintenance process. The Warehouse level of Customer and the Year level of time have been calculated and stored. Out of the 12 cells shown, four now have data values, which is one-third the total. The entire measure has this ratio when Channel and Product are fully aggregated during maintenance.

When an application queries the analytic workspace, either the aggregate values have already been calculated and can simply be retrieved, or they can be calculated on the fly from a small number of stored aggregates. In addition, you can choose to cache aggregate values for the duration of a session, so that they are calculated on the fly only once. Table 7-3 shows the Sales measure when it is fully aggregated in response to a query. Eight values are calculated on the fly to return the answer set to the client.

Aggregating Compressed Composites

Compressed composites are used to store extremely sparse data. They are designed specifically to handle data structures in which several levels may store the same value. Figure 7-5 shows an aggregation in which one out of 12 cells has a data value. The skip-level strategy described in "Aggregating Non-Compressed Composites" does not produce any benefit under these circumstances.

Figure 7-5 Aggregation of Very Sparse Data

Description of "Figure 7-5 Aggregation of Very Sparse Data"

Use this aggregation strategy for compressed cubes:

• Identify the dimension with the most members. If several dimensions have about the same number, then choose the dimension with the most levels. Do not pre-aggregate this dimension.

• Pre-aggregate all other dimensions up to, but not including, the top level, unless the next level down has a large number of members.

You can adjust these basic guidelines to the particular characteristics of your data. For example, you may skip levels that are seldom queried from pre-aggregation. Or you may need to pre-aggregate a level with a large number of child values, to provide acceptable run-time performance.

Selecting Dimension Members for Aggregation

The aggregation rules defined for a cube or a measure are always performed over all dimension members. You can perform a partial aggregation only in a calculation plan and only for regular composites.

To aggregate over a portion of a measure, you select the dimension members that identify the cells containing the source data, using the Status page of the Aggregation property sheet. You do not need to select the target cells. All of the cells identified by the ancestors of the selected dimension members are aggregated, either when you execute the calculation plan or when a user queries the measure.

When you select the dimension members, they are in status. This means that the dimension members have been selected for use in a calculation, a query, or other data manipulation. Likewise, out of status means that the dimension members have been excluded from use.

Figure 7-6 shows an aggregation in which the 12 months of 2006 are in status. Neither the quarters nor the year are in status, but aggregates are generated for all levels.

Figure 7-6 Sum Aggregation With All Source Values in Status

Description of "Figure 7-6 Sum Aggregation With All Source Values in Status"

Figure 7-7 shows the same portion of data, but with only Feb to Jun in status. Aggregates are calculated only for Q1, Q2, and 2006. Note that Jan is included in the aggregation, even though it is out of status. The aggregation engine adds the ancestors, then the children to status before aggregating the data, as a means of maintaining the integrity of the data. The values for Jul to Dec are not included in the aggregation.

Figure 7-7 Sum Aggregation With Some Source Values Out of Status

Description of "Figure 7-7 Sum Aggregation With Some Source Values Out of Status"

You may need to aggregate data that is stored in the middle of a hierarchy, perhaps if the data for a particular measure is not available or needed at the base level. You must be sure that the cells with the data are the lowest levels in the hierarchy in status. Figure 7-8 shows quarterly forecast data in status and aggregated to the year. The monthly values are not in status, and thus are excluded from the aggregation.

Figure 7-8 Sum Aggregation From the Quarterly Level

Description of "Figure 7-8 Sum Aggregation From the Quarterly Level"

Aggregation begins at the lowest level in status and rolls up the hierarchy. The aggregate values will overwrite any pre-existing values higher in the hierarchy. Figure 7-9 shows that when the Month level is in status, those values will overwrite the forecast values at the Quarter level. The status of Quarter and Year has no effect on the aggregation.

Figure 7-9 Sum Aggregation From the Month Level Overwrites Quarters

Description of "Figure 7-9 Sum Aggregation From the Month Level Overwrites Quarters"

Defining an Aggregation

Analytic Workspace Manager enables you to define aggregations at three different times. You can use whatever combination best suits your needs:

• Cube. You can define default aggregation rules for all measures in a cube by defining them just once. You define these rules when creating or modifying a cube.

• Measure. You can define unique aggregation rules for a particular measure that will override the default cube rules. You define these rules when creating or modifying a measure. You cannot specify aggregation rules for individual measures for compressed cubes.

• Calculation Plan. You can define rules for one or more measures and determine the order in which the measures are calculated. In this way, you can support dependencies among the measures and the calculations, such as aggregating data that is generated by a forecast. You define these rules when creating or modifying a calculation plan. This type of aggregation is performed after the measures are aggregated using either the Cube or the Measure rules.

Regardless of the level at which you define the summarization rules, the basic decisions and the user interface are the same:

• Select an aggregation operator and, depending on the operator, a weight measure on the Rules tab.

• Select the levels for storing values on the Summarize To tab.

To aggregate a measure using the cube rules:

1. On the General page of the Cube property sheet, select Use Default Aggregation Plan for Cube Aggregation.

2. On the General page of the Measure property sheet, select Use Aggregation Specification From the Cube.

To aggregate a measure using its individual rules:

1. On the General page of the Cube property sheet, select Use Default Aggregation Plan for Cube Aggregation.

   Any measure in the cube without its own individual rules will use this default plan.

2. On the General page of the Measure property sheet, select Override the Aggregation Specification of the Cube.

To aggregate a measure using a calculation plan:

1. Define the aggregation rules for the measure or the cube, as described previously.

   You can aggregate all the values first, then execute the calculation plan to overwrite a portion of the aggregate values. Or you can define the rules for the measure so that no aggregates are created during data maintenance.

2. Create a calculation plan that will aggregate all or part of the measure.

To aggregate all measures in a cube using a calculation plan:

1. On the General page of the Cube property sheet, clear the Use Default Aggregation Plan for Cube Aggregation box.

2. Create a calculation plan containing one or more aggregation steps that will aggregate all measures in the cube.

Aggregation Operators

Analytic workspaces provide an extensive list of aggregation methods, including weighted, hierarchical, and weighted hierarchical methods.

Case Study: Aggregating a Moderately Sparse or Dense Cube

The Global data set is a good candidate for skip-level aggregation. This example discusses aggregation of the Units Cube, which has four dimensions: Time, Customer, Product, and Channel. You could skip levels on one or two dimensions. For this example, skip levels on only one dimension; because Global is small, precalculating the additional levels does not create a problem with time or disk space.

The Customer dimension has two hierarchies with a total of seven levels. Because it has the most levels of any dimension, it is the best choice for skipping levels.

To identify the levels to be precalculated, you must know the number of dimension members at each level. You can easily acquire this information in SQL, using this SQL command:

SELECT COUNT(DISTINCT ship_to_id), COUNT(DISTINCT warehouse_id),
COUNT(DISTINCT region_id),COUNT(DISTINCT total_customer_id),
COUNT(DISTINCT account_id), COUNT(DISTINCT market_segment_id),
COUNT(DISTINCT total_market_id), FROM global.customer_dim;

Global is a very small data set, so few adjacent levels have the desired 10:1 ratio of children-to-parent dimension members. Table 7-4 and Table 7-5 identify the appropriate levels to be calculated and stored for the two hierarchies. Only eight members are stored out of a total of 45 aggregate members.

On the Summarize To page for the Units Cube, select the precalculated levels for Customer, and select all levels for Time, Product, and Channel.

Figure 7-10 shows this selection on the Summarize To page of the Units Cube property sheet.

Figure 7-10 Selection of Customer Levels

Description of "Figure 7-10 Selection of Customer Levels"

Case Study: Aggregating a Very Sparse Cube

Sales History is a very sparse data set, so it uses compressed composites for all cubes. You should fully aggregate all but the largest dimension. Table 7-6 shows the number of base-level members for each dimension. This information is easily acquired by counting the unique values in the dimension tables like this:

SELECT COUNT(DISTINCT time_id) FROM sh.times;

Because Customers is far larger than any of the other dimensions, you do not need to count the aggregate members or the number of levels to identify it as the largest one. On the Summarize To page for the cubes, deselect all levels for Customers and select all but the top level for Channels, Products, Promotions, and Times.