TM1 Forum

Hi,
on Windows 2008 R2 64-bit I have created some reports in Cognos BI 64-bit v8.4.1 fp3 Report Studio that are accessing TM1 64-bit v9.5.2 cube.

The report I have been testing is executing for 5 minutes. From Windows Task Manager I can see first few seconds and last few seconds of report execution Cognos BI process is using 100% of CPU, but majority of time tm1sd.exe is using 100% of CPU.

I have turned on TM1 Performance Monitor and looked at StatsByCube cube (see attachment). This is small cube, it only occupies 92 MB of "Total Memory Used".

I have 10 GB of memory available on computer (currently assigned to this Cognos virtual machine, but I can get much more if I need it). Just wondering if there is some way to set TM1 to use more memory, for example I see "Memory Used for Calculations" is 0 also other memory parameters are zero (see attachment).

I would just like dramatically speed-up performance of executing some reports that are made in Cognos BI that access data from TM1 cube. Any tip what I can do?


P.S. I have noticed that all reports that are accessing level product_id from TM1 cubes runs 5 or more minutes (sometimes even 20 or more). Level is defined in HiearchyProperites cube and used in Cognos BI.

I am also attaching report definition and MDX that is created by Cognos BI. Any idea how to spead up performance?

Regards

The first thing to say is that there is no way to increase the amount of memory TM1 uses in order to improve the performance, the concept doesn't exist.

In order to track your problem I suggest that you narrow down where the performance hit comes from.
If you look at the same view in TM1 is it just as slow? If so repost giving a lot more information about the cube the data is coming from, i.e. is it ruled, skipchecked, number of values in the view etc etc.

If the TM1 time is small then this would suggest a problem with the MDX or BI itself, again repost giving as much detail about the set-up of your BI environment as possible.

Cheers,

My understanding is that prior to BI 10.1/TM1 9.5.1 the interface between the two was - how shall I put it delicately - 'not optimal' eg the elements were explicitly included in the generated MDX, zero suppression happened in BI, etc etc. You may need to upgrade to get it working well. Myself, I've found that pulling out small amounts of calculated values seems fine, so maybe you can do more work on the TM1 side to deliver the values you actually need.

Hi,
are you sure about this?

David Usherwood wrote:...zero suppression happened in BI...

I have send my tm1 model to one of IBM pre-sales personal and it tested on Cognos BI v10.1 (without any fixpack at the time of test few weeks ago, there was no fixpack) with TM1 v9.5.2 and the results were the same. One of IBMers said that fixpack 1 for Cognos BI v10.1 should solved the problem, so I hope I get some results from IBMers about fixpack 1. If this is really pre-v10.1 problem then I see a migration as required path, if not then no migration is planned for now.

I have created some views at TM1 server and it looks like Cognos BI does not use this views or data that are stored in this view in memory (Tests show exactly the same performance problem). Is there any way I can check if TM1 created view is used by Cognos BI?
Regards

abcuser wrote:Is there any way I can check if TM1 created view is used by Cognos BI?

Did you configure your query in Cognos BI to pull from a TM1 view? I doubt it's going to magically just know to utilize a cube view you created in TM1 unless you specifically instruct it to.

@abcuser:

are you sure about this?

Haven't done it myself (don't have the time to wade through the tedious BI installfest). It comes from the product team themselves though.
@tomok:
Can't recall that you can talk directly to a named view (as above, haven't got a current BI install to test on).

tomok wrote:

abcuser wrote:Is there any way I can check if TM1 created view is used by Cognos BI?

Did you configure your query in Cognos BI to pull from a TM1 view? I doubt it's going to magically just know to utilize a cube view you created in TM1 unless you specifically instruct it to.

BI has a link to a cube, it then pulls back the information required based on the query you have created. It will not (cannot) use any views that were created in TM1.

