January 5, 2016 at 9:43 am
Hello,
I am struggling with this weird issue, using SSAS 2012 SP3:
The cube is summarizing those exact values (leaf level), no MDX calculation involved there:
2635760.279
1882685.913
107582.0522
-2635760.279
-1882685.913
-107582.0522
The sum should be 0, right? But instead the returned value (SSMS and/or Excel) is 4.36....E-10. If you try to copy/past those numbers directly in Excel, with format = general you will have the exact same result than SSAS (but it is correct if the format is set to number).
The measure is based on a SQL Server view, datatype is numeric(24,8). Nothing is done to change this in the DSV (datatype = Decimal).
Then my measure in the measure group it correctly set to double, and bids helper Measure Group Health Check is happy with that.
The result different than 0 is an issue because some analysts are filtering value to show the 0...Any idea?
January 8, 2016 at 10:34 am
I think part of the issue is the internal use of the FLOAT data type. See this article for more information: http://www.sqlservercentral.com/blogs/richard-douglas/2013/02/01/float-datatype-casting-issue/
What happens if you use the Currency data type instead?
January 11, 2016 at 9:23 am
Thank you for your response.
Indeed, in the mean time I did this downsizing to Currency for a particular measure, and it is now working for this one.
But downsizing the other measures is not an option, I need the double datatype. But just for testing purpose, even if I cast those measures (in the underlying view) as numeric(18,4), bids helper Measure Group Health Check only wants Double datatype in the cube. The options in the drop-down list are Double and Single (no Currency), but if I select Single, everything is rounded as an integer.... :crazy:
I just cannot believe that a simple sum of a few numbers as Double could be so wrong...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply