TM1 Forum

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 All fairly straight forward stuff.
When the rule is first saved I get the correct result.
with the associated rule trace. 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. with the associated rule trace. 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!

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

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

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,

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.

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,

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.