I have a simple model: 

datatypes of fact value:

SQL: numeric (34, 17)

tabular: val_fact: Decimal Number

The data is as follows:

In the dimension there are 906,499 rows. breakdown as follows

column_desc    count(*)
NULL               346,288
A                     47
B                     560,164

Sum of facts by dim is as follows in SQL: 

column_desc    sum(f.val_fact)                                         count(*)
A                    22797975.60234741981400000                 141
B                    -505752798136.03296564810300000         890,098

The problem is that in Excel via a Pivot I am getting inconsistent results. Sometimes column B is:

-505752798136.50800000 or -505752798136.03300000

If I refresh the pivot the value flips between the two values above! This is very strange so the aggregation is not consistent. 

I'm using Excel 2007 SP3 and SQL Server 2012 SP 2 for SSAS tabular cube.

In SSMS via MDX the sum for B is -505752798135.508. The results are inconsistent.

To summarise for dim member B:

SQL: XXXXXXX.032965648103

Excel: XXXXXXX.033 or XXXXXXX.508

MDX: XXXXXXX.508

There is no issue with the aggregation for the Dim member A.

This is confusing for our users, who need the result to be consistent. Does anyone know why this would happen and what would be the fix?

Can you try using Currency data type in Tabular? If it works, it is a known issue in floating point rounding - but the difference seems strange to me, usually the problem is minimal (in decimal) but generate issues when you try to match the exact value.

Currency is consistent, but still doesn't match exactly to the aggregated value using from the SQL DB (XXX.0329656). I understand that there must be rounding at some point. Even so, I would have expected when using decimal that the result would be consistent, not a diff of 0.5 depending on how it is queried.

• Edited by Tom Stagg Monday, September 29, 2014 10:12 AM typo

This is strange - I would suggest you to do more investigation because probably it's not the rounding error I was referring to but some other difference in data and/or in calculation.

We've reproduced this on a number of machines and have raised it with Microsoft. The data and calculations are simple and have been checked by multiple developers. I've also raise a connect item for this. https://connect.microsoft.com/SQLServer/feedbackdetail/view/984441

Tom, thanks for notifying that - I will monitor the Connect issue.

Thanks,

The behaviour has been explained by Microsoft. We needed to change the SQL DB to Money and the Tabular cube to Currency. Otherwise a cached result using a float type can result in approximate values as per behaviour above. In our SQL DB our fact table stored data at decimal(34,17) and there is no corresponding datatype in SSAS with that level of precision. Closest is decimal using 8 bytes only. 

From MS: 

There is 3 reasons for the issue:
1/ The data type used is a IEEE float type that is an approximated value (Real / Float)
2/ In certain circumstances SSAS is able to reuse previous result that have been stored in the cache
3/ Because of 1 and 2 the SUM is made with a different order depending if the entire column is scanned or only a part of it using the indexes

At the end with the 3 points above you obtain a result that change depending of the different conditions.
To avoid this issue an exact data type must be used from the data source (e.g Currrency / Money).

Workaround: To avoid this issue an exact data type must be used from the data source (e.g Currrency / Money).

• Marked as answer by Tom Stagg 2 hours 38 minutes ago

Hi Tom, thanks for the update, it is a very interesting behavior.