rolling average at day level

Post Reply
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

rolling average at day level

Post by macsir »

Hi, there is a request for doing forecast rolling average based on actual. Usually it is not a problem but here we are doing at day level for one year. e.g. to forecast 1st Feb 2017, we have to average all back to 1st Feb 2016, which means it needs jump back to 365 times for a cell. Algorithm with conditional feeders is not an issue but only impacting performance as it involves tons of calculations. Open one view will take about 1-2 minutes. TI is not acceptable as users don't want to run process each time to reflect each change.

The only way I can think about is to change faster CPU as I can see CPU is hitting the max when opening such views. Is there other way to improve the performance? Any magic parameter in config file? Please advice.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: rolling average at day level

Post by Steve Rowe »

Suggest you add complexity to your period dimension and do the averaging in there on a weighted consolidation. This will be much faster than rules only.
Note that averaging in consolidations fails if zero values are a problem (i.e. if you have two days D1=10, D2=0. What is the average? If it is 5 you can use consolidations, if its 10 you're back to square 1).

Once you have the average calculated on the consolidation you can then rule it to where it needs to be.

Other thoughts in rough order of possible success.
Look at your maths, if you have AV="365 day average for Day 0 (D0)" then the average for D1= AV +(1/365 * D1) - (1/365 * D-366). This should be more efficient logic, calculate 1 day longhand and derive the rest. Your still going to be battling with the rule cache when data changes though.
Look hard at the range of data that changes, maybe you can pre-calc some of the average using TI.
Look hard at how the data changes, is there a trigger which you can link a TI too. i.e. Actual data loads. Their objection is not to a TI, it is to having to run it.
MTQ settings, may not be massive benefit with rule driven calc but there will be some.
More cores to potentially improve MTQ, might be easier to test if you are on a VM (watch out for licence implications).
Faster CPUs, again watch out for licence implications on the PVU

HTH
Technical Director
www.infocat.co.uk
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: rolling average at day level

Post by macsir »

Thanks Steve for so many thoughts. I will think about them tomorrow.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
mvaspal
Community Contributor
Posts: 341
Joined: Wed Nov 03, 2010 9:16 pm
OLAP Product: tm1
Version: 10 2 2 - 2.0.5
Excel Version: From 2007 to 2013
Location: Earth

Re: rolling average at day level

Post by mvaspal »

MTQ settings, may not be massive benefit with rule driven calc but there will be some.
Hi Steve,

I think I saw cases when the MTQ impact was quite impressive on rule calculations too, for example if there were 4 cores, the calc was 3-4x faster using MTQ. Do you have a different experience with MTQ?
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: rolling average at day level

Post by Steve Rowe »

I think it depends on the complexity of the rules as they query can't be multi-threaded in the same way as pure data.

I've not tested extensively though, it was what I have been told and experienced a couple of times, hence the cautionary comment on expecting MTQ to make a step change in performance in a heavily ruled environment.
Technical Director
www.infocat.co.uk
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: rolling average at day level

Post by macsir »

Hi Steve

I tested the following cal in some cube. It does have better performance as jumping less times and same result. Thanks for that. But in some places I can't use it as user wants to overwrite some calculated Avg in some days. Anyway, it helps. And also I limited some view sizes to open faster.

Look at your maths, if you have AV="365 day average for Day 0 (D0)" then the average for D1= AV +(1/365 * D1) - (1/365 * D-366). This should be more efficient logic, calculate 1 day longhand and derive the rest. Your still going to be battling with the rule cache when data changes though.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: rolling average at day level

Post by macsir »

Steve Rowe wrote:I think it depends on the complexity of the rules as they query can't be multi-threaded in the same way as pure data.

I've not tested extensively though, it was what I have been told and experienced a couple of times, hence the cautionary comment on expecting MTQ to make a step change in performance in a heavily ruled environment.
Well, now, I have performance issue now. I built the model using weighted consolidation to get average as much as possible. The model is forecasting next 3 or 5 years at day level. Use has capability to switch to rule-driven or manual override in all forecast average calculation. This is heavily rule-base model. A final view takes around 10 minutes to build which user complains a lot. The server is VM with 4 cores with TM1 10.2.2. MTQ is set to -1 which means use all cores.

Is there any way to improve the speed of building views apart from using faster CPU or increase core numbers?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: rolling average at day level

Post by qml »

macsir wrote:Is there any way to improve the speed of building views apart from using faster CPU or increase core numbers?
Impossible is nothing. There are probably still rule calculations there that you can optimise, utilising weighted consolidations instead etc. You can also look into optimising dimension order, rule syntax and ordering, feeders.

Consider an aggregate cube if there are details in your current cube that are not necessary. I would also check if MTQ is actually being utilised to its fullest potenitial - are all cores used when a report runs, do you have HyperThreading on etc?

And, lastly, you can play with a few MTQ parameters to further optimise multithreading:

MTQ.OperationProgressCheckSkipLoopSize=1
MTQ.CTreeRedundancyReducer=TRUE
MTQ.CTreeWorkUnitMerge=TRUE
MTQ.MultithreadStargateCreationUsesMerge=TRUE
MTQ.SingleCellConsolidation=FALSE
Kamil Arendt
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: rolling average at day level

Post by macsir »

Hi qml
Thanks for that. Yes, weight consolidation is used already, dim order is optimised. MTQ=-1 which uses all cores. This is VM, so no HT at the moment. I can see Cpu is at 100% when building large views with 4 cores. I will pay with those further MTQ parameters.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Post Reply