How to prevent query from hell?

[abcuser]

Hi,
end-users are accessing data from Cognos TM1 v9.5.2 (without fixpacks/patches) on Windows 2008 RC2 from Cognos BI v8.4.1 Report Studio. End-users create reports by themself and execute queries. Queries are executed against TM1 server. But at least once per week end-user executes 'query from hell' = requires way to many data that TM1 is able to handle and so TM1 server crashes. I have to manually start TM1 server (Windows service) and from logs find out which which was the cube that was last executed, because this cube gets corrupted TM1 named levels (that is the error I am getting from Cognos BI server) - completely reloading cube with all data solves the problem. This is annoying problem and I would like to prevent it if possible.

Is there any way I can prevent this crashes? Like some setting to set maximum amount of data (or memory or something) that single user can consume?
Thanks

[Wim Gielis]

Does the property MaxViewSize can be of any help here?
It's a setting in the TM1s.cfg file.
It is specified in amount of MB, used at maximum to open a certain view.
Note, you cannot distinguish by cube, it's a server-wide setting.

[tomok]

The robustness of the inter-operability between TM1 and Cognos 8.4.1 is tenuous at best. I would NEVER allow clients to create their own queries into a TM1 cube in this environment.

[standtrue]

one option would be to create one or more "reporting" cubes in TM1 and grant users access to these cubes alone in BI.

The reporting cubes could have their values imported via TI process on a scheduled basis (only have C level rules) and be built in such a way that they would contain only the data required by specific user/user groups (so they can be smaller, involve less contention, easier to navigate, etc).

[abcuser]

Does the property MaxViewSize can be of any help here?

I have looked into MaximumViewSize parameter and as stated in documentation 500 MB of memory for 64-bit systems is set as default value. I think this default setting is OK on my 10 GB of RAM system. On the other hand I think using Cognos BI --> Cognos TM1 and TM1 view are not used, so this settings is probably useless in my case.



I am trying to reproduce 'tm1 crash' problem adding as much of named levels in Cognos BI Report Studio as possible, but I am never able to reproduce the "TM1 crash", but instead always get error in Report Studio (which is OK as expected):

Code: Select all

The MDX query submitted to TM1 server failed during execution. The TM1 error code is 88. The TM1 error message is "SystemOutOfMemory"

Any idea how to test to reproduce the TM1 crash?

Additional questions:
1. When some 'query from hell' is executed it consumes 100% of single CPU core. Is there any way to limit CPU resources used by single user using TM1 server?
2. As I see TM1 runs as single Windows process, if crash happens at one cube whole server (all cubes) are down. Is there any way of setting one cube per process (like in Hyperion Essbase) or any other way to prevent 'all cubes are down' problem?

[tomok]

1. When some 'query from hell' is executed it consumes 100% of single CPU core. Is there any way to limit CPU resources used by single user using TM1 server?

No.

2. As I see TM1 runs as single Windows process, if crash happens at one cube whole server (all cubes) are down. Is there any way of setting one cube per process (like in Hyperion Essbase) or any other way to prevent 'all cubes are down' problem?

No.

[David Usherwood]

I don't think TM1 is your problem here. (I will admit most of us here are TM1 fanbois, but even so... )
Cognos 8.x over TM1 is a bad idea - it was a real 1.0 job. Dynamic Query came in with Cognos 10.1 (I think) and TM1 9.5.2. This did a lot to sort out the interface though, personally, I'm not impressed with how Reporter (RS in CX) works with TM1 cubes - seems really limited and rigid. Have a look at the generated MDX - eeuw. And I don't think you can use your own. But I'm quite willing to believe it can be done better.
If the output you want comes out fine in Perspectives (and it should) then TM1 isn't letting you down.