Recalculating in Excel changes the way rules work

[jstrygner]

Hello after some break since my last posting here.

Today I faced some weird behaviour of my 9.5.1 HF16 and HF17 (till now tested only on these) and for now I am powerless .

Maybe somebody faced something like this before.

Generally speaking:
1. I have a cell in Cube Viewer which at beginning returns value 1, which is correct. It is a consolidation calculated by rules. I can recalculate my view many times, it is 1!
2. I am opening a report in Excel that among others presents also this cell. First recalculation of my report returns also 1.
3. Second recalculation though (after no additional action) changes the value to 2!
4. When I come back to my Cube Viewer and recalculate I also see 2 (and this is not correct anymore to be precise ).
5. When I go to rules editor and "resave" the rules (add a space or delete and put back a semicolon, just to make TM1 do the "resave") I can come back to my Cube Viewer and after recalculation my value goes back to 1. It also goes back to 1 in Excel, but... only after first recalculation. Second recalculation leaves me back with 2 both in CV and Excel .

I am working to do some more findings, but hope somebody here will be able to say: "Oh yes, we had that and the reason was...".

My comments:
1. I can understand I did some mistakes while writing my rules, but what I cannot understand is why TM1 returns different values (after saving rule and after second recalculation in Excel).
2. I thought this could have something to do with the direction of consolidating children, but the cell I am talking about has its rule that divides two other values from other cells and those do not change. My calculated cell should not be calculated as consolidation at any time.
3. It looks like my second recalculation in Excel turns rules calculation off for this cell and applies hierarchy summing (in this case value 2 would be expected), but how would that be possible?
4. Using Rules Tracer in both cases I have the same source data in the "Trace" section, the same cells shown in "Tracing Calculation:" section and the same rules displayed on the gray field between these two sections. The only difference is that in the first case I have the "Calculated: 1.0000" and in the second case I have the "Calculated: 2.0000" result.

Additional info:
1. Server and client - Windows XP (English), Office 2003 (English), TM1 9.5.1 (HF16 and HF17) (English).
2. Excel calculation - Manual
3. No DBS formulas in Excel file, no macros.
4. Excel file I am talking about is the only one open on my computer while doing these test.

I will appreciate your help. If I have some more findings I'll add them.

Regards.

[jrizk]

Hi. Is there rebuilding of any dimensions in the cube happening before you recalculate - I've experienced this when a dimension was updated etc. - and values being stored in memory. Try running a macro with the line

Application.Run("M_CLEAR")

and see if it helps. Could be something else but worth trying.

[Steve Rowe]

If this isn't a bug then it sounds like your rules are written in such a way that the engine has two possible routes when calculating. One route delivers the 1 the other delivers the 2.

If the elements you are using one step down are also consolidations and calculated then TM1 may be going back to the Ns below this to deliver the results. You might want to look at consolidate children in the rules, you can this to force TM1 to calculate C levels on the hierarchy.

TM1 calculates C levels direct from the N levels below it irrespective of how the dimension is actual built.

Also consider any feeder issues with the elements in your calculation, though this shouldn't cause the issue you are seeing.

I might be worth posting your rules or a screen shot of the cube viewer with the detail of the calc shown.

Good luck!

Cheers
Steve

[jstrygner]

Hi again, thanks for reply

First some responses to your hints:
@ jrizk:
* No, no rebuilding, no nothing just "recalculate" and after that just another "recalculate". It is a model I am developing on my laptop, so I am definitely the only user that does anything in there for sure.
@ Steve Rowe:
* I am not a TM1 guru, but also not a rookie and all I can think of right now is a bug (see findings below).
* I think two different routes of calculation are possible only when my cell would be calculated as a consolidation, not a rule that divides one thing by another (or some components below would use in some extent such consolidation calculations). As I wrote in my post before (My Comments, point 2) those components below in both cases have the same values (visible in Cube Viewer, Excel report and Rules Tracer), so the division result should be the same.
* And here is the part of my rule that does this particular calculation (cube Delivery_Cycle with dimensions: System, Version, Region, Curriculum, Currency, Business_Line, Year, Quarter, Measure):

Code: Select all

# % Spend
## Leaves
[System:'System', Version:'Bud Q IOT - Current', Currency:'USD', Measure:'% Spend']=N:If(
	SubSt(!Region, 1, 3)@='IMT',
	DB('Delivery_Cycle', !System, !Version, SubSt(ElPar('Region', !Region, 1), 1, 5), !Curriculum, !Currency, !Business_Line, !Year, !Quarter, !Measure),
	Continue);
	C:Continue;
## Consolidations
[Version:'Bud Q IOT - Current', Currency:'USD', Measure:'% Spend']=C:[Measure:'Curr. Spend']\[Curriculum:'Curriculum NA', Measure:'Curr. Spend'];

All other rules above this one concern different Version or different Measure element, so this is the rule that works on my C level (Rules Tracer confirms it).

Additional information I forgot to write about last time:
1. I did full restart of my computer.

And now what I know more:
Not getting into details I discovered, that to stop recalculation causing returning 2 instead of 1 I had to delete one DBRW formula that was 40 rows below my "interesting case" cell. The below DBRW was pointing to a Version element that was a parent of my "interesting case" cell Version. All other elements in both DBRWs were the same. It was enough for me to get rid of this below DBRW (I used Excel formulas to calculate what I need).
So I have a workaround, but still have no idea, why my TM1 behaved in a way it did.
I know sometimes Excel returns weird values, when some circular reference is present in the file, but I never faced it in TM1. While having undesired 2 I run parallel TM1 Architect and it was also showing value 2, so it wasn't just wrong value in my Perspectives, it was wrong value on the TM1 server level.

Once again thanks for responses, I hope a time will come, when I'll finally know the reason of this.

Regards.

[Steve Rowe]

[Version:'Bud Q IOT - Current', Currency:'USD', Measure:'% Spend']=C:[Measure:'Curr. Spend']\[Curriculum:'Curriculum NA', Measure:'Curr. Spend'];

Are the dependencies of this rule on the calculation line both N level? If not it could be that one of the Cs that is below this has the "two routes" problem.

I'd also try getting rid of the C: Continue and putting AllowSeparateNandC in the cfg.

Cheers

[jstrygner]

Are the dependencies of this rule on the calculation line both N level? If not it could be that one of the Cs that is below this has the "two routes" problem.

Steve, I am not sure if I understand your question, but I think you ask, if the cell that is addressed in my case with [Measure:'Curr. Spend'] and the cell that is addressed with [Curriculum:'Curriculum NA', Measure:'Curr. Spend'] are N level.
No, these are both C level, because my problem appears on an element 'All Business Lines' which is a consolidation in Business_Line dimension.

I (think I) understand your suggestion that although my two cells below do not change their value, calculation doesn't look at them (although shows them in rules tracer), but it goes lower to Ns.

The more I think about your hints, the more possible they seem to me to be the reason.
I know where to look now.

As soon as I have something, I'll let know.

Cheers

[Steve Rowe]

Hi, Yes that's what I was trying to say...I get to your posts before I have my coffee in the morning as I eat breakfast so I may not make the best sense then, it's difficult enough to explain with brain fully engaged...
Cheers