TM1 Performance Declined

[comma]

I've tried to search the forum but I can't find any thread with same problem as mine.

The problem is the time required to open the cubes in my server has been increasing lately, a.k.a the performance is lowering down.
As an example, when we first start using TM1, cube A took 1 minute to open. Now it took 2 minutes.

Does it have anything to do with the number of data in the cube? Because at first we were only using 1 budget version, while now we've used up to 4 versions.
If it is because of that, does anyone have any tips to increase the performance back again?

I've tried to restart the TM1 server, even the Windows Server, but no luck.
Thanks fellas.

[Steve Vincent]

2 simple rules to TM1 speed; the more data you put in, the slower it will get. The more complicated the calculations, the slower it will get.

Going from 1 to 4 scenarios will most certainly slow it down, depending upon the analysis your customers are doing. If your cubes use rules this will likely exacerbate the issue. The solution depends on your models; if some scenarios are read only (ie. No more data is changing) then maybe any rules could be withdrawn and replaced with data instead. Or maybe data could be archived and some scenarios withdrawn. Without knowing what the models are doing its difficult to advise, but I always look at rules first and data archival second.

[comma]

The solution depends on your models; if some scenarios are read only (ie. No more data is changing) then maybe any rules could be withdrawn and replaced with data instead.

Yes, for past versions we're only keeping it for comparison purpose only.
How do you usually withdrawn rules and replace it with data? Because once the rules are turned off, I believe the data will be gone?

[tomok]

How do you usually withdrawn rules and replace it with data? Because once the rules are turned off, I believe the data will be gone?

The concept is simple although the execution of that might not be. It depends on how your rules are written. You will want to take a particular year or version, or whatever, and export out the values to a flat file, making sure you don't check the box that says to skip rule calculated vales. Then you go to the rules and turn off the calculation of the period/version you are about to load in as data. Then you load the data back in.

Like I said, in concept it is simple but changing the rules so that certain versions and/or years no longer have calculated values may take some serious modifications to your rules. You would be advised to go back to your developer and get their assistance on this. If you are the developer then all I can say is good luck. Based on the questions I have seen from you in this forum you appear to be in way over your head.

The other thing I want to mention is to look at the default view set up on the cube(s). If your default view (the view that opens whenever a user just double-clicks on the cube name) is looking at the top of the house, meaning it is pulling highly summarized data, then you may be needlessly calculating values and the "open the cube" time will get longer and longer the more data you have in the cube. I always establish a default view for every cube that pulls from a lower level in the cube, or a small piece of the cube, so that the calculation time will be quick whenever a user just double-clicks on the cube object versus opening a particular view. This may be your culprit.

[lotsaram]

Just to elaborate on what has already been said having more data in a cube will mean the cube will take longer to load (this will only be an issue for users if the cube is not pre-loaded) and having more data/more rules in the cube will mean that views will take longer to open/calculate only if the views are requiring calculation of the additional data.

If you have a cube with 5 versions and this increases to 50 versions a view which refers to only a single version would not necessarily take any longer to calculate.

It is important to make sure that all public views and in particular default views are set up intelligently so that only required data is calculated. Pay particular attention to using subsets rather than "unregistered subsets", the size of subsets used and avoiding dynamic subsets. Depending on whether your cubes are updated intra-day you might also want to consider pre-calculating views with ViewConstruct as in a reporting environment this can lead to significant performance gains for users.

[comma]

Like I said, in concept it is simple but changing the rules so that certain versions and/or years no longer have calculated values may take some serious modifications to your rules.

Don't I just have to add this rule at the first line?

Code: Select all

['Version1'] = N: STET;

Am I overlooking things here?

If your default view (the view that opens whenever a user just double-clicks on the cube name) is looking at the top of the house, meaning it is pulling highly summarized data, then you may be needlessly calculating values and the "open the cube" time will get longer and longer the more data you have in the cube.

Yeah, I understand this. But the users are also complaining the time required to load when they changed any elements, not just when opening cubes.

and avoiding dynamic subsets

