Rounding variances

Rounding variances

Joined: April 13th, 2011, 5:00 pm

September 14th, 2011, 1:07 pm #1

I'm seeing rounding differences between 2 database for the "same" number. What settings should I be looking at?

DB1 is a BSO cube sitting on SERVER A
DB2 is an ASO cube sitting on SERVER B

DB1 rounds 115.5 to 115
DB2 rounds 115.5 to 116

Both servers *should* be identical (Solaris, Essbase 11.1.1.2) but I haven't requested the specs yet.

I've checked the CFG and I don't believe any of the settings are related to rounding (I could be wrong).

I ran a report script on each DB, with each script increasing the number of decimal places. The numbers are inconsistent, but they should both round up to 116.

Output of DB1:
115
115.5
115.50
115.500
115.5000
115.50000
115.500000
115.5000000
115.50000000
115.500000000
115.5000000000
115.50000000000
115.500000000000
115.5000000000001
115.50000000000007
115.500000000000071
115.5000000000001137
115.50000000000011369
115.500000000000113687
115.5000000000001421085
115.50000000000014210855
115.500000000000142108547
115.5000000000001705302566
115.50000000000017053025658
115.500000000000156319401867
115.5000000000001705302565824
115.50000000000017053025658242
115.500000000000170530256582424
115.5000000000001421085471520200
115.50000000000017053025658242404
115.500000000000213162820728030056
115.5000000000002131628207280300558
115.50000000000021316282072803005576
115.500000000000227373675443232059479
115.5000000000002557953848736360669136
115.50000000000022737367544323205947876
115.500000000000213162820728030055761337
115.5000000000002131628207280300557613373
115.50000000000018474111129762604832649231
115.500000000000213162820728030055761337280
115.5000000000002131628207280300557613372803

Output of DB2:
116
115.5
115.50
115.500
115.5000
115.50000
115.500000
115.5000000
115.50000000
115.500000000
115.5000000000
115.50000000000
115.500000000000
115.5000000000001
115.50000000000007
115.500000000000071
115.5000000000001137
115.50000000000011369
115.500000000000113687
115.5000000000001421085
115.50000000000018474111
115.500000000000213162821
115.5000000000001847411113
115.50000000000022737367544
115.500000000000213162820728
115.5000000000002131628207280
115.50000000000018474111129763
115.500000000000213162820728030
115.5000000000002131628207280301
115.50000000000021316282072803006
115.500000000000213162820728030056
115.5000000000002273736754432320595
115.50000000000021316282072803005576
115.500000000000227373675443232059479
115.5000000000002557953848736360669136
115.50000000000022737367544323205947876
115.500000000000213162820728030055761337
115.5000000000002131628207280300557613373
115.50000000000018474111129762604832649231
115.500000000000213162820728030055761337280
115.5000000000002131628207280300557613372803
Reply
Like
Share

Alan Hutchins
Alan Hutchins

September 14th, 2011, 1:11 pm #2

I dont know about ASO, but a few yeras back (well truthfully a number of years back), I had 2 BSO databases where the same number gave a similar reponse to yours. I eventually traced it to the number of dp's that the source data had in 1 cube when compared to the other cube.

In the end all I did was to use the Excel function ROUND, to make sure I got the "correct" answer.
Reply
Share

Joined: April 13th, 2011, 5:00 pm

September 14th, 2011, 1:33 pm #3

Thanks, Alan. Unfortunately, I'm extracting the data using a report script (part of a reconciliation process) and am using {DECIMAL 0} to round the number. The databases return different values so my reconciliation fails.
Reply
Like
Share

Ben White
Ben White

September 14th, 2011, 4:38 pm #4

I'm seeing rounding differences between 2 database for the "same" number. What settings should I be looking at?

DB1 is a BSO cube sitting on SERVER A
DB2 is an ASO cube sitting on SERVER B

DB1 rounds 115.5 to 115
DB2 rounds 115.5 to 116

Both servers *should* be identical (Solaris, Essbase 11.1.1.2) but I haven't requested the specs yet.

I've checked the CFG and I don't believe any of the settings are related to rounding (I could be wrong).

I ran a report script on each DB, with each script increasing the number of decimal places. The numbers are inconsistent, but they should both round up to 116.

Output of DB1:
115
115.5
115.50
115.500
115.5000
115.50000
115.500000
115.5000000
115.50000000
115.500000000
115.5000000000
115.50000000000
115.500000000000
115.5000000000001
115.50000000000007
115.500000000000071
115.5000000000001137
115.50000000000011369
115.500000000000113687
115.5000000000001421085
115.50000000000014210855
115.500000000000142108547
115.5000000000001705302566
115.50000000000017053025658
115.500000000000156319401867
115.5000000000001705302565824
115.50000000000017053025658242
115.500000000000170530256582424
115.5000000000001421085471520200
115.50000000000017053025658242404
115.500000000000213162820728030056
115.5000000000002131628207280300558
115.50000000000021316282072803005576
115.500000000000227373675443232059479
115.5000000000002557953848736360669136
115.50000000000022737367544323205947876
115.500000000000213162820728030055761337
115.5000000000002131628207280300557613373
115.50000000000018474111129762604832649231
115.500000000000213162820728030055761337280
115.5000000000002131628207280300557613372803

