Dynamic Cubes optimizations added to IBM Cognos 10.2.2 FP1

Content

Optimization of the loading of In-Memory Aggregates
In 10.2.2 FP1, during the loading of in-memory aggregates, Dynamic Cubes will load an in-memory aggregate from previously loaded in-memory aggregates. This is only supported for measures with associative aggregation rules (SUM, MAX, MIN, and COUNT).

This feature helps to reduce the number of queries posed to the underlying relational database, thus reducing load on the database and in most cases, reducing the time required to load a set of in-memory aggregates.

This feature is enabled by default but can be disabled by adding an advanced property named "UseStackedAggregates" to the query service and assigning it a value of "false".

Optimization of Calculated Measures and Members

Calculated measures and members are computed by the Dynamic Query Mode (DQM) server's MDX engine in each of the individual cells with which the measures or members intersect. As the MDX engine evaluates a calculated measure or member expression, it may determine that the engine requires the value for one or more (non-calculated) measures, which it retrieves from the underlying dynamic cube, which may in turn cause the cube to retrieve the necessary data from the underlying relational database.

In the presence of multiple calculated measures or members, this can result in a dynamic cube issuing a large number of nearly identical SQL queries to obtain data for each of the cells for each of the calculated measures or members. The overhead of executing a large number of these SQL queries leads to long report execution times.

In 10.2.2 FP1, Dynamic Cubes is able to consolidate these multiple SQL queries into a smaller set of SQL queries that are executed ahead of the execution of the MDX query, thus ‘priming’ the cube’s data cache with the data necessary for the evaluation of the calculated measures and members. Dynamic Cubes is able to perform this optimization for some, but not all, calculated measures and members, depending upon the various functions and expressions used within the calculated measures and members.

This feature's impact on performance can vary and is best ascertained by testing the performance of a particular workload with, and without, this feature enabled. As a consequence, this feature is disabled by default. The feature is controlled by two query service advanced properties, which means that the optimization is applied to all cubes hosted by the query service on a particular dispatcher. The advanced properties are:

qsEnablePrimingQuery
If set to 'true', the Dynamic Query Mode server will evaluate queries issued for execution to determine if they are suitable for this particular optimization. This evaluation incurs an overhead to query planning. If this feature is enabled, the goal is that the overall cost of this overhead is less than the gains obtained from the query optimization.

qsPrimingQueryThreshold
This advanced property is used to control which queries are evaluated for the optimization. Its value is a number greater than 0, with a default of 10. If a query contains less calculated measures and members (in total) than the threshold value, the query evaluation is skipped entirely, thus restricting the number of queries which are impacted by the query planning overhead of this optimization.

The number of MDX calculated measures and members compared to the threshold includes those created by the query engine in addition to those created by the report author or the business intelligence studio.

This particular optimization, when enabled, can result in a larger number of data values being placed in the data cache in comparison than with it disabled. Consequently, consider allocating additional memory to the data cache when this feature is enabled.

Additional tracing has been enabled for this feature. Within the configuration/xqe.diagnosticlogging.xml file the event group named "PrimingQuery" can be set to a level of "trace" to enable entries regarding this feature to be written to the OriginalAndPrimingQueryFile.xml. To enable the new settings you must stop/start the query service once the configuration file has been edited and saved.

Known Issues

In 10.2.2 FP1 the number of calculated measures and members counted by the feature is one less than the actual number.