Saturday, September 6, 2014

How to improve crosstab performance by removing default measure

  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.
  1. 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
  1. Normal with single default measure
4 seconds
  1. Measures on nested crosstab edge on row, (with default measure on crosstab corner)
65 seconds (14 times slower)
  1. Measures on nested crosstab edge on column (with default measure on crosstab corner)
33 second  (7 times slower)
  1. Measures on nested crosstab edge on row, (without default measure on crosstab corner)
4 seconds
  1. 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


  1. Sample

    1. Database

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


    1. FM

Go sales (query)


    1. 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)



No comments:

Post a Comment