October 3, 2011 at 2:51 pm
I have small integer fields that contain all positive numbers and when summed reach into the millions. The excel spreadsheet connected to the cube shows an incorrect value which is negative to boot.
A google search has yielded nothing, but I can't be the first to have seen this. Does anyone have any ideas?
Thanks,
October 3, 2011 at 3:00 pm
Just a punt, but perhaps you've 'rolled the clock' on your smallint? If a small int can only range from -32768 thru 32767 but you want this to sum to the 'millions', you may have over-flowed the data type, resulting in a strange, negative number. Why not try changing the datatype of the measure to something that will/can handle the larger resultant value?
Steve.
October 3, 2011 at 3:51 pm
I agree, I have seen this happen in SSAS before when the sum of the value is larger than the data type the number turns negative.
October 4, 2011 at 2:14 am
I thought of that, and will try it if nothing else comes up. I've disregarded that up to this point, however, because added plenty of fields that have a numer larger than there actual integer type, including tinyints that sum up to numbers well beyond 256. As long as the number fits in the original data type, the SSAS summed value does not have to fit into that data type.
It's probably just something stupid I've overlooked. Just hard to imagine what.
October 4, 2011 at 2:31 am
I started relooking the issue again. There is a number in the same cube and same spreadsheet with the same data type and it adds up perfectly, and it's the largest number of the four. The other three are a subset, although they are not calculated fields.
Again, I don't want to rule anything out, and I'll try that if nothing else comes up, but it's working fine in this other instance.
October 4, 2011 at 8:58 am
I have found the stupid thing I overlooked. I had to manually override the data type in TWO places, both properties of the measure. I had altered it in one only to get deployment errors. Only by carefully comparing it with working ones did I see I had changed both, even though one looks like it's automatically selected when you create the measure.
When you mentioned the data type in your posts, I was thinking in the actual table, not the measure properties.
I'm tempted to remove the entire post from embarrassment that I overlooked something so stupid, but it might be useful to someone at some point.
Thanks for the comments.
October 5, 2011 at 7:02 pm
I had to change BOTH the data type field in the Advanced section of the measure properties and the data type field in the Basic section. The second is only visible if you click on the + sign by the Source field.
There's probably a good reason this appears twice, but on the face of it it seems redundant to me.
October 5, 2011 at 7:45 pm
You set them both to?? Int? Just interested to know if you did blow out the small int size and set it to something larger.which ended up working.
Steve.
October 6, 2011 at 3:58 am
Yes, I set them both to UnsignedInt, since there's no possibility of a negative number.
October 6, 2011 at 4:03 am
RonKyle (10/6/2011)
Yes, I set them both to UnsignedInt, since there's no possibility of a negative number.
Have you checked the values in the cube to see if they are accurate? As this was an overflow issue I would have been more inclined to set the measure data type in the cube to BigInt to avoid the overflow.
October 6, 2011 at 4:13 am
I have and they are. The numbers don't come anywhere close to the upper limits of an integer.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply