IBM Cognos Dynamic Cubes

Chapter 4. Modeling dynamic cubes 85

tracked. Another example is a measure that takes that variance and compares it to the plan

value to produce a percent of plan measure.

By being built-in, your report authors do not need to create these expressions. This can save

their time and the time of users who might want to create these expressions. Also, because

they are already there, the report authors do not need to re-create a measure expression that

they use in multiple reports.

Because it exists in only one place, the risk of an expression being incorrectly created in one

report is removed. There is only one risk point. Enforcing commonality can reduce

misunderstanding and misinterpretation of information. In addition, because it is built-in, the

performance of the calculation can be faster than a calculation made in a report.

Virtual cubes are covered in greater detail in Chapter 6, “Virtual cubes” on page 133.

4.9 Aggregate cubes

Cognos Dynamic Cubes support aggregate awareness. This awareness is accomplished

through

aggregate cubes. Aggregate cubes define the measures, dimensions, and dimension

grain by which queries can be routed to aggregate tables rather than to the detail fact table.

Because aggregate tables store fact data at a higher-than-detail level of granularity, the time

necessary to aggregate values during the query can be lessened, thus improving

performance. A query can be routed to the aggregate table if all the measures and dimension

hierarchies of the query exist in the aggregate cube definition. Not all of the dimensions and

measures in the aggregate cube need to be in the query.

The objective of modeling an aggregate cube is to establish rules by which the dynamic cube

can know when it can route a query to an aggregate table. This task is done by specifying a

mapping from the identifiers in the dimensions and measures in the cube that have scope to

the aggregate table, to the identifiers in the aggregate table, and, if necessary, its related

tables in a rolled-up dimension schema.

This aggregate cube routing will direct a query only to the aggregate table for a query that

uses objects from a dimension grain at or above the grain of the mapping between it and the

aggregate table. Therefore, using objects from a grain below the mapping grain does not

cause double-counting, because that query continues to route to the detail fact table.

4.9.1 Modeling aggregate cubes

You can use the samples to explore and learn about aggregate cube modeling. The sample

database GOSLDW contains one aggregate table. Its type of aggregate table is a degenerate

dimension. The name of the aggregate table is AGGR_TIME_PROD_OM_FACT. The fact and

dimension information is contained in the one table. The sample Cognos Cube Designer

model contains an aggregate cube named gosldw_sales2, which is stored in the

gosldw_sales cube.

What you need to know before proceeding

The modeler needs to be aware of the nature of the aggregate table. This information

determines what you need to do to model the aggregate cube.

The primary aggregate table scenarios are degenerate dimensions, rolled-up dimensions,

parent-child dimensions, custom aggregation, and slicers.