SSAS: Beware of measure data types

There was a thread today on the Analysis Services forum where someone appeared to be having an issue with a data type overflow. Multiple large, positive, integer values were aggregating up to a negative amount. If you are interested the full thread is here: Re- negative values on a measure

Basically the database and the DSV were increased to a bigint data type, but the issue was still occurring. What I believe is happening here is that the measure in the cube was set with an int data type. To fix this, edit the cube, go into the cube structure tab, click on the measure in question and change its data type from int to bigint. The re-process the cube.

BI Dev Studio (BIDS) does not check that the data types are the same (or greater) in the cube when compared to the DSV. If any of your fact table records overflowed the data type you would get an error message when you processed the cube. But if the aggregation of the measures exceeds the data type it silently overflows.

If you set up a 2 record fact table with 2 billion in each amount column and the measure's data type as int, you get the following result.

2000000000  Record 1

2000000000  Record 2

-294967296  Total <= Overflow!

Whereas when you change the data type of the measure to bigint, you get the following (correct) result

2000000000  Record 1

2000000000  Record 2

4000000000  Total

This issue has already been reported to Microsoft here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=219523

Apparently it is "being investigated for the next version", but the current workaround was to manually set the data type to bigint.

Print | posted on Monday, November 27, 2006 1:26 PM