Cognos report performance tuning - Transform nested Crosstab to single-edge Crosstab with much better performance (relational only)

Check list of all posts and the book 

Introduction
Please see sample below,
Nested nodes at row level are product number, product, product type code, product type, product line code, product line. Note that the product number is positioned at first, which is the lowest granularity for row nodes. such a cross tab report is used in many situation to get detail information for each product, but with dynamic columns, in this case year. the report performance is very poor because of multiple level nested nodes. This document demonstrate a new approach to dramatically improve performance with the same report output. The idea is to use the first level, or product number as only cross node, then turn all other nodes into properties of product number.  Please see implementation detail below, or check specification (10.2) for review.

Implementation

Step 1: define it as typical crosstab with nested nodes below and run it

 
It takes a very long time to run, as there are 5 levels of nested nodes. Looking into three layers, or database layer, Cognos application layer and Client layer, you can identify that the main issue is from Cognos engine, or BIBus processing. When checking dmc file under Cognos temp directory, it generate a big size of temp file.

Step 2, Unlock; create a table with 6 columns and 1 row in product number node member

Step 3, Drag all query items from query items to this table as below

Step 4, to make sure all columns will be aligned, and good to export to excel, you need to define the size of each items

Step 5, remove product number text and remove all other nodes

Step 6, run this report, you can see performance is much better, cut about 3/2 time. And the generated dmc size is only 1/3 of old approach.