Calculation weirdness

[Steve Rowe]

I love the little treats that TM1 serves up on a Friday afternoon, it's almost as if it can tell my brain is beginning to shut down to protect itself from the weekends excesses.Especially impressive since this server is in another country....

Anyway..
A user reported that a calculated number keeps changing without them doing anything. Impossible I say! It seems it really is though.
The value is the result of a C level calculation on some KPI values, used to determine if a KPI should be summed or averaged on consolidated periods.
The rule is as shown below, the bit that applies is the section bracketed by #XXXXXXXXXXXXXXXXXXXXXXXXXX

Code: Select all

['Input']=C:
#When we are on C level for month then for some KPIS we need to take the value for the last period
#rather than allow the system to accumulate.
#If we are on the C level for periods and 0 level for all other dims ( some dims commented since they contain only 0 levels)		
If ( Ellev('Periods', !Periods) >0 & 
#      Ellev('Book Indicateur', !Book Indicateur) =0 & 
#      Ellev('Cycle', !Cycle) =0 & 
      Ellev('Entity', !Entity) =0 ,
#      Ellev('Indicateur - Mois', !Indicateur - Mois) =0 ,
#Calc where we allow the standard consolidation to apply
   If (Attrs( 'Indicateur - Mois',!Indicateur - Mois, 'Month Sum Method')@='Standard',
#Then ignore this rule
        stet,
#Month End, there are no more of these but I left the code in
   If (Attrs( 'Indicateur - Mois',!Indicateur - Mois, 'Month Sum Method')@='Month End',
#Month end calculation, refer back to the last period
   DB('Indicateur - Mois',!Book Indicateur,!Cycle,!Entity,
#Substitute in period reference
 DB('Periods Properties',!Periods,'Last Period'),
   !Indicateur - Mois),
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
#Average
   If (Attrs( 'Indicateur - Mois',!Indicateur - Mois, 'Month Sum Method')@='Moyen',
#Refer to the Somme per Moyen book to get the total
   DB('Indicateur - Mois','Somme pour Moyen',!Cycle,!Entity,!Periods,!Indicateur - Mois) \
#divide by the number of periods
   ['Period Populated'],
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
#Don't calculate the Summary, don't test for this since it shouldbe everythign else
    0))),

All fairly straight forward stuff.
When the rule is first saved I get the correct result.

pic1.GIF (23.94 KiB) Viewed 4865 times

with the associated rule trace.

pic2.GIF (20.2 KiB) Viewed 4865 times

However after repeated presses of the calculate button I get the following result. You will see that the result for Estime 1 has changed. Just to stress the only thing that I have done to make this change is repeated calcs of the cube view.

pic3.gif (23.95 KiB) Viewed 4865 times

with the associated rule trace.

pic4.gif (20.63 KiB) Viewed 4865 times

It's pretty repeatable (no server restart as yet), after a rule save the calculation works.
Following repeated recalcs the value changes.
So far it has only affected values in Estime 1, there are no rules that reference Estime 1 directly.
Once it goes wrong it goes wrong for all KPIs with the Moyen property but only for Estime 1.

I don't think I've seen this before, anyone else have anythoughts on what might be causing this?
I'm expecting/hoping it to be something that clears when I restart the server. I'd like to have some kind of logical explanation for this no matter how warped, I don't think I've ever seen the rule logic just break like this....

Edit :
Further check was to change
DB('Indicateur - Mois','Somme pour Moyen',!Cycle,!Entity,!Periods,!Indicateur - Mois) \
#divide by the number of periods
['Period Populated'],
to
DB('Indicateur - Mois','Somme pour Moyen',!Cycle,!Entity,!Periods,!Indicateur - Mois) /
#divide by the number of periods
['Period Populated'],

The soft fail (\) for the DIV zero issue would return 316 if populated periods was somehow returning zero so if I change the code to give a hard fail Div0 error (/) I should see 26 and then Div0 when the behaviour changes. I still get 26/316 so this change had no impact.
/EndEdit
All on 9.0SP3U9, keeping the old school alive!

[TM1Dunk]

Steve

As the rule trace seems to reference the correct 2 values on each occasion i.e.:

1) Somme pour Moyen = 315.5
2) Period Populated = 12

It seems to be simply the "divide by" operation which is going askew.

Looking at your rule code, a suggestion I may offer is to fully qualify the ['Period populated'] reference to DB('Indicateur - Mois' , !Entity , !Periods , !Indicator - Mois , !Cycle , 'Period Populated') (dimensions probably in the wrong order here, but I'm sure you get the gist!).

Duncan

[Steve Rowe]

Thanks Duncan,
That didn't work unfortunately and also the behaviour survives a restart.

The only other thing I can see that looks weird is that the rule tracer appears to check another rule even after it has evaluated the result. This is indicated by the "['Input']=C: If..." after the 216 and 25 lines. Normally the rule engine wouldn't do any more work after it's evaluated a rule

Cheers

[Steve Rowe]

Well I still have not solved this but I can now reproduce very specifically what is going on. I can't explain or fix it but that's for later

By a process of elimination I established that the problem only occurs when I f9'ed (rather than shift+f9) a specific spreadsheet. Once I f9'ed the workbook the rules moved to their broken state.

My first thought was that there was DBS in the but there is not and the transaction log confirms this.

Anyway again by a process of elimination I narrowed the issue down to one specific spreadsheet. The different thing about this spreadsheet was that it also retrieves the result for a C level entity. The rules are set up such that at the intersection of a C level entity and C level period I want to consolidate the values underneath, if I am on a N level entity and a C level period I want to perform the average calculation.

If I have a cube view the same layout as the spreadsheet I don't get the same broken behaviour, the rules perform as written and survive multiple calcs.
If I slice the cube view the rules perform as written and survive multiple calcs.
If I range value the view function in the slice I created in the previous step then the rules break on the first calc.

So if I request values from an excel spreadsheet for a C level entity using DBRWs via a View function then I get the correct results.
If I request the values from an excel spreadsheet for a C level entity using DBRWs but don't use the View function then I get the incorrect results and I break the rules for that bit of the cube.

Very very odd, the engine obviously doesn't like what I am doing with the C levels but this a real strange way of showing it!

Cheers,

[John Hammond]

Steve did not read your post in depth but I have seen this before and could your problem be some manifestation of this.

http://publib.boulder.ibm.com/infocente ... es_N7.html

You have no control over the order in which TM1 performs dimension consolidations. Furthermore, depending on which consolidation path is optimal at any given moment, TM1 may alternate between paths. Consequently, you may request the Total, 1 Quarter value twice in the same TM1 session and get different results.

Apologies if I am off the mark.

[Steve Rowe]

Hi John,
Yeah it's kind of in this area but not really too!
I do have a C level rule that is overriding a consolidation that is a member of another consolidation, that I can normally cope with or avoid. The weird thing in this case was that (using the example in the link you provide) that when I calculate the 1318 the totals in 1 quarter change from their rule driven values to their natural consolidated values. To make it even weirder, this behaviour only happens when I reference the "1318" value from a DBRW without going via a view function. Maybe the view function / cube viewer in some way controlled the "direction" of the calculation and that's why I got the good behaviour with that in place

Anyway I think I might have solved the issue by simplifying the C level rules that were in place.
Cheers,

[Steve Vincent]

in case you may have overlooked it, what (if any) settings are in the server cfg file that refer to the behaviour of calculated cells or views? do you have any of the following;

AllowSeparateNandCRules = T
DisableWorksheetView=T
ReEvaluateConditionalFeeders=T

Might be an indicator to the issue or help understand it.