88 IBM Cognos Dynamic Cubes
4.9.7 Slicers
You must identify the members that are slicers and add them to the aggregate cube definition
by adding them into the slicers tab of the aggregate cube editor.
4.9.8 Other modeling considerations
The objective is to choose the aggregate grain that will match the grain of the dimensions that
are contained the aggregate table. If you choose to map the aggregate grain of a dimension
to a level above the fact grain of the aggregate table, the query will still route to the aggregate
cube but then some aggregation will still have to be performed by the query engine. For
example, AGGR_TIME_PROD_OM_FACT has two columns that map to level keys of the time
dimension. They map to the level keys of the year and quarter levels of that dimension.
Although you could set the aggregate grain of the time dimension to be years, this might not
be the best approach because it would preclude some queries from routing to an aggregate
cube, defeating the purpose of the aggregate cube.
If you set the aggregate grain to below the grain of the aggregate table, you need to map level
keys to objects that do not exist in the aggregate table, which is quite difficult to do. If your
application needs to map that grain, the aggregate table must be modified to include that
grain.
Be sure to understand the concept of level keys. The keys of all the levels that you set as
included in the aggregate grain must be defined in the aggregate table. That is why
AGGR_TIME_PROD_OM_FACT, which is the aggregate table that is included in the Cognos
sample relational data base GOSLDW, does not have only the quarter level keys but also the
year level keys. If your level key needs to include keys from higher levels to uniquely identify
the members in the level there is no need to map the key twice. Although the object is used
more than once, there will be only one reference of the object to map.
Exploring the aggregate cube
Use the following steps to explore the aggregate cube:
1. Select the gosldw_sales cube.
2. Expand the Project Explorer tree node for the cube or right-click Open Editor and click the
Aggregates tab. You see an aggregate cube named gosldw_sales2.
3. Select gosldw_sales2 and right-click Open Editor. You see three dimensions: Time,
Order method, and Products.
4. Select Time.
To the side, you see a list of hierarchies and levels. In the case of Time, only one hierarchy
is listed, because it has only one hierarchy. If a dimension has more than one hierarchy,
probably only one actually participates in the aggregate table, because having more than
one hierarchy can add to the tuple, which in turn has an effect. Several levels show check
boxes that are selected: Year and Quarter levels. This selection indicates that the
aggregate level grain for the Time dimension is the Quarter level.
5. Deselect the Year check box.
The Quarter check box is cleared also, which indicates that you must include the keys of
higher dimension grains in the aggregate cube to correctly route the query.
6. Click the Quarter check box. The Year check box also becomes selected.
Chapter 4. Modeling dynamic cubes 89
7. Examine the other two dimensions.
Notice that Order Method has only one level. It might seem pointless to include Order
Method in the aggregate table, but actually it allows for a richer, more versatile aggregate
table. The set of queries that can be routed to the aggregate table is enlarged.
8. Click the Measures button.
You see two measures, Quantity and Revenue. In the mapping column, you see the
columns in the aggregate table that map to these measures. They are QUANTITY and
SALE_TOTAL.
9. Hover the mouse on one of the cells in the mapping column.
The aggregate table name and the column name appear in a tool tip. This step enables
you to trace the object mapping back to the source. For example, the tool tip for the
mapping for the measure Quantity displays the following text:
AGGR_TIME_PROD_OM_FACT.QUANTITY.
10.Return to the Aggregate cube editor and click the Level mapping tab. You see a list of
level keys, their source dimensions, and the aggregate table mapping.
11.Hover the mouse on one of the cells in the mapping column.
The aggregate table name and the column name appear in a tool tip. This step enables
you to trace the object mapping back to the source. Because
AGGR_TIME_PROD_OM_FACT is a degenerate dimension with level keys, it is sufficient
to map the cube to gosldw_sales2 with the level key mapping.
12.Return to the Aggregates tab.
13.Click the relationship edit links for any of the dimensions. The Relationship Editor opens. A
message indicates that no joins are necessary.
14.Click Cancel to close the editor.
15.Deselect the Dimension Grain check boxes for the dimension.
16.Click the Relationship edit links for that dimension.
You see that the message is gone. If you need to create a mapping to an aggregate table
through matching join keys, click the columns for the dimension and the aggregate table
and choose the keys to form a relationship. You do not need to specify level grains.
17.Click Cancel to close the Relationship Editor.
18.Click Undo to restore the level grain or manually click the level grain that was being used.
19.Click the New Dimension button. A dialog opens, which lists the dimensions that exist in
the cube but do not yet exist in the aggregate cube.
20.Click any of them and click OK. The dimension is added to the aggregate cube.
21.Select the new dimension and click Delete. The dimension is removed from the aggregate
cube.
22.Click the Slicers tab.
If you needed to add slicers, expand the member browser for the dimension, select them,
and add them to the slicer list.
23.Click the Implementation tab.
The Implementation tab shows a diagram representation of the aggregate cube. Because
AGGR_TIME_PROD_OM_FACT is a degenerate dimension, the diagram is fairly simple:
there is only one table in the aggregate cube. If the aggregate cube contained rolled-up
dimensions, they would be presented in the implementation diagram also.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.