236 IBM Cognos Dynamic Cubes
Figure 8-23 is an example of the gosldw_sales metrics showing database aggregate table hit
rate.
Figure 8-23 Example of the gosldw_sales metrics showing database aggregate table hit rate
8.5.5 Database aggregates tips and troubleshooting
This section provides troubleshooting information for database aggregates.
Suggested practices for database aggregates and Cognos Dynamic
Cubes
The following suggested practices are related to database aggregates and Cognos Dynamic
Cubes:
? Use the Aggregate Advisor to get recommendations for aggregates.
? Consider stacked aggregates, that is, having aggregates that are derived from one
another. This practice can contribute to the following benefits:
– Higher level aggregates to be derivable from lower aggregates.
– Improved maintenance time.
– Deep aggregates to cover more queries, and upper aggregates to give better
performance.
? In Cognos Cube Designer, model the measures as additive when possible.
If the measure is defined by an expression and the expression can be considered as
additive, set the regular aggregate type to SUM. This practice will enable the aggregate
routing logic to use the aggregate cube and will enable the Aggregate Advisor to consider
including it in its recommendations.
? For measures, use SUM and COUNT aggregates rather than AVERAGE, where possible.
? De-normalize the aggregate table to eliminate the need to join tables.
Chapter 8. Optimization and performance tuning 237
Suggested practices for modeling aggregate cubes in Cognos Cube
Designer
When modeling aggregate cubes in Cognos Cube Designer, model smaller aggregates at
higher levels first, so that the aggregate cube is assigned a lower ordinal value. Alternatively,
the ordinal property can be updated after the aggregate cubes are created. Aggregate cubes
with lower ordinal values will be considered before those with higher ordinal values in the
Cognos Dynamic Cubes routing logic.
Aggregate cubes with the following types of traits should be assigned lower ordinal values:
? Highly aggregated tables, that is, those with a small number of rows
? Frequently used aggregate tables
Aggregate cubes with the following types of traits should be assigned higher ordinal values:
? Aggregate tables at lower levels of aggregation, that is, those with a larger number of rows
? Infrequently used aggregate tables
Troubleshooting Cognos Dynamic Cubes database aggregate routing
This section provides troubleshooting information for database aggregate routing.
Disable external database aggregate routing
The first step in troubleshooting a potential in-database aggregate query problem is to
determine whether the problems is the result of the database aggregates. Disable the
external aggregate support and rerun the scenario without the database aggregate
awareness to see if the correct data values are returned:
1. Go to IBM Cognos Administration.
2. Select Status.
3. Select System.
4. Select the server.
5. Select the dispatcher.
6. Select QueryService.
7. In the QueryService context menu, select Set properties.
8. Select the Settings tab.
9. In Dynamic cube configurations, select Edit in the Value column.
10.Each cube has its own aggregate cache, so select the pencil icon next to gosldw_sales to
edit the cube’s configuration properties.
11.Select the Disable external aggregates property.
12.Click OK.
Restart the cube for the updated setting to take effect. Although aggregate cubes may be
defined in the cube model, selecting to disable this setting will not consider any of them for
routing.
Using the DQM server log to understand database aggregate routing
This section describes advanced troubleshooting techniques by using more verbose logging
levels and examining the DQM server log.
238 IBM Cognos Dynamic Cubes
To generate some insight about which aggregate cubes are considered and why they are
selected, enable the logging event group specifically for the database aggregate
routing-related activity:
1. On the QueryService system, edit the DQM logging configuration, which is located relative
to the server installation directory:
configurationxqe.diagnosticlogging.xml
2. Edit the aggregate cache event group to log level of trace:
<eventGroup name=" ROLAPQuery.AggregateStrategy" level="trace"/>
3. Save the xqe.diagnosticlogging.xml file.
4. Restart the QueryService for the logging level changes to take effect.
5. Start the cube.
6. Run the problem query.
7. By default, the DQM server log is written to a file, which is located relative to the server
installation directory:
logsXQExqelog-<timestamp>.xml
8. When troubleshooting by using the DQM server log is complete, revert the changes to the
log level in the xqe.diagnosticlogging.xml file, and then restart the QueryService for the
changes to take effect.
As queries are processed in Cognos Dynamic Cubes, the analysis of which aggregate cubes
are considered, which are rejected, and which one is selected, gets written to the DQM server
log. A single user query may be decomposed into smaller queries so that some values that
can benefit from database aggregates are routed to an aggregate table and other values are
routed to the underlying warehouse tables.
The analyses for each of the smaller queries are found in log entries for the database
aggregate routing event group denoted by the <aggregateAnalysis> element. The aggregate
analysis element consists of the following sections:
? Original query: Lists the measures, levels, and each level’s dimension and hierarchy from
the input query for consideration.
? Aggregates considered: Lists all of the matching aggregates that are qualified for routing
the input query for consideration. There might be more than one matching aggregate cube
that is qualified and considered as a match.
? Aggregates selected: Describes the final aggregate that are selected from one or more
qualified aggregates for consideration. It also describes the reason for choosing this
aggregate.
? Aggregates not matching: All the aggregates that do not qualify for routing are listed here.
The reason for not qualifying is also described for each non-matching aggregate. Typically,
reasons are as follows:
– Measure mismatch: The input query measure aggregation is not listed as part of the
aggregate cube, and therefore cannot be used.
– Level mismatch: The levels at which the aggregate cube is defined do not match with
the level of aggregate of the input query.
– Aggregate slice mismatch: The slice coverage of the aggregate cube is not enough to
satisfy the input query even though the measure and levels are matching. For example,
if the aggregate cube is defined for slice of Time Year 2012, and the input query is
Chapter 8. Optimization and performance tuning 239
asking for measures with Time Year 2011 and 2012, the aggregate cube with only 2012
slice cannot be used to satisfy the input query.
– Aggregate cannot be rolled up for non-additive measure: Aggregates with additive
measures, such as SUM and COUNT, can be used to satisfy queries of the higher
level. For example, an aggregate with a SUM measure and Time levels to Quarter can
be used to satisfy a query at the Year level because the values from the Quarter level
can be added, or rolled up, to get the Year value with this aggregate. For non-additive
measures, such as AVG and STDDEV, the roll up cannot be done for higher level
queries. In this case, the aggregate is not selected with this reason.
Consider the cross-tab report (in Figure 8-24) against the gosldw_sales cube for Time Year
by the measures, Gross profit, Quantity, Revenue, Unit cost, Unit price, and Unit sale price.
There is an aggregate cube, gosldw_sales2, that maps to the
AGGR_TIME_PROD_OM_FACT database aggregate table, which aggregates measures
Quantity and Revenue at Time Quarter and Products Product type levels. In this example
scenario, there are no in-memory aggregates enabled, only the database aggregates.
Figure 8-24 Cross tab report of Time Year by gosldw_sales measures
The log entries in Example 8-11 show cross-tab report user query, decomposed into two
smaller queries and their corresponding aggregate analysis. One query does route to the
gosldw_sales2 aggregate because the measures and levels match.
Example 8-11 Cross-tab report user query (1)
<event component="XQE" group="ROLAPQuery.AggregateStrategy"...>
<![CDATA[<aggregateAnalysis>
<v5Report name="unknown"/>
<originalQuery>
<measures>
<measure name="Quantity"/>
<measure name="Revenue"/>
</measures>
<levels>
<level name="Year" dimension="Time" hierarchy="Time"/>
</levels>
</originalQuery>
<aggregatesConsidered>
<aggregateCube name="gosldw_sales2" ordinal="1"/>
</aggregatesConsidered>
<aggregateSelected>
<aggregateCube name="gosldw_sales2" reasonForChoosing="Only matching aggregate
cube found." SQLExecTimeInMS="-1"/>
</aggregateSelected>
<aggregatesNotMatching/>
</aggregateAnalysis>
]]></event>
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.