Optimization question

[ioscat]

Hello TM1 community!
I have calculation that spread some value (total loss) based on data from another cube (detailed profit).

Code: Select all

detailed loss=total loss * DB(detailed profit) / DB(consolidated profit)

Is it better to create additional item in profit cube that calculates

Code: Select all

spread multiplier = detailed profit / consolidated profit

and that value used by loss cube

Code: Select all

detailed loss=total loss * DB(spread multiplier)

Will it calculate faster? And in case if i have multiple cubes for spreading different types of loss (electricity cost, taxes, ...)

Moscow weather: it's icy raining: rain and -4 deg Celcium

[ioscat]

My comrade said that caching will behave like premultiplication and improve overall calculation...

[Steve Rowe]

Good Q..

The answer is it depends on the how the frequency of "DB(detailed profit) / DB(consolidated profit)" varies with the frequency of "detailed loss".

If there is a 1:1 relationship then there is no benefit since for every "DB(detailed profit) / DB(consolidated profit)" there is a "detailed loss". The less this true the better off you are creating DB(spread multiplier).

So say there are 1000 "detailed loss" where "DB(detailed profit) / DB(consolidated profit)" evaluates to the same location then you save yourself calculating "DB(detailed profit) / DB(consolidated profit)" 999 times, assuming no new data into your system, because the first time "DB(spread multiplier)" is calculated it is cached (in theory).

There is a trade off to this in RAM terms since you will be storing DB(spread multiplier).

