Float data types

Question

Float data types

Tic4815

Old Hand

Points: 308
More actions
April 9, 2015 at 6:31 am

#148883

I don't really know SSAS but have been a .NET programmer and worked with SQL Server and T-SQL for years. I am working on a project where there is a database storing sales transactions and a cube that is created nightly so we can get MTD Sales, YTD Sales, Profit $, etc.
I just ran into an issue where I got an error retrieving the Profit $ measure (using an MDX query inside of a SQL stored procedure using OPENQUERY) and found that the profit $ should be 0.07 but is coming back as 6.9999999999993179E-2. After talking to our DBA, he said all of the calculations in the cube are double precision floating point and I need to round all results to 2 decimal places (programs like Excel handle the rounding and I should too.)
Our DBA also claims the measures can't be calculated and returned as currency. He claims that the potential for error is very small and no one has ever complained about a $ value being off.
I am struggling with this answer because I have always been told never to use floating point for monetary values. I am having a hard time believing there is no way to have a measure be a currency field that is accurate. I looked for articles online but can't seem to find anything to answer my questions.
Could someone please point me in the right direction? Thanks so much!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17461 More actions · Answer 1

You are correct in the sense that Floats shouldn't be used for monetary data. In database terms, DECIMAL or NUMERIC should be used. The problem with SSAS is that it will convert DECIMAL and NUMERICs to Double or Single. SSAS does have a Currency data type which can be used and will round out to 4 decimal places.

You have a couple of options (without the DBA actually changing datatypes at source):

1. Point the DSV to views rather than directly to tables and perform a CAST in the view.

This is the approach I always use, mainly because it's easy to maintain and creates an abstraction layer from the database.

2. Use a Named Calculation in the DSV to do the CAST.

3. Format the value in MDX using the FORMAT_STRING function.

4. Set the data type in the Measure properties.

Whatever method you choose, for your DBA to say that they will continue to use Floats for monetary values because of a low probability of error is bad and just wrong. Even Microsoft themselves say:

"Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types."

https://msdn.microsoft.com/en-us/library/ms187912%28v=sql.105%29.aspx

Show your DBA that, if they continue to argue the point then they are a moron of the highest order.

I'm on LinkedIn

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

PB_BI (4/9/2015)
Show your DBA that, if they continue to argue the point then they are a moron of the highest order.

Not really. Without using FLOAT datatypes, you calculate a 30 year mortgage amortization schedule in SQL Server and have it come out exactly to the penny at the final payment. Do the same in Excel and see how far your SQL is off. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PB_BI SSCoach Points: 17461 More actions · Answer 3

Jeff Moden (4/9/2015)
PB_BI (4/9/2015)
Show your DBA that, if they continue to argue the point then they are a moron of the highest order.
Not really. Without using FLOAT datatypes, you calculate a 30 year mortgage amortization schedule in SQL Server and have it come out exactly to the penny at the final payment. Do the same in Excel and see how far your SQL is off. 😉

So you're saying that they're all wrong and excel is right? Explain.... 😛

I'm on LinkedIn

Tic4815 Old Hand Points: 308 More actions · Answer 4

As with many things, it depends on your exact circumstance and I apologize for not including this info:

We have 2 measures, total sales $ and total cost $ for all tickets. We never go beyond 4 places after the decimal on these. Profit $ is a calculated measure (sales $ - cost $.)

Even though the source DB has the sales $ and cost $ as decimal, I think the DBA set everything up as a float in the cube.

We would never need the type of precision that an amortization would require.

PB_BI SSCoach Points: 17461 More actions · Answer 5

In which case it's not your DBA that is the problem. As I said in my first reply, SSAS reads DECIMAL as DOUBLE or SINGLE which are FLOATs. Change the measure data type to Currency to round up to 4 decimal places.

I'm on LinkedIn