I ran into a situation recently that had me stumped for a bit, and there didn’t seem to be any other posts about this when I searched.
I was working with a cube containing a very straightforward measure. This was an integer column and used an AggregationType of SUM. No other calculations were involved.
When view the top level value for the measure (in other words, the All member for all attributes in all dimensions), the value of the measure was negative.
Once you drilled down, the values became positive.
Normally when I see behavior like this, I would assume that unary operators were at work, and were set incorrectly. But that clearly was not the case here – since I had built this dimension myself, I knew that there was no account intelligence or unary operators.
I traced back through the query in the Data Source View all way to the underlying relational data. There were a few negative values, but not enough to account for the large negative numbers I was seeing.
It finally turned out that the magnitude of the SUM was overflowing the Int data type. By changing the data type of the measure to BigInt, the values started to show up as expected.
Make sure you change both properties. This will fail when you try to deploy:
This will suceed because all the data types agree:
My usual practice it to let the data types of the underlying database determine the data type of the measure in the cubes. Here was one case where the data created an overflow so that the data type needed to be adjusted.
Now the drilled down values are correct, and so is the All level
Seems obvious now, but it didn’t last week when I was scratching my head trying to figure out where these mysterious negative numbers were coming from.