(back in the "good old days" when we had little memory and a faster TM1 we would actually go the other direction and work harder to not calculate measures that the user didn't want to see and only being done to make the maths easier, this piece of recieved wisdom I guess needs to be revised!).

I should say that above is all theory and I've not taken the time to prove it out.

Cheers

[Duncan P]

The question about the ratio of unique values to places in which they are used is a good one. However it does rely on the value being cached once it has been calculated. This depends on the number of steps it took to calculate, which is compared to the CalculationThresholdForStorage for which the default value is 50.

If your [detailed profit] / [consolidated profit ] takes less than 50 steps to calculate then it won't be stored. This is true whether it is used once or a million times. Now when this is called for the first cell TM1 will need to calculate consolidated_profit which will almost certainly take more than 50 steps, and so the ratio will also have taken more than 50 steps and be stored. However for the next cell, because the consolidated_profit will have been cached, it will take only a few steps and so the ratio probably won't be stored. You can muck about with CalculationThresholdForStorage but this will affect the behaviour of the entire server.

The other consideration is the difference in performance between ['consolidated_profit'] and DB( ... 'consolidated_profit' ). In the tests I have done the [] version has been about 3 times faster. So two []s and a DB will be faster than two DBs. It's just another thing to consider.

[Steve Rowe]

Interesting.

Does anyone know what a "step" is?

Is it just the number of populated N cells that are below a particular consolidation irrespective of if they are ruled or not? Does a fed cell that evaluates to zero count?

The documentation states
"Defines a minimum number of rule calculations required for a single cell or Stargate view, beyond which the TM1® server stores the calculations for use during the current server session."

Is the rule part of the above statement correct? I'm somewhat confused on what things we are counting to get to 50.

I also wonder what logic was used in order to set the default at 50, it seems fairly arbitrary and I guess playing around with this amount could be powerful tool to tune performance. Has anyone had any experiences they wish to share?

Cheers,

[garry cook]

I've played about with this config quite a bit recently on a beast of a model and the difference is very noticable in terms of both speed and RAM usage.

Although every model will obviously be different, I found on this service that moving the flag down from 50 to 20 had the effect of drastically speeding up the model at all points (low level / top level views) but that the RAM usage more than quadrupled on average (yes, 4 x RAM usage). On the server I was on, this was fine because IBM had told them to buy a server that I suspect had liquid nitrogen cooling somewhere in it. Was 48 core (not 24 hyperthreaded, actually 48) beast of a thing with RAM to burn so wasn't an issue. The performance however went from taking 8 minutes to run a top level view to around 3.5 minutes and the mid level views especially showed drastic improvement when the service had been going for a while.

Unfortunately I found it very difficult to predict how this was going to affect the model consistently though as for obvious reasons, the caching can be totally different depending on what users are actually doing in a live environment. On month end days, the perfomance boost became far less pronounced because the cached data kept changing as updates took place but the RAM usage didn't drop anywhere near as much. The memory in garbage was through the roof - didn't seem to be freeing up this RAM anywhere near as much as it should have done imo.

So guess it's a suck it and see but can definetly say that the flag did make a dramatic difference both positively (speed) and negatively (RAM monster) on the model I was testing it on. To be fair though, I was playing about with it because the model was so painful to use in the first place so can't say what difference it would make on a model performing ok to begin with.

[lotsaram]

Have also played with this and on most servers we have CalculationThresholdForStorage=5
Have noticed a significant speed improvement as many verws which I am guessing used to be calculated from scratch now get cached. Unlike Garry I haven't noticed any significant increase in RAM consumption or garbage memory as a result, but RAM consumption is not a limiting factor as we have plenty of unused memory capacity.

Other key parameters to look at when it comes to performance optimization are VMM and VMT (which are actually set at cube level in cube properties and not in the server config).

But regardless of any configuration tweaking in any rule calculation intensive model the cube design, data volume, degree of overfeeding and efficiency of the rule calculations themselves is really what is going to determine performance. Add multiple users and concurrency and I would add in managing locking into the mix as far as performance experienced by the user goes as being a critical factor.

[Steve Rowe]

Good to hear that this can have a dramatic change in performance.

I'd still like to know what we are counting when we say "50 steps", it is a very ambiguous term and kind of a key piece of information for this functionality.

Anyone want to offer an opinion?

Cheers,

[ioscat]

so i got a question about how does it works?

suppose we have cube dependence:
a->b->c->d, a->b->e
if we open view of cube 'd' - what will be saved to cache? digits from view of cube 'd'? or all leaf cell of 'd' will be stored? or all half way calculations will be stored too? will i get performance bonus when opening view of cube 'e'?

i'm playing with this parameter now, but cannot find behavior mechanism...

[ioscat]

One more question
Do you use MaximumCubeLoadThreads? How to allow multi threading while working in Architect? Is it possible?
I first time used MaximumCubeLoadThreads parameter and time of loading decreased extremely - maybe 10-30 times. But starting size of model was 3GB instead of 2.

[ioscat]

Does AllRuleCalcStargateOptimization have any effect? I can't imagine situation that in cube all consolidated values are rule calculated. We will always have a typical consolidation - by time or by some other dimension.

[ioscat]

Hello!
While reading article at tm1tutorials (http://www.tm1tutorials.com/healthcheck.p3.php) i found statement about MaximumCubeLoadThreads

This will load cubes in parallel by utilizing the different CPU Cores. *Its recommended to leave this setting at its default value of 0 - Zero when your server utilises conditional feeders or has 80% of the cubes linked via Rules.

What is the reason of the 80%?

[lotsaram]

80% is an arbitrary number pulled out of someone's you know what.

The point is that if you have a model with rules that uses conditional feeders or many to one or one to many feeders then loading on multiple cores may not be faster than single core loading and could produce an excessive amount of garbage memory.

The precise "tipping point" though of how many cores is best to load with is entirely model and data dependent and can only be determined by trial and error experimentation. You can't put a simple number on it.

[ioscat]

Upping topic again.

What is better?

Code: Select all

=N:IF(<...>,CONTINUE,0);

or

Code: Select all

=N:IF(<...>,CONTINUE,STET);

?

[Duncan P]

They are different. The one that is better is the one that gives the effect that you need. The first forces the value to 0 and prevents data entry, the second allows data entry if the condition is not met. Which effect do you want?

[ioscat]

well i mean in the second case we can lock the same cells by cellsecurity and let them stay zeroed. Will it give any significant positive influence on performance?

[qml]

we can lock the same cells by cellsecurity and let them stay zeroed. Will it give any significant positive influence on performance?

No, it will have a negative influence on performance.

[ioscat]

found that

Code: Select all

numbr(!year)

is significantly faster than

Code: Select all

attrn('year', !year, 'Numeric')

and making alias is better than text attribute. about 30% for each one.

[tomok]

found that

Code: Select all

numbr(!year)

is significantly faster than

Code: Select all

attrn('year', !year, 'Numeric')

That one is kind of a no-brainer. In the first example you are simply executing a math function against information inside the cube structure. In the second you are asking TM1 to query the attributes cube. Any time you have to go outside the cube to resolve a rule it will be slower.