Enhanced null suppression

Suppressing rows and columns that contain only null values makes a report easier to read.

For example, a product that has no sales for a given quarter may result in a very large report with thousands of cells that contain no data.

The time required to evaluate a table to determine which rows and columns contain only null values is mainly determined by the number of cells in the table. Other factors such as the nesting levels on the axes and the use of complex calculated columns might also affect the time.

The number of cells in a table is determined by the number of cross joins, which are the Cartesian product of member sets. The number of cross joins are calculated as follows:

crossjoin ({a1,a2}, {b1,b2}, {c}) = {(a1,b1,c) (a1,b2,c) (a2,b1,c) (a2,b2,c)}

In the following example, the resolved edge has 1 × 1 × 170 × 818 × 818 = 113,751,080 tuples, or cells, to process.

The rows of a crosstab showing five nested sets, which have the following number of tuples, from left to right: 1, 1, 170, 818, and 818 — Figure 1. A crosstab containing five nested sets.

IBM® Cognos® Analytics allows query optimizations for suppression when authoring reports with OLAP data sources in IBM Cognos Query Studio and IBM Cognos Analytics - Reporting, where you can enable suppression.

If a cell's expression evaluates to null, it does not have to be computed during query evaluation, which reduces the number of cells that need to be processed.

The results vary based on a combination of the structure of the cube, the cardinality, the style of report, the size of the query, and the sparsity of the results.

In IBM Cognos Analysis Studio, when using a package with the dynamic query mode enabled, null suppression of rows and columns is processed in the data source, which results in improved performance.

For more information about null suppression and implementing optimizations specific to IBM Cognos TM1® data sources, see the IBM Redbooks® publication, IBM Cognos Dynamic Query (www.redbooks.ibm.com/abstracts/sg248121.html).