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.
86 IBM Cognos Dynamic Cubes
If the aggregate table is a degenerate dimension you need to know the nature of the key in
the aggregate table. The nature of the key determines your modeling actions. It is possible
that the key allows you to map the aggregate cube to the level keys of dimensions in your
cube. It is possible that the key requires you to create a relationship between the aggregate
table and a dimension.
If a key in the aggregate table matches the key of a level then you can map the appropriate
levels to the aggregate table. For example, if the keys in the aggregate table contain the level
keys of a Time dimension’s Year and Quarter levels then you can choose to map the Time
dimension to the aggregate cube. A query that used Year or Quarter is routed to use the
aggregate table. The aggregate table, in relational query terms, is essentially (if not in fact) a
view. AGGR_TIME_PROD_OM_FACT is this type of aggregate table.
If a key in the aggregate table matches the key in a level such that the key can be used to
identify a relationship between the dimension and the aggregate cube, you can create
relationships between the aggregate table and the dimension table objects. The aggregate
table becomes an alternate fact table with relationships that are defined between it and its
participating dimensions.
It is possible that the aggregate table has associated rolled-up dimension tables. You then
need to model the aggregate cube to include them. The aggregate table and its rolled-up
dimensions will be a relational schema separate from the schema of the detail fact table and
its relational schema.
It is possible that one dimension in the aggregate cube is a parent-child dimension.
Parent-child dimensions do not have defined levels. The data of the parent and child
attributes determine how the members are generated and assembled into a member tree.
The aggregate table has a record for each member. The mapping allows the query to route to
the aggregate table.
You need to know if a measure in the cube has custom aggregation. If it does, then your
aggregate table needs to support it too. The mapping is similar to a parent-child dimension.
Custom aggregation is a method to predefine the aggregation of member values outside of
the aggregation functionality of Cognos Dynamic Cubes. For more information about custom
aggregation, consult the Cognos Dynamic Cube user guides.
You also need to identify if the aggregate tables use slicers, which are partitioned levels. If
you do not include a member that is a slicer in the slicer definition, a query that involves that
member will be routed to the detail fact table unnecessarily. If you include a member that is
not a slicer in the slicer definition, you can cause the query to route to the aggregate cube
when it should not. The query will return a NULL value.
What you need to do for each case
For any particular aggregate table scenario, you need to do several tasks to correctly model
the aggregate cube. Some are common to all cases. Some are specific to one case. The
following sections describe each of these tasks.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.