Output of DB2:
116
115.5
115.50
115.500
115.5000
115.50000
115.500000
115.5000000
115.50000000
115.500000000
115.5000000000
115.50000000000
115.500000000000
115.5000000000001
115.50000000000007
115.500000000000071
115.5000000000001137
115.50000000000011369
115.500000000000113687
115.5000000000001421085
115.50000000000018474111
115.500000000000213162821
115.5000000000001847411113
115.50000000000022737367544
115.500000000000213162820728
115.5000000000002131628207280
115.50000000000018474111129763
115.500000000000213162820728030
115.5000000000002131628207280301
115.50000000000021316282072803006
115.500000000000213162820728030056
115.5000000000002273736754432320595
115.50000000000021316282072803005576
115.500000000000227373675443232059479
115.5000000000002557953848736360669136
115.50000000000022737367544323205947876
115.500000000000213162820728030055761337
115.5000000000002131628207280300557613373
115.50000000000018474111129762604832649231
115.500000000000213162820728030055761337280
115.5000000000002131628207280300557613372803
So BSO is rounding 115.5 down to 115? The documentation of {DECIMAL} indicates that it's supposed to round, not truncate, so that really looks like a bug.
Reply
Share

Joined: April 13th, 2011, 5:00 pm

September 14th, 2011, 10:35 pm #5

So I can reproduce this on Sample.Basic

If I lock-and-send the value 115.5 to the intersection Jan->Sales->Florida->Actual->Product in Sample.Basic and I extract this number using a report script, I get 116.

That's what I expect.

If I lock-and-send the following values:
Jan->Sales->Florida->Actual->"100-10": 105.419
Jan->Sales->Florida->Actual->"100-20": 1.841
Jan->Sales->Florida->Actual->"100-30": 1.152
Jan->Sales->Florida->Actual->"200-10": 0.521
Jan->Sales->Florida->Actual->"200-20": 2.33
Jan->Sales->Florida->Actual->"200-30": 2.585
Jan->Sales->Florida->Actual->"200-40": 1.652

And then run a default calc, and re-run my report script, I get 115 even though those numbers add up to 115.5

Would anyone like to try?

Here's the report script:

"Jan"
"Actual"
"Product"
"Sales"
"Florida"
!
{DECIMAL 1} !
{DECIMAL 2} !
{DECIMAL 3} !
{DECIMAL 4} !
{DECIMAL 5} !
{DECIMAL 6} !
{DECIMAL 7} !
{DECIMAL 8} !
{DECIMAL 9} !
{DECIMAL 10} !
{DECIMAL 11} !
{DECIMAL 12} !
{DECIMAL 13} !
{DECIMAL 14} !
{DECIMAL 15} !
{DECIMAL 16} !
{DECIMAL 17} !
{DECIMAL 18} !
{DECIMAL 19} !
{DECIMAL 20} !
{DECIMAL 21} !
{DECIMAL 22} !
{DECIMAL 23} !
{DECIMAL 24} !
{DECIMAL 25} !
{DECIMAL 26} !
{DECIMAL 27} !
{DECIMAL 28} !
{DECIMAL 29} !
{DECIMAL 30} !
{DECIMAL 31} !
{DECIMAL 32} !
{DECIMAL 33} !
{DECIMAL 34} !
{DECIMAL 35} !
{DECIMAL 36} !
{DECIMAL 37} !
{DECIMAL 38} !
{DECIMAL 39} !
{DECIMAL 40} !
Reply
Like
Share

Joined: September 18th, 2008, 7:30 pm

September 14th, 2011, 11:16 pm #6

I'm seeing rounding differences between 2 database for the "same" number. What settings should I be looking at?

DB1 is a BSO cube sitting on SERVER A
DB2 is an ASO cube sitting on SERVER B

DB1 rounds 115.5 to 115
DB2 rounds 115.5 to 116

Both servers *should* be identical (Solaris, Essbase 11.1.1.2) but I haven't requested the specs yet.

I've checked the CFG and I don't believe any of the settings are related to rounding (I could be wrong).

I ran a report script on each DB, with each script increasing the number of decimal places. The numbers are inconsistent, but they should both round up to 116.

Output of DB1:
115
115.5
115.50
115.500
115.5000
115.50000
115.500000
115.5000000
115.50000000
115.500000000
115.5000000000
115.50000000000
115.500000000000
115.5000000000001
115.50000000000007
115.500000000000071
115.5000000000001137
115.50000000000011369
115.500000000000113687
115.5000000000001421085
115.50000000000014210855
115.500000000000142108547
115.5000000000001705302566
115.50000000000017053025658
115.500000000000156319401867
115.5000000000001705302565824
115.50000000000017053025658242
115.500000000000170530256582424
115.5000000000001421085471520200
115.50000000000017053025658242404
115.500000000000213162820728030056
115.5000000000002131628207280300558
115.50000000000021316282072803005576
115.500000000000227373675443232059479
115.5000000000002557953848736360669136
115.50000000000022737367544323205947876
115.500000000000213162820728030055761337
115.5000000000002131628207280300557613373
115.50000000000018474111129762604832649231
115.500000000000213162820728030055761337280
115.5000000000002131628207280300557613372803

