TM1 Performance Issue

[Mark RMBC]

Hi All and happy New Year,

I am wondering if anyone has come across an issue of a similar nature to the one I am about to describe and if so do you have any tips for resolving the issue, or working around it.

I will not be going into any great detail so if I have missed any pertinent information apologies up front!

I have a cube that is designed to calculate the budgets for each employee and job combination in the organisation. The cube has the following dimensions:
Employee
Job Number
Cost Centre
Detail Code
Year
Versions
Staff Budget Setting Measures

There are a number of Staff Budget Setting Measures, for example Appointed Hours, FTE (which is employee full time equivalent), Basic Pay, National Insurance etc etc.

The issue is as follows, if on the web I open up a Cost Centre which includes an employee and job combination whose Appointed Hours are zero it takes a long time to calculate, however where a Cost Centre does not include any employee and job combinations whose Appointed Hours are zero the recalculation is almost instant. Furthermore if where a Cost Centre includes any employee and job combinations whose Appointed Hours are zero and I change this to 0.001 then the recalculation speeds up again, the problem is resolved.

From this I determined it must be the feeders that are determining this behaviour and by including Appointed Hours in the view I am not effectively excluding Zero rows.

Appointed Hours is used to feed other measures in the cube as follows:
['Appointed Hours']=>['F.T.E'], ['Salary Increment'], ['Living Wage'];
['Appointed Hours']=>DB('Staffing-Budget-Setting',!Employee,!Job Number,!Cost Centre,!Detail Code,attrs('Year',!year,'Next Year'),!Versions,'Appointed Hours');

The point here is that I am happy with how the feeders are working, to me they make sense but I am also happy that rows where Appointed Hours are zero need to be shown on the web view.

I was thinking I could run a TI process from a view that put in the value of 0.001 where appointed hours was zero and then call another process which put the value back to zero again.

Anyone have any ideas how to deal with this situation?

Regards, Mark

[gtonkin]

Hi Mark, what would help is the version of TM1 e.g. 10.2.2 FP4 for example - do not want to make an assumption that it is 10.1.1 per your profile.
Are you using UNDEFVALS at all?

[Mark RMBC]

Hi gtonkin,

thanks for the reply

We are using 10.1.1.

I am not using UNDEFVALS and don't really know too much about it, other than what I have just read!

I think I understand the concept but my mind is trying to imagine the overall impact on the model and if adding this will require a retest of everything, from processes to web views etc etc etc!

But you think it would help?

Additional - Having looked up UNDEFVALS some commenters have said why would you ever use this, but from my understanding of what it does I am thinking, why would you ever not use it! Maybe I am missing something?

cheers, Mark

[gtonkin]

Hi Mark, what rules do you have that are dependent on Appointment Hours? Wondering if there is a divide by zero issue. If you are dividing are you using a backslash rather than forward?

[tomok]

What is the significance of the "if on the web" statement? Are you saying there is not an issue in Perspectives? Is this a cube view or an Active Form report? Do you mean recalculate or rebuild? Sounds like a zero-suppression issue. Without knowing what is in the rows and columns of the report or cube view, and how those play into the calculations I can't provide much help.

[Mark RMBC]

Hi,

gtonkin - I am using a backslash in the rules, as per below:

['F.T.E']=N:

((['Appointed Hours']\['Grade Standard Hours'])*(['Contracted Weeks']\Numbr(DB('Admin Parameters Cube','FTE - Full Contracted Weeks','Value')))*['Job Cost %']);

Now Appointed Hours feeds the F.T.E measure and the F.T.E measures feeds other measures, so if appointed hours are zero then the F.T.E I presume is not fed and therefore the other measures that are fed by F.T.E. are not fed either. As tomok indicates it is as if there is no zero suppression. There is zero suppression on the rows but because there are multiple measures in the columns it will only suppress rows where all columns are zero (obviously!).