How much will dynamic subsets affect the load time? Is it significant?
Because if I don't use dynamic subsets, it'll be harder to maintain the subset since we add new elements quite often.

[Martin Erlmoser]

do you use skipcheck & feeders in your rules ?

[comma]

do you use skipcheck & feeders in your rules ?

Yes, I do.

[lotsaram]

do you use skipcheck & feeders in your rules ?

Yes, I do.

Yes but are your rules and feeders correct/efficient? Have the rules been checked by someone with a significant depth of TM1 expertise? I ask the question because the query times to rebuild a view that you are talking about are extremely long. I would only expect those sort of times in an extremely large cube of many GB when looking at quite a consolidated view with many rule calculations. Typically even for large cubes with very large dimensions of thousands or even hundreds of thousand members view recalculation is usually a matter of seconds not minutes. If your query time is this long then it is more than likely you have done something wrong, most likely with inefficient rules and/or overfeeding.

How much will dynamic subsets affect the load time? Is it significant?
Because if I don't use dynamic subsets, it'll be harder to maintain the subset since we add new elements quite often.How much will dynamic subsets affect the load time? Is it significant?
Because if I don't use dynamic subsets, it'll be harder to maintain the subset since we add new elements quite often.

The impact of dynamic subsets will depend on a number of things - the number of users on the system doing queries simultaneously, the size of the dimensions that the dynamic subsets apply to and the complexity of the MDX queries. You can have TI take a nightly copy of dynamic subsets to static copies so there should be no additional maintenance. Typically meta data is updated overnight at which time subsets can also be updated, it is very seldom that subsets need to dynamically change at the drop of a hat to reflect instantaneous user input.

As an aside are you REALLY using 9.5.0? This was for practical purposes a beta version and shouldn't be used by anyone for any serious purpose (I'm sure even IBM would agree with this). You should upgrade to 9.5.1 at the earliest opportunity!

[Martin Erlmoser]

whats about performance monitoring, is the server swapping heavily? also system specs could be interesting, tm1s.cfg, }statsbywhatever cubes, how often do you reboot your tm1server?

is the performance overall bad or only with specific elements (+,- calc rule consolidations?) / cubeviews
can you exclude a client or connection issue, is the performance on the server itself also bad?

there are thousands things which can cause such problems.

[comma]

Yes but are your rules and feeders correct/efficient? Have the rules been checked by someone with a significant depth of TM1 expertise?

Our model was developed by third party consultant. So even if it's not written in the most efficient way, I think (or I hope) it's at least close to that.

I ask the question because the query times to rebuild a view that you are talking about are extremely long. I would only expect those sort of times in an extremely large cube of many GB when looking at quite a consolidated view with many rule calculations.

Yes, it's a big cube indeed because it's P&L statement cube, and it pulled data from every other cubes. The account dimension is pretty big too, and the hierarchy depth is up to 12 levels. Knowing this is P&L cube, do you still think 1 minute is too long? If yes, how long is it supposed to take to load this cube if the rules and feeders are correct?

it is very seldom that subsets need to dynamically change at the drop of a hat to reflect instantaneous user input

Maybe it's hard too believe, but that happens quite often here

As an aside are you REALLY using 9.5.0? This was for practical purposes a beta version and shouldn't be used by anyone for any serious purpose (I'm sure even IBM would agree with this). You should upgrade to 9.5.1 at the earliest opportunity!

Yes, we are. I want to upgrade it ASAP too, but we're still at the peak of our budget cycle. Still have to wait until it cool down a lil bit.

[comma]

whats about performance monitoring, is the server swapping heavily?

Unfortunately, I didn't start the Performance Monitor. Is it really that important?
Btw, what do you mean by server swapping?

also system specs could be interesting, tm1s.cfg, }statsbywhatever cubes, how often do you reboot your tm1server?

The server is 8 core 2.4 GHz with 64GB of RAM. At the moment, the RAM used is about 7GB. As for config, what parameter is important? I used IntegratedSecurityMode = 5, and I think the other params don't really matter, CMIIW. And I rebooted the TM1 server and the Windows Server last week.

