DBRW vs DBR in Perpectives/Excel give different answers

[gtonkin]

First off, apologies for the table and possibly confusing layout.

The story is as follows:
We produce some reconciliations from TM1 and SAP and need hash totals to balance. Users picked up and issue when doing checks that balances were out under certain circumstances.
1) If you F2 then enter again, values may change e.g. in my Total accounts row, the value changes the last four decimals from 4000 to 8000. Recalculate changes things back to balance to the cube again.
2) DBRWs seem to give the value as represented in the Cube viewer whereas DBRs do not.

Has anyone seen this behaviour before and know what it is? This seems consistent acros 9.4, 9.5.2 FP3 and on 10.2.2
Formulae are straight DBRW and DBR reading from C levels, no nested DBRs or Attributes.
With Automatic calculation on, F2 and enter makes all DBRW results same as DBR and thus incorrect.

Let me know your thoughts if you have any insight - thank you.
[/b]

Desc	DBRW	DBR	DBR-DBRW
Total Accounts	-142,864,306.499994000	-142,864,306.499998000	-.000004381[/b]
Total Assets	-379,945,370.000000000	-379,945,370.000000000	.000000000[/b]
Asset 1	-73,733,727.150000000	-73,733,727.150000000	.000000000
Asset 2	.000000060	.000000000	-.000000060
Asset 3	-332,426,400.150000000	-332,426,400.150000000	.000000000
Asset 4	-60,721,093.750000000	-60,721,093.750000000	.000000000
Asset 5	-1,078,121.200000000	-1,078,121.200000000	.000000000
Asset 6	376.000000000	376.000000000	.000000000
Asset 7	-46,964.300000000	-46,964.300000000	.000000000
Asset 8	88,060,560.550000000	88,060,560.550000000	.000000000
Total Liabilities	237,081,063.500006000	237,081,063.500000000	-.000005782[/b]
Liability 1	-504,000,000.000000000	-504,000,000.000000000	.000000000
Liability 2	-446,841,872.600000000	-446,841,872.600000000	.000000000
Liability 3	-14,065.800000000	-14,065.800000000	.000000000
Liability 4	11,194,801.200000000	11,194,801.200000000	.000000000
Liability 5	194,790,597.600000000	194,790,597.600000000	.000000000
Liability 6	336,741,104.800000000	336,741,104.800000000	.000000000
Liability 7	149,741,809.050000000	149,741,809.050000000	.000000000
Liability 8	38,905,526.600000000	38,905,526.600000000	.000000000
Liability 9	-15,758,118.500000000	-15,758,118.500000000	.000000000
Liability 10	-88,040,031.750000000	-88,040,031.750000000	.000000000
Liability 11	170,600,236.700000000	170,600,236.700000000	.000000000
Liability 12	-43,775,422.700000000	-43,775,422.700000000	.000000000
Liability 13	561,890.850000000	561,890.850000000	.000000000
Liability 14	.000005811	.000000954	-.000004858
Liability 15	311,661,000.000000000	311,661,000.000000000	.000000000
Liability 16	35,697,236.500000000	35,697,236.500000000	.000000000
Liability 17	85,616,371.550000000	85,616,371.550000000	.000000000
Excel SUM	237,081,063.500006000	237,081,063.500001000	-.000004858

[BrianL]

Looks like you are just seeing issues with floating point accuracy.

Here's the IBM technote - https://www-304.ibm.com/support/docview ... wg21515228.

[tomok]

Are you using DBRWs inside other DBRWs? For example, do you have a cell calculated via a DBRW, and then use that cell in a reference for another DBRW? This is a no-no in TM1 because the term DBRW actually means Database/Retrieve/Wide Area Network. It bundles cell requests together before sending to the server. If you have interdependent DBRWs then you will get unpredictable results. All cells that will be used in further TM1 calls should use DBR instead.

[gtonkin]

Thanks Brian-I am aware of the floating point issue and am fairly sure there will be issues in the model relating to this due to the large numbers in the model. However, I would expect the two functions to present the same results with the floating issue present in both. This is really what is irking me as they do not. I have never seen this before in systems working on smaller values and not many of our systems need to create hash totals etc. highlighting this issue.

@Tom, Thank you for your reply,

Formulae are straight DBRW and DBR reading from C levels, no nested DBRs or Attributes.

I used a straight slice, copied the contents of column B in my spreadsheet to column C, replaced DBRWs with DBRs, added difference column.

I will try and replicate issue using numbers less than 15 digits but including decimal places maybe something like 6 before, 8 after decimal.

[Michel Zijlema]

Thanks Brian-I am aware of the floating point issue and am fairly sure there will be issues in the model relating to this due to the large numbers in the model. However, I would expect the two functions to present the same results with the floating issue present in both. This is really what is irking me as they do not. I have never seen this before in systems working on smaller values and not many of our systems need to create hash totals etc. highlighting this issue.

@Tom, Thank you for your reply,

Formulae are straight DBRW and DBR reading from C levels, no nested DBRs or Attributes.

I used a straight slice, copied the contents of column B in my spreadsheet to column C, replaced DBRWs with DBRs, added difference column.

I will try and replicate issue using numbers less than 15 digits but including decimal places maybe something like 6 before, 8 after decimal.

Are the DBRW and DBR formulas in the slice referencing a VIEW formula as cube reference?
Are there rules defined on the cube, and if so, are there rules calculating on C: level?

TM1 is choosing a consolidation path for retrieving a C: level cell value. Maybe the consolidation path for the DBRW cell is differing from the consolidation path of the DBR formula, which potentially could lead to minor differences (because of machine precision and/or when C: level rules are involved).

Michel

[gtonkin]

Thanks Michel,

Both DBRW and DBR are referencing the VIEW formula as I simply copied column B to column C to keep everything the same other than DBRW vs DBR.
The Cube has NO rules at all, simply consolidations, all with a weighting of 1