There is one anomaly in the feeders but not sure if this is a problem or a design no no, Appointed Hours is used to feed another measure called Salary increment (which is in the same measure dimension as Appointed Hours) even though Appointed Hours is not used in the calculation for Salary increment. I did this because the actual calculation for Salary increment comes from another cube, so the feeder would have been a DB from that cube and using Appointed Hours seems logical to me!

tomok,

Apologies but the Web comment is a complete red herring, it occurs whether in perspectives or the Web and occurs when I try to recalculate the sheet. In the view I have the Employee and Jobs combinations in the rows and the measures across the columns. Now on some rows the Appointed Hours are zero but other measures have data. So it appears to be slow because the appointed hours are zero and have always been zero. But if I change those appointed hours to not be zero the performance improves markedly. If I then change the appointed hours back to zero the performance is still ok. It only appears to be an issue if the appointed hours have always been zero.

I will attach an excel file to show you an example view

regards, Mark

[Mark RMBC]

Example View TM1 SBS.xlsx

(15.8 KiB) Downloaded 300 times

[whitej_d]

I'm pretty sure it's not feeder related. If it was, you would more likely see the opposite behaviour, in that including a non-zero number in the view would be slower than a view containing only zeros, as having a number will almost always result in more feeders, therefore more calculations and therefore a longer calculation time. Feeders are created when a cell goes from zero to non zero, so in your case entering a small value in 'appointed hours' could only result in a greater than or equal number of feeders. I can't see a situation where entering a number would cause a speed up relating to feeders.

It seems to me like there are only 2 possible reasons. Either a bug in the software relating to division by zero, in which case a PMR to IBM or an upgrade would be the way to go, or there could be some conditional logic in the rule which changes the logic if the appointed hours are zero. Do you have any 'if' statements in the rules which are directly or indirectly dependent on 'Appointed Hours', in this cube or others?

[Mark RMBC]

Thanks for the reply whitej_d.

I am pretty certain division by zero can be ruled out.

On the rule logic nothing is jumping out at me at the moment. When time allows I will do some testing to see if I can narrow down the issue and then provide more pertinent detail. I was hoping the little detail I have provided would have prompted someone to go, ahh!, the problem is this and it quite common! But I guess in TM1 world it is never that simple!

I do I think have a workaround in place, not fully tested though, where I can create a TI to put 0.001 against any value that has 0 in appointed hours and then put the value back to 0 again! I should add I am not literally adding zero to every possible combination, just those where I know data has been loaded into other measures against dimension combinations! The issue I had when I first tested this process is that where appointed hours were zero it would not show in the view I was running the TI process against, but I solved this by using in the view another number measure entirely (contracted weeks) and then doing a cellget for appointed hours and adding this to the contracted weeks and then doing a cellput 0.001 if the total of contracted weeks + appointed hour = contracted weeks.

additional - i should re-emphasise that if appointed hours are zero and then changed to a value other than zero and then changed back to zero the problem disappears, which leads me to believe that it isn't the zero that is the problem, well only if the value has always and only been zero.

regards, mark

[mattgoff]

This sure sounds like a bug. Normally I would say you should open a ticket w/ support, but they first thing they are going to tell you is to upgrade to the latest version. 10.1.x is officially supported for a few more month, so you could still give it a shot if you're unable to upgrade in a timely fashion. Or you could continue with your workaround until you are too.

Matt

[Steve Rowe]

Just to round this off I think it would be v useful to see the whole rule sheet as is rather than little bits of it.

[Mark RMBC]

Appreciate all the responses.

I have attached the rules file. I wanted to avoid this as I didn't want the humiliation of the experts pouring over all my bad habits, but I guess to move forward some humiliation is inevitable!

matt - we are looking to upgrade shortly. My only concern is that our models are heavily reliant on web sheets and I am anticipating lots of problems dealing with any new bugs!

regards, Mark

Staffing Budget Setting Cube Rules Forum.txt

(25.41 KiB) Downloaded 300 times