Page 1 of 1
numbers with 13 digits ?
Posted: Thu Sep 06, 2012 7:19 am
by 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
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 7:38 am
by 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
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 9:22 am
by 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
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 9:28 am
by 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.
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 9:44 am
by 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.
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 9:49 am
by 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.
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 9:55 am
by Duncan P
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 10:10 am
by 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
Re: numbers with 13 digits ?
Posted: Thu Sep 06, 2012 4:45 pm
by 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,
...