Output of DB2:
116
115.5
115.50
115.500
115.5000
115.50000
115.500000
115.5000000
115.50000000
115.500000000
115.5000000000
115.50000000000
115.500000000000
115.5000000000001
115.50000000000007
115.500000000000071
115.5000000000001137
115.50000000000011369
115.500000000000113687
115.5000000000001421085
115.50000000000018474111
115.500000000000213162821
115.5000000000001847411113
115.50000000000022737367544
115.500000000000213162820728
115.5000000000002131628207280
115.50000000000018474111129763
115.500000000000213162820728030
115.5000000000002131628207280301
115.50000000000021316282072803006
115.500000000000213162820728030056
115.5000000000002273736754432320595
115.50000000000021316282072803005576
115.500000000000227373675443232059479
115.5000000000002557953848736360669136
115.50000000000022737367544323205947876
115.500000000000213162820728030055761337
115.5000000000002131628207280300557613373
115.50000000000018474111129762604832649231
115.500000000000213162820728030055761337280
115.5000000000002131628207280300557613372803
I'd be interested to know what rounding algorithm rounds
the integer
115.5000000000001137
to
115.500000000000071

IOW, in what number system does .1137 round to .710? By default I would think the next number (going up) after 115.5000000000001137 would be 115.500000000000114



Regards,
Robb Salzmann
Reply
Like
Share

Joined: September 18th, 2008, 7:30 pm

September 14th, 2011, 11:18 pm #7

So I can reproduce this on Sample.Basic

If I lock-and-send the value 115.5 to the intersection Jan->Sales->Florida->Actual->Product in Sample.Basic and I extract this number using a report script, I get 116.

That's what I expect.

If I lock-and-send the following values:
Jan->Sales->Florida->Actual->"100-10": 105.419
Jan->Sales->Florida->Actual->"100-20": 1.841
Jan->Sales->Florida->Actual->"100-30": 1.152
Jan->Sales->Florida->Actual->"200-10": 0.521
Jan->Sales->Florida->Actual->"200-20": 2.33
Jan->Sales->Florida->Actual->"200-30": 2.585
Jan->Sales->Florida->Actual->"200-40": 1.652

And then run a default calc, and re-run my report script, I get 115 even though those numbers add up to 115.5

Would anyone like to try?

Here's the report script:

"Jan"
"Actual"
"Product"
"Sales"
"Florida"
!
{DECIMAL 1} !
{DECIMAL 2} !
{DECIMAL 3} !
{DECIMAL 4} !
{DECIMAL 5} !
{DECIMAL 6} !
{DECIMAL 7} !
{DECIMAL 8} !
{DECIMAL 9} !
{DECIMAL 10} !
{DECIMAL 11} !
{DECIMAL 12} !
{DECIMAL 13} !
{DECIMAL 14} !
{DECIMAL 15} !
{DECIMAL 16} !
{DECIMAL 17} !
{DECIMAL 18} !
{DECIMAL 19} !
{DECIMAL 20} !
{DECIMAL 21} !
{DECIMAL 22} !
{DECIMAL 23} !
{DECIMAL 24} !
{DECIMAL 25} !
{DECIMAL 26} !
{DECIMAL 27} !
{DECIMAL 28} !
{DECIMAL 29} !
{DECIMAL 30} !
{DECIMAL 31} !
{DECIMAL 32} !
{DECIMAL 33} !
{DECIMAL 34} !
{DECIMAL 35} !
{DECIMAL 36} !
{DECIMAL 37} !
{DECIMAL 38} !
{DECIMAL 39} !
{DECIMAL 40} !
Run the test so that the values add up to 116.5 and see what that rounds to.

Some rounding techniques round odd numbers one way, and even numbers the other instead of always rounding 5 up.



Regards,
Robb Salzmann
Reply
Like
Share

Ben White
Ben White

September 14th, 2011, 11:21 pm #8

I'd be interested to know what rounding algorithm rounds
the integer
115.5000000000001137
to
115.500000000000071

IOW, in what number system does .1137 round to .710? By default I would think the next number (going up) after 115.5000000000001137 would be 115.500000000000114



Regards,
Robb Salzmann
http://en.wikipedia.org/wiki/IEEE_754-2008

Enjoy :-)
Reply
Share

Ben White
Ben White

September 14th, 2011, 11:25 pm #9

Well, actually, no, that's not very helpful. I think it's fair to say that anything past the 15th significant figure is essentially noise.
Reply
Share

Joined: April 13th, 2011, 5:00 pm

September 14th, 2011, 11:27 pm #10

Run the test so that the values add up to 116.5 and see what that rounds to.

Some rounding techniques round odd numbers one way, and even numbers the other instead of always rounding 5 up.



Regards,
Robb Salzmann
When I was a wee little boy, I was told that, because 5 was in the middle, you round to the nearest even number. I understand that has been replaced with a simpler "round up on 5" rule.
Reply
Like
Share


Confirmation of reply: