Cognos Report Performance Issue - TM1Cube as Data Source

[ub14]

Hi,

Enviroment: IBM Cognos 10
TM1 9.5.2
We have developed few Reports in RS with data source as TM1 Cube. Cube is built of 9 dimensions and 5 facts. some of the dimension is having less elements <10. Each Report (excel as default format) is having 5 separate pages with respective dimension as key. All the reports are coming back in good time ( less than < 25 sec). One Dimension which is having 50 elements is taking more than 7 minutes to generate excel output. Once i filter out this dimension limited elements (5), the report is coming back in good time. The only difference i could find out with rest of the dimensions is no elements.

We would just like speed-up performance of executing this report. Any ideas how to speed up the performance?

[David Usherwood]

First I would create views in 'pure' TM1 (Architect or Perspectives) and compare performance to the BI versions. It's quite possible that your source cube's feeders aren't right.
As a side note, TM1 has no concept of a 'fact' - all dimensions work the same way. But you do nominate a dimension as a Measure to keep BI happy.

[moby91]

Are you aware of the two optional parameters UseProviderCrossJoinThreshold and UseNonEmptyOnDataQueryThreshold ?

http://www.tm1forum.com/viewtopic.php?p=22278#p22042
Re: How to set the use of more memory to spead up performance


For more information see the Cognos Proven Practice

http://www.ibm.com/developerworks/data/ ... ge529.html
The IBM Cognos 10 Dynamic Query Cookbook

Summary: This document is intended to provide a single point of reference for techniques and product behaviours when dealing with the Dynamic Query Mode delivered with IBM Cognos 10.

http://public.dhe.ibm.com/software/dw/d ... okbook.pdf

[ub14]

Hi,

Enabling use dynamic query mode while publishing the package improved performance a lot. The report is execution brought down to 2 min from 7 min.

There is no improvement after modify the below paramters in
C10/configuration/qfs_config.xml file under the TM1OlapODPXQE provider.

<parameter name="UseNonEmptyOnDataQueryThreshold" value="1"/>
<parameter name="UseProviderCrossJoinThreshold" value="10000"/>

Any other things we need to look in to improve the performance of the report.

[ParisHilton]

Hi forumites,
I'm currently experiencing some terrific gains in performance from checking the 'Dynamic Query Mode' tick box in Framework manager and would just like to add a couple of quick comments regarding my experience in case it helps return just a tiny drop into the ocean of knowledge on this forum.


1) Make sure you have Fix Pack 2 installed, otherwise running reports seems to bring down your TM1 'server' rather regularly. This is noted in the FP2 issues.

2) It appears if you have checked the DQM box, you can't connect to a relational data source in FM. Probably nothing to worry Tm1ers, and I think you can choose some data source to use DQM and some not, but clearly you need to think about what you're doing for a bit, rather than blindly go round ticking boxes !

[kwijibo]

Hi,

make sure you have enabled the null suppression for the cognos query, as performance really benefits from that.
In order to do that go to the query properties and choose "nulls" for the Parameter "Suppress" unter the section "Query Hints".

You might also consider to set the values for the stargate view creation in the TM1 cube properties as you can read about here (documentation auf 9.4.1, but it's still valid):
http://publib.boulder.ibm.com/infocente ... 00C08.html
http://publib.boulder.ibm.com/infocente ... 702DC.html (Parameters VMM and VMT)

Best regards