How to improve crosstab performance by removing default measure

Check list of all posts

1. Introduction

To insert measures on nested crosstab edge give business users to see more than one measure at the same time. It is necessary based on business requirements. However, this definition will cause Cognos engine to bring all records into Cognos server, and performance is too poor to acceptable. the problem is that we the default measure on crosstab corner is not removed.

3. Test result

A sample is created with sample database and package from Cognos. The test result is listed as follows

Cases	Waiting time for first page to appear
Normal with single default measure	4 seconds
Measures on nested crosstab edge on row, (with default measure on crosstab corner)	65 seconds (14 times slower)
Measures on nested crosstab edge on column (with default measure on crosstab corner)	33 second  (7 times slower)
Measures on nested crosstab edge on row, (without default measure on crosstab corner)	4 seconds
Measures on nested crosstab edge on column (without default measure on crosstab corner)	4 seconds

The detail sample will described below for you to reviwew

4. Sample

1. Database

SQL server sample database from Cognos GOSALES  (you can use oracle or DB2 database and should produce the same problem)

2. FM

Go sales (query)

3. Reports

Case 1 Normal with single default measure

Case 2 Measures on nested crosstab edge on row, (with default measure on crosstab corner)

Case 3 Measures on nested crosstab edge on column (with default measure on crosstab corner)

Case 4 Measures on nested crosstab edge on row, (without default measure on crosstab corner)

Case 5 Measures on nested crosstab edge on column (without default measure on crosstab corner)