SSAS needs larger datatypes to store currency (128-bit currency and/or decimal) - by James Stange, Jr

Status : 

 


5
0
Sign in
to vote
ID 757395 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 8/7/2012 1:34:09 PM
Access Restriction Public

Description

    I want to also stress the need for larger numeric data types in SSAS.  As an employee of an insurance firm we must deal with very large numbers (a.k.a millions of clients insured for 
thousands to millions of dollars).  Adding to this mix are foreign currency translations (for example Korean Won = USD x ~1128), multiple year running balances and an 'absolute value of account' measures used by auditors to reconcile between systems.   On cubes built on this sort of data aggregates easily exceed the 922 trillion that the "currency" (which is a modified 64-bit int) can represent.  Since the "currency" data type silently rolls over, like an old fashion odometer, when an overflow occurs this rules out using the "currency" data type.  This leaves as our only recourse to use a "double" datatype that has less decimal precision but does but doesn't overflow or rollover.  The catch to this is that the lack of precision causes rounding errors that are not acceptable to accountants that expect balance sheet accounts to zero out to the penny.  Trust me, explaining to an accountant floating point number precision is not a good way to spend a day and provides no relief to them when they encounter occasional penny to multiple dollar inaccuracies.

This need should be addressed by offering one or more of the following:
  1) 128bit implementation of currency, a.k.a a "bigcurrency" datatype.
  2) A decimal datatype like that in SQL Server with it's large decimal precision.

Larger numeric datatypes are a pragmatic need that without many financial institutions will be forced to seek out other BI products.
Sign in to post a comment.
Posted by SteveH_UK on 9/18/2012 at 6:56 AM
Fully agree. If we want to aggregate on large data sets (billions of rows) with accuracy, SSAS is unusable. SQL Server Database can be used with ColumnStore support for storage of 18 decimal digits but calculations at greater precision, but SSAS doesn't even have that. Alternative products, such as Sybase IQ, recognise the need to support greater levels of precision and support decimals with over one hundred significant digits.

IEEE floating point types are wholly unsuitable to this task.