abcuser: to test it out you'll need to create a view in TM1 that looks similar to the report that you're doing in BI (per Steve's suggestion). I.e. retrieves the same data. If that view is fast then your problem is the connection.

Using Express I've found that BI takes a lot longer than TM1. David's comment about zero suppression being done in BI seems like the most likely culprit as the cube was quite sparse.

Martin

abcuser wrote:on Windows 2008 R2 64-bit I have created some reports in Cognos BI 64-bit v8.4.1 fp3 Report Studio that are accessing TM1 64-bit v9.5.2 cube

As you are using the 64-bit TM1 server software for Windows x64, did you add the optional parameter "LockPagesInMemory=T" to your TM1 server configuration file tm1s.cfg to prevent paging ?


To quote the manual TM1 9.5.1 Operations Guide on the optional parameter "LockPagesInMemory=T":

http://publib.boulder.ibm.com/infocente ... esInMemory
TM1 9.5.1 Operation
TM1 System Configuration > Parameters in the Tm1s.cfg File >

LockPagesInMemory

Parameter type:
* Optional
* Static

When this parameter is enabled, Windows trims pages from the TM1 process space, but does not page them to disk.

If a TM1 server running on a Windows 64-bit operating system is idle for a long period of time, physical memory taken up by the TM1 server will page out to disk. This is a function of the Windows 64-bit operating system and not TM1. This can cause performance degradation in large TM1 databases when trying to access data after an idle period.

To maximize performance when running a large TM1 database on 64-bit Windows, set LockPagesInMemory=T in the Tm1s.cfg file.

When this parameter is enabled, Windows still trims pages from the TM1 process space, but does not page them to disk. This benefits TM1 server performance because objects are no longer placed in virtual memory, but instead remain in physical RAM.

This sounds like a useful parameter for a large dev server that I host for a client. It can often be running for days before I get to it, and the RAM has slowly risen.

Are there any downsides to setting this parameter to T?

abcuser wrote:Hi, on Windows 2008 R2 64-bit I have created some reports in Cognos BI 64-bit v8.4.1 fp3 Report Studio that are accessing TM1 64-bit v9.5.2 cube. Any idea how to spead up performance?

Using TM1 as an OLAP datasource for Cognos BI, there are two Query modes: The (old slow) Classic Query Mode (CQM) and the (new fast) Dynamic Query Mode (DQM).

As you are using TM1 9.5.2, the only combination to support DQM is TM1 9.5.2 and Cognos BI 10.1 FP1, as (a) DQM is a feature of Cognos BI 10.1 and (b) TM1 9.5.2 supports only Cognos BI 10.1 FP1, it does not support Cognos BI 10.1


For more information on DQM see the manual Cognos BI 10.1 Dynamic Query:

http://publib.boulder.ibm.com/infocente ... query_mode
Dynamic Query 10.1.0
Dynamic query mode

moby91 wrote:As you are using the 64-bit TM1 server software for Windows x64, did you add the optional parameter "LockPagesInMemory=T" to your TM1 server configuration file tm1s.cfg to prevent paging?

Yes, I have added this setting, problem persists. If I understand correctly this is only for large cubes (from my first post in this thread it is seen that this is not a huge cube it is only 90 MB in size) and only for processes that are not accessed in some time.

moby91 wrote:As you are using TM1 9.5.2, the only combination to support DQM is TM1 9.5.2 and Cognos BI 10.1 FP1, as (a) DQM is a feature of Cognos BI 10.1 and (b) TM1 9.5.2 supports only Cognos BI 10.1 FP1, it does not support Cognos BI 10.1

I got response from IBM support today that they have tested my model in v9.5.2 and Cognos V10.1 FIXPACK 1 and the problem still persists.

Martin Ryan wrote:to test it out you'll need to create a view in TM1 that looks similar to the report that you're doing in BI (per Steve's suggestion). I.e. retrieves the same data. If that view is fast then your problem is the connection.

The view is taking minute or so to create. Is there any why to know if Cognos BI is really using the data from TM1 view?

Do this:

<parameter name="UseProviderCrossJoinThreshold" value="1000"/>
UseProviderCrossJoinThreshold introduces crosstab row / column edge member retrieval optimisations where the nested edge member crossjoin results in a large member set. The optimisation attempts to eliminate members from the crossjoin which would not return data. The established value is the crossjoin threshold from which thes optimisations start to be invoked.

<parameter name="UseNonEmptyOnDataQueryThreshold" value="1"/>
UseNonEmptyOnDataQueryThreshold ensure the NON EMPTY clause is added to an MDX query.

Hi

If there is a way to get BI to use TM1 Views I would like to know about it. As fas as I know all BI-TM1 communication is via MDX which ignores any predefined Views or Subsets in TM1.

We are using 9.5.1 with Hot Fix 14, and that appears to cure most of the zero suppression issues, which are the most likely reason for the poor performance.

We were finding that reports that took seconds to appear in the TM1 Cube Viewer were taking minutes in BI.

Regards

Paul Simon

kastellu wrote:Do this:

<parameter name="UseProviderCrossJoinThreshold" value="1000"/>
UseProviderCrossJoinThreshold introduces crosstab row / column edge member retrieval optimisations where the nested edge member crossjoin results in a large member set. The optimisation attempts to eliminate members from the crossjoin which would not return data. The established value is the crossjoin threshold from which thes optimisations start to be invoked.

<parameter name="UseNonEmptyOnDataQueryThreshold" value="1"/>
UseNonEmptyOnDataQueryThreshold ensure the NON EMPTY clause is added to an MDX query.

kastellu, I have reported a PMR and they give the same info as you posted. Change settings to above two parameters in <cognos_install>\configuration\qfs_config.xml file. I have done some in deep tests and have found out the following:
1. This settings is influencing very sparse reports. I have measured one report from 45 minutes to drop down to 10 seconds.
2. I have also found out that UseNonEmptyOnDataQueryThreshold set to 1 also produces incorrect values in some of the reports. It actually just hides some data in columns like measures which have some special rules on it. So I have turned this parameter to default 0 and only enable UseProviderCrossJoinThreshold to 1000. Performance is still 9 seconds. So UseNonEmptyOnDataQueryThreshold does not have any impact on performance in our case.

I now have an interesting situation. Most of reports (lets say 90%) are executed in 20 seconds, but some of them are executed in minutes like 5 to 45 minutes (10% of reports). Settings UseProviderCrossJoinThreshold to 1000 has effect only for long-running reports were time was reduced from 5-45 min to 10 seconds. This settings has no effect on reducing the time of majority 90% of reports which are still executing in 20 seconds.

Is there any way I can influence the rest of reports (90% of them) to increase performance? Is there any settings to increase performance for very sparse cubes? Is there some settings on TM1 to generally enable "null suppression"? I would really need to speed-up majority of reports that are not effected by above described parameters. Any idea?