Pinpointing Slow Cube Opening - Cube Dependency?

[ADW]

Hello,

I have cube which is 350 MB in size ('Memory Used'), quite small. The cube is mostly fed from 4 source/assumption cube data and then multiplied by a volume.

When the server has been restarted the cube can take 10 minutes to open (i.e. not time for server to come up on restart, but time after double clicking on cube). Thereafter the cube will usually open instantly, although slowly when it feels like it. The cube performance is very quick once opened.

Whilst tyring to find a solution to make the cube open quicker I stumbled across 'Adding cube dependency'. I notice from the log file that the system runs TM1.Cube.Dependency for around 30 lines when I first open the cube.

Is this what is making / would this make the cube slow to open? Or is it not possible to tell?

If it is I understand that I can create a TI to run these dependencies before opening the cube. Would this TI only require running once for each server restart?

Really need to speed up the opening of this cube (and a few others), so if I'm talking rubbish above, then advice on what else to inspect would be greatly appreciated.

[tomok]

The cube is mostly fed from 4 source/assumption cube data and then multiplied by a volume.

So what you are telling us is that the cube actually has no data in it, it's full of dynamically calculated values. In TM1 those values are not going to get calculated until a user asks for them in a cube view or sheet. That's by design.

When the server has been restarted the cube can take 10 minutes to open (i.e. not time for server to come up on restart, but time after double clicking on cube).

That's because the values mentioned above are being calculated. If you want to speed up the opening of the cube you need to create a Default view for the cube in question that is much smaller than current one in existence.

Thereafter the cube will usually open instantly, although slowly when it feels like it. The cube performance is very quick once opened.

Once the values are calculated they are cached so that subsequent retrievals are much faster.

Whilst tyring to find a solution to make the cube open quicker I stumbled across 'Adding cube dependency'.

Cube dependencies are an important topic but you're barking up the wrong tree for the problem above.

It's clear you have a limited understanding of the concepts in a RAM-based OLAP model. Suggest you spend some time reading the TM1 manuals.

[ADW]

Firstly thank you for your help and taking the time to respond.

Secondly, no need to be so patronising. Bit harsh that! (However great/amazing you are at understanding the concepts in a RAM-based OLAP modelling. Or how many TM1 manuals you may have read, or presumably written)

[tomok]

Bit harsh that!

Really????? Apologies if you took offense (or should I say offence?) Whatever happened to that stiff upper lip? Seriously though, nothing harsh about letting you know you need to spend some time reading about TM1. Not understanding the concept of dynamic calculations, caching of results, etc. with TM1 will severely hamper your ability to do much anything of value development-wise.

Firstly thank you for your help and taking the time to respond.

Probably should have stuck with this. BTW, where should I send your refund check?

[lotsaram]

Because cube dependencies get written to the message log at the end of rule evaluations many people assume (erroneously as Tomok pointed out) that setting the cube dependencies is somehow taking a lot of time. This is wrong, setting dependencies is near instant but it comes at the tail end of the evaluation of the calculated values and this is the time consuming action. When the server loads cube dependencies are only set initially for intercube feeders and dependencies are only set from the rules themselves when the rules are evaluated. By correctly setting the dependencies up front with TI you won't influence the time taken to load the view but what you can manage is the performance impact on other users as when the server encounters a new cube dependency while evaluating a rule it places a server wide lock (even with parallel interaction). Having the dependency already set avoids the locking impact on other users but it doesn't speed up the calculation.

To make the view open quickly (instantly) what you need to do is PRECALCULATE and CACHE the view using the ViewConstruct TI command. This is probably the secret sauce you were looking for. Note the cache is cleared whenever any data changes in the cube or in any cube where there is an "upstream" dependency which would mean the calculation needs to happen again and will take the same time. So to make the cube open quicker -precalculate view(s) with viewconstruct - make sure the default view and other public views are not excessively big or contain irrelevant data (like an "all years" consolidation) - look at improving the model design itself and make sure rules are efficient and not overfed, by the sounds of it you have some issues here.

For what it's worth ant at the risk or raising ire I think Tomok was a little harsh. But don't let that discourage you from reading up on how TM1 caching works, the advice was correct even if it could have done with more cushioning. This forum is probably the best place to find material.

[Duncan P]

If you like to read words on paper (which I do) you could do a lot worse than get hold of the IBM official guide to TM1 10.1 (mentioned favourably here http://www.tm1forum.com/viewtopic.php?f=20&t=6761). I found the section on calculation caching and "Stargate" views particularly useful.

[Alan Kirk]

If you like to read words on paper (which I do) you could do a lot worse than get hold of the IBM official guide to TM1 10.1 (mentioned favourably here http://www.tm1forum.com/viewtopic.php?f=20&t=6761). I found the section on calculation caching and "Stargate" views particularly useful.

Got nothing against words on dead trees for those so inclined.

But given that it's the year 2012, I have something against words which can only be found on dead trees, especially when coming from a supposed leading technology company.

But that having been said... it is actually available in Kindle format now. At a ridiculously inflated price (albeit a work-related and therefore tax deductible one) compared to any equivalent O'Reilly text I grant you (that would be the hand of McGraw-Hill at work, I expect; I rarely buy their titles for that reason), but at least it's available.

[ADW]

Not understanding the concept of dynamic calculations, caching of results, etc. with TM1 will severely hamper your ability to do much anything of value development-wise.

Not really wanting to get into an arguement about this. Just that you shouldn't assume people know nothing, and have never done, or are not able to do, anything of value all because they asked a question where the answer was obvious to you.

Lotsaram - thanks for your response. I think the model is good, with the cube opening very fast (now) after Tomok's suggestion.