can you exclude a client or connection issue, is the performance on the server itself also bad?

Yes, I believe I can exclude client or connection issues. The overall performance of the server is pretty good too, and it's dedicated to TM1 only.

Btw, no one has answered my question about the rule. Any advice?

Don't I just have to add this rule at the first line?

Code: Select all

['Version1'] = N: STET;

Am I overlooking things here?

Thanks guys, I really appreciate all of your answers so far, and I'm still expecting your further help

[lotsaram]

['Version1'] = N: STET;
Yes if you want to revert to data entry and no rules for version1 then stetting the rule out in this manner as the 1st or one of the 1st lines in the rule file is all you need to do. But if it is a matter of performance then it is equally if not more important to restrict feeders.

Your model is not that big and yes from what you describe 1 - 2 minutes to calculate views is too long (by a long way). By the sounds of it your model is probably suffering from a few design issues which should be addressed once you have finished the current budget cycle. With proper design a view refresh on an income statement cube should be instantaneous to seconds at most.

Although you say the model was developed by an external consultant this is no guarantee that your system was built to good standards. Presently there are many IBM partners implementing TM1 (or attempting to) who's depth of TM1 experience is ankle deep at best. Unfortunately the same goes for many of IBM's own consultants too but at least they should have access to competent people with experience within IBM. This kind of statement on this forum may be getting to be sounding like a broken record but it is true, experience counts when it comes to build quality, much more so with TM1 than with many other products. I can't imagine that any experienced TM1 partner would go live with version 9.5.0 so the fact that you are using this version raises suspicions.

[tomok]

Yes, it's a big cube indeed because it's P&L statement cube, and it pulled data from every other cubes. The account dimension is pretty big too, and the hierarchy depth is up to 12 levels. Knowing this is P&L cube, do you still think 1 minute is too long? If yes, how long is it supposed to take to load this cube if the rules and feeders are correct?

So, you have a P&L cube that pulls from every other cube, probably through intercube rule refernces and a 12 layer deep hierarchy. I'm also going to guess that you have different dimensionality in some of these source cubes so that you are having to summarize data before passing to the P&L cube. This type of setup is ripe for performance problems. I'm not saying it's wrong, it may be the correct way given your user requirements. You just have to understand that in a design like this it is absolutely imperative that your rules are done the most efficient way possible and you don't overfeed. Even if everything is done correctly this type of design can be slow. Sometimes the only way to speed it up is to remove some of the cubes from dynamically feeding the summary cube and relacing it with a data transfer via TI. I would only do this as a last resort since doing so removes the advantage TM1 has over the other tools out there.

[jim wood]

We went down the route of removing dynamic links and replacing them with TI Processes. We only did this however as our model was getting very large. (before the change it was runnign at 70GB) Taking this option lowered our memory usage (down to sub 50GB) and startup times and increased peformance. The processes do now however take longer than the did before. Most of our information is loaded on a weekly basis so we are able to get away with it.

[comma]

['Version1'] = N: STET;
Yes if you want to revert to data entry and no rules for version1 then stetting the rule out in this manner as the 1st or one of the 1st lines in the rule file is all you need to do. But if it is a matter of performance then it is equally if not more important to restrict feeders.

Now after I think it again, changing the Rules won't be the best solution, or at least not the easiest one.
Because I'll have to change the Rules in all of my cubes every time we're moving to a new budget version or to a new year.

Although you say the model was developed by an external consultant this is no guarantee that your system was built to good standards. Presently there are many IBM partners implementing TM1 (or attempting to) who's depth of TM1 experience is ankle deep at best. Unfortunately the same goes for many of IBM's own consultants too but at least they should have access to competent people with experience within IBM.

Actually, one minute is the best that IBM can give to us. Because they've looked into our model, they said that's how long it supposed to be. And since the user was happy with the load time at that moment, we accepted it.
But now it's taking longer and longer (it could hit up to 4 minutes now). Maybe I'll ask IBM again.

Thanks for all of your suggestions and replies guys. I really appreciate it.