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

Comments on this post

# re: SSAS: Beware of measure data types

Requesting Gravatar...
I got this too, tried the bigint trick in the DB and it still failed. I ended up making it a float which suited me better.

Even better is my discovery that a BIT type, when summed, is negative because it sign extends it. The only way i fixed this was to make a calculation that is set to (-[measures].[abitflagforagging])
Left by RES on Oct 21, 2008 3:30 PM

# re: SSAS: Beware of measure data types

Requesting Gravatar...
You can't really sum a bit value, it only has possible values of 0 or 1. You would need to cast up to a larger data type if you wanted to sum all the 1's
Left by Darren Gosbell on Oct 22, 2008 8:06 PM

# re: SSAS: Beware of measure data types

Requesting Gravatar...
changed the data type to BIGINT in all measures of the cube where as it is Integer.

But deployment is failing giving the error like data type is not same as source.

Left by Naga on Apr 16, 2010 3:44 PM

# re: SSAS: Beware of measure data types

Requesting Gravatar...
@Naga what you are facing is a different issue. Have you tried refreshing your DSV? Or have you tried installing BIDS Helper and checking your dimension data types? (see http://bidshelper.codeplex.com/wikipage?title=Dimension%20Data%20Type%20Discrepancy%20Check&referringTitle=Home)
Left by Darren Gosbell on Apr 17, 2010 8:37 PM

Your comment:

 (will show your gravatar)