What can cause variability in recalculation times?

Post Reply
jwafro
Posts: 22
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

What can cause variability in recalculation times?

Post by jwafro » Thu Mar 29, 2018 2:15 pm

Hi All

I have been challenged to improve the performance of our budgeting model.
It is very slow ~ over 15 minutes for a system recalc on average.
I have also been required to set challenging SLA's for system performance, which I will be held to and have to report against.

As such I have been working to bring down the model recalculation time.

I have built a testing process around viewconstruct to enable me to record the time the main calculation cube takes, and test/confirm the impact of changes.

I have found that despite the source data remaining the same, and there being no other demands on the server, the model can vary substantially in the time that it takes to calculate this view.

So, for example the quickest recalc would be 8 min, the longest 28 min (not an outlier), and the average around 15 min. This forces me to run the test at a volume, eg 30 times to get a justifiable data set of what the performance range is.

I watch the taskmanager during this process and can see that sometimes, (the quick refreshes) it uses 100 CPU utilisation, and yet other times, it will fluctuate wildly between 100% and 10%.

I am coming up with a testing regime, What could be the cause of this variability in calculation time?

I read somewhere that TM1 decides on the fly the path its going to take to calculate an output, maybe there is someway to help direct it? eg with more specific feeders?

Duncan P
MVP
Posts: 587
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: What can cause variability in recalculation times?

Post by Duncan P » Sat Apr 07, 2018 7:00 pm

Are you making sure that all caches are completely clear before you start the test? This is normally done by changing a value in the cube (or an upstream cube) from the one the view is running on. This is unless they have finally shipped selective cache invalidation. I kind of imagine there would have been some talk about it if they had though.

jwafro
Posts: 22
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: What can cause variability in recalculation times?

Post by jwafro » Tue Jun 12, 2018 10:45 am

Hi Duncan

Yes, I write a cell in the cube to clear it prior to each refresh of the calculation. (and then delete that value)

I didn't determine what happened to the cube, however though rebuilding the model's design and removing a dimension, Ive brought the recalc speeds down to around 2 min, so happy day. The variability is still there, but the range is much more acceptable. (30 seconds vs 20 minutes)

Post Reply