9.5.2/Consolidations/Rules/Intermittant Zero Values

Post bug reports and the status of reported bugs
Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

9.5.2/Consolidations/Rules/Intermittant Zero Values

Post by Steve Vincent »

A colleague has found a bug in his models that seems to relate to the way the cube viewer calculates data. On my completely different models I am also able to replicate it. It relates to overwriting a consolidated value with a value from one of its children, and value returned not being consistent depending upon the cube view.

In a basic example, a small cube requires the consolidated value for “2011” to be overwritten with the value from “Dec – 2011”. The dimension is laid out as below (all weighted 1);

Code: Select all

2011
Jan - 2011
Feb - 2011
Mar - 2011
Apr - 2011
May - 2011
Jun - 2011
Jul - 2011
Aug - 2011
Sep - 2011
Oct - 2011
Nov - 2011
Dec – 2011
The rules are (skipcheck is on);

Code: Select all

['2011', 'Budgeted Util'] = C: DB('Utilisation',!Nomination,!Resource Source,'Dec - 2011',!Organisation Breakdown,'Budgeted Util');
If values are equally spread across each quarter in a 2 2 4 pattern, the consolidated value with no rule will show 32 (4x4 + 8x2).

With the rule turned on it should read 4, but in some cases (summarized below) returns zero.

1. Just 2011 is selected and shown either on the X or Y axis of the view – 0.
2. After 1, the consolidation is expanded to show the months – 4.
3. After 2, the consolidation is contracted back to just 2011 – 0.
4. If 2011 and any other month/year element is picked (as long as the rule does not relate to it & even if there are no values held in it) – 4.
5. If 2011 is moved to the “header” of the view so only 1 element can be selected – 4.
6. If view 1 is sliced or snapped to Excel – 0.
7. If view 1 is sliced, the VIEW formula removed by paste / values then recalculated – 4.
8. If view 1 is used in a TI to export the data – 4.

Subtle changes to the rule help to illustrate it further;

Take the value from outside of the consolidated element’s hierarchy;

Code: Select all

['2011', 'Budgeted Util'] = C: DB('Utilisation',!Nomination,!Resource Source,'Dec - 2012',!Organisation Breakdown,'Budgeted Util');
This returns the correct value of 4.

Take the value from a different element within the consolidated element’s hierarchy;

Code: Select all

['2011', 'Budgeted Util'] = C: DB('Utilisation',!Nomination,!Resource Source,'Nov - 2011',!Organisation Breakdown,'Budgeted Util');
This returns the incorrect value of 0.

Add 2 rules to do 2011 & 2012 in the same way

Code: Select all

['2011', 'Budgeted Util'] = C: DB('Utilisation',!Nomination,!Resource Source,'Dec - 2011',!Organisation Breakdown,'Budgeted Util');
['2012', 'Budgeted Util'] = C: DB('Utilisation',!Nomination,!Resource Source,'Dec - 2012',!Organisation Breakdown,'Budgeted Util');
Selecting both 2011 & 2012 in the view will show the incorrect value of 0.

I am using the 64bit 9.5.2 release with FP1 applied to the server (Win 2008), with the client running on XP 32bit with an original, un-patched 9.5.2.

I have also tested (with the same client) on a 32 bit, original 9.5.2 running on Win 2003 and did NOT have the same issues.

My colleague is unable to test it today but does have access to a 64bit Win2008 server running an original 9.5.2. We are hoping a test on that machine will provide the correct numbers in the test models which will then prove a change made in FP1 has messed up part of the calculations done by TM1. If that also fails then it maybe an issue limited to just the 64bit version of 9.5.2…
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Post Reply