If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Performance of Cognos 8.3 with Service Pack 3

Started by sanjay0828, 11 Jun 2009 11:02:13 AM

Previous topic - Next topic

sanjay0828

In Cognos 8.3, when we have multiple members per dimension in the context filter, the performance of the report goes down.
This is because for each such dimension, Cognos creates two calculated members.

The first one aggregates the individual members in the dimension. This is needed and this is how we normally do it.
However, a second calculated member is created, which is used in the WHERE clause of the MDX and this causes the performance to degrade.

WITH
MEMBER [Time Monthly].[Time Monthly].[COG_OQP_INT_m4]
AS 'AGGREGATE({[Time Monthly].[Time Monthly].&[1.21E2], [Time Monthly].[Time Monthly].&[1.22E2], [Time Monthly].[Time Monthly].&[1.23E2], [Time Monthly].[Time Monthly].&[1.24E2]})', SOLVE_ORDER = 1

MEMBER [Time Monthly].[Time Monthly].[COG_OQP_INT_m2]
AS 'IIF([Measures].CURRENTMEMBER IS [Measures].[Purchase Quantity],
([Time Monthly].[Time Monthly].[COG_OQP_INT_m4], [Measures].[Purchase Quantity]),
AGGREGATE({[Time Monthly].[Time Monthly].&[1.21E2], [Time Monthly].[Time Monthly].&[1.22E2],
[Time Monthly].[Time Monthly].&[1.23E2], [Time Monthly].[Time Monthly].&[1.24E2]}))', SOLVE_ORDER = 1

SELECT {[Measures].[Purchase Quantity]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON AXIS(0)

WHERE ([Time Monthly].[Time Monthly].[COG_OQP_INT_m2])

The second calculated member [Time Monthly].[Time Monthly].[COG_OQP_INT_m2] causes the performance to degrade.

Cognos 8.4 does not have this problem.
In 8.4, no calculated members are created for the context filter.
Instead in the WHERE clause, the following MDX statement is used :-

WHERE ({[Time Monthly].[Time Monthly].&[1.21E2], [Time Monthly].[Time Monthly].&[1.22E2], [Time Monthly].[Time Monthly].&[1.23E2], [Time Monthly].[Time Monthly].&[1.24E2]})

But, since this version is relatively new, my client is having concerns regarding the stability of the tool.

But, they are okay to upgrade to Cognos v8.3 SP3.
Please could someone check whether the MDX in v8.3 SP3 has this problem?
We would like to first confirm that there is an improvement and then go for the upgrade.

Many thanks in advance,
Sanjay