Generating Aggregate Data - OLAP

An analytic workspace initially contains only the detail data from the relational schema. However, it also contains the hierarchies, levels, and parent relations that are needed to aggregate the data. The aggregate data in an analytic workspace replaces the use of materialized views; all of the aggregate data is created in the analytic workspace. To optimize run-time performance, you must generate and store some aggregate data.

Strategies for Calculating Aggregates

A data cube in an analytic workspace can be solved at two distinct times:

  • At run-time when needed. The cells for the aggregate values are NA (null) until a query requests the aggregate values. The aggregates are then calculated in response to the query. This type of aggregation is referred to as on-the-fly or run-time aggregation. Run-time aggregation slows querying time since the data must be calculated instead of just retrieved, but it does not require storage in a permanent tablespace for the aggregate values.
  • As a data maintenance procedure. The DBA calculates the aggregate values and stores them in the analytic workspace for all users to share. This type of aggregate data is sometimes call precomputed or stored aggregates. Stored aggregates support the fastest querying time, but increase the size of the analytic workspace and therefore the size of the relational database. The amount of stored data may also be limited by the amount of time available for data maintenance, which is typically limited to a batch window. Fully materializing a cube may simply take more time than the batch window permits.
    When dimensions have multiple hierarchies or the hierarchies have many levels, fully aggregating the measures increases 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.

A typical strategy is to aggregate some of the data as a data maintenance procedure and the rest of the data on demand. This strategy is called skip-level aggregation. The data cube is presented to the application fully solved, with no detectable difference between the values that were retrieved from storage and the values that were calculated for the query. When skip-level aggregation is done correctly, the time to calculate the unsolved levels is negligible.

How to Select Levels to Pre-Aggregate and Store

A good strategy for identifying levels for pre-aggregation is to determine the ratio of dimension members at each level, and to keep the ratio of members to be rolled up on the fly at approximately 10:1. This ratio assures that all answer sets can be returned quickly. Either the data is stored in the analytic workspace, or it can be calculated by rolling up 10 values at a time into each aggregate value.

This 10:1 rule is best applied with some judgment. You might want to permit a higher ratio for levels that you know are seldom accessed. Or you might want to pre-calculate levels at a lower ratio if you know they have heavy use.

About Aggregation Plans

An analytic workspace that was created by the Create Analytic Workspace wizard has a default set of rules, called an aggregation plan, for each cube. A default plan specifies that:

  • No aggregate levels are stored. All aggregate data is calculated at run-time as necessary to return an answer set to a query.
  • All measures in the cube use the default plan.

The default aggregation plans assure that the measures in a cube are always presented to an application with fully solved data; that is, all levels of all hierarchies in an answer set are populated. However, because the default plans specify that all levels must be calculated during the user's session, their use typically causes unacceptably slow performance.

You can define and deploy aggregation plans that precalculate some of the data. Each measure can have its own aggregation plan, or any number of measures in a cube can share the same one. For each aggregation plan that you create, you must specify:

  • Which aggregate levels are stored.
  • Which measures in the cube use the plan.

An aggregation plan does not take effect until it is deployed. Deployment creates and modifies objects in the analytic workspace to support the aggregation plan, and then calculates all stored aggregate levels. While you can create an aggregation plan in a few minutes, deployment can take much longer, depending on the amount of data that needs to be calculated and the available resources. You may want to schedule aggregation for off-peak hours.

How to Create and Deploy an Aggregation Plan

An aggregation plan can be used for all the measures in a cube, or just for selected measures.

Creating an Aggregation Plan

To create an aggregation plan:

  1. Expand the Cubes folder of OLAP Catalog View so that you can see the names of the analytic workspaces in your schema.
  2. Right-click the name of your workspace.
  3. Choose Create Aggregation Plan Using Wizard. Complete the steps of the wizard.
    Click the Help button to get specific information about each step.

The aggregation plan is a permanent part of your analytic workspace until you explicitly delete it.

Changing the Aggregation Operator

The Aggregation Plan wizard always specifies SUM as the method of aggregation. However, you can change the method to one of these operators:


A procedure in the DBMS_AWM package changes the operator in an existing aggregation plan. The syntax of the procedure call is this:

EXECUTE DBMS_AWM.SET_AWCUBEAGG_SPEC_AGGOP('aggplan', 'aw_owner', 'aw_name', 'cube', 'measure', 'dimension', 'operator')

For the BI Beans to be aware of this change, you must either re-enable your analytic workspace or manually execute the


Deploying an Aggregation Plan

Deployment first deletes any previously aggregated data, then solves the data for the specified levels. To deploy an aggregation plan:

  1. Expand the OLAP Catalog View sufficiently to see the names of the aggregation plans for your workspace.
  2. Right-click the name of an aggregation plan, and choose Deploy Aggregation Plan Using Wizard. Complete the steps of the wizard.

You can edit an aggregation plan, but no change is made to your data until you redeploy the modified plan. Similarly, you can delete an aggregation plan, but no change is made until you deploy another plan for the same measures. You must redeploy your aggregation plans whenever you refresh the data.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics