numbers with 13 digits ?

[Fweimar]

After loading data from an ORACLE database with the format decimal(13,2) we end up finding numbers in TM1 that look like 90.000000000001 (we exported the data and find these numbers in the .cma file)
and therefore also consolidated values that look like 0.0000000000001

We apply rules that check if a consolidated value <> 0.
All these rules fail, since 0 <> 0.0000000000001

We do not manually spread , we just load data .
Workaround is to use ROUNDP( number,0).
Anyone that has seen this behaviour before ?

using TM1 10.1.1 64 bit on Windows2008 R2

Regards,

Fabian

[declanr]

If I have understood your post correctly I don't think you are experiencing a bug.

TM1 stores values as binary floating point values; some 2 decimal place values can't be stored in this format which is when you start seeing the .0000000001s etc

[Fweimar]

It happens anyway ....

We see these numbers directly imported from ORACLE. Is it possible that ORACLE stores them like this ?

"validation:NUN_LEDGER","IT1:08023120","IT2:FAC1","IT3:XXX","IT4:","IT6:","AC","RAW","1284","PER:201206","Credit (LCY)",-1954.01
"validation:NUN_LEDGER","IT1:08023120","IT2:PRC1","IT3:XXX","IT4:","IT6:","AC","RAW","1284","PER:201206","Credit (LCY)",-179.59
"validation:NUN_LEDGER","IT1:08025120","IT2:CPT1","IT3:MEM","IT4:","IT6:","AC","RAW","1284","PER:201206","Credit (LCY)",-46.789999999999999
"validation:NUN_LEDGER","IT1:08025120","IT2:PRC1","IT3:XXX","IT4:","IT6:","AC","RAW","1284","PER:201206","Credit (LCY)",-1294.8
"validation:NUN_LEDGER","IT1:08027120","IT2:FAC1","IT3:XXX","IT4:","IT6:","AC","RAW","1284","PER:201206","Credit (LCY)",-328.91000000000003
"validation:NUN_LEDGER","IT1:08027120","IT2:MCO1","IT3:XXX","IT4:","IT6:","AC","RAW","1284","PER:201206","Credit (LCY)",-267.05000000000001

[declanr]

If you want to know how oracle is storing the numbers surely your best option is to query directly without using TM1 as a medium?
Just run a basic sql query to pull the top 1000 rows etc and see what you get.

[Duncan P]

If the columns in the Oracle table are of type BINARY_DOUBLE or BINARY_FLOAT then yes, the numbers in Oracle have the error. If they are defined as NUMBER then no.

However between you and the Oracle database is a lot of code. If any of that code is using IEEE floating point it will introduce the error on numeric data. You could get round it by converting to text in the SQL query and then doing the conversion to number in the TI script.

[Fweimar]

The actual inconsistency is between what I see in the TM1 Viewer and what is exported into the .cma file.

How do you explain that ?

Fabian.

[Duncan P]

It's explained in this thread http://www.tm1forum.com/viewtopic.php?f=3&t=6072.

[Fweimar]

So using the ROUNDP(<32bit float>, 2) is the only way to apply rules that query a cell for the value 0,00 ?

Fabian

[David Usherwood]

Yes.
TM1 works entirely in full floating point.
Try setting your format to (say) 0,00.0000000000 and see what the cube viewer shows.
I would use a test similar to
if(abs(roundp(['Value'],5) = 0,
...