Build a very basic cube that has a dimension with a hierarchy and a single measure. For example, a time dimension with Year, Quarter, Month, Date along with a quantity sold. The quantity sold is an integer in your database.
Now, load up your database with test data such that the quantity when rolled up to a quarter level will be just less than the maximum value for an integer. Process your cube and see the absolutely stupid value that gets displayed for a year.
For those of you reading along and not wanting to build a cube to see what happens, here it is in a nutshell.
To make the math simple, we'll use 2 billion as the max value for an int and -2 billion and the min value. Say for example that the quantity sold for each quarter was 1 billion units. When you look at the cube from the quarter on down, you see
nice, neat POSITIVE values. But, when you look at the quantity sold for the year, SSAS reports the quantity sold as 0.
No, it didn't substitute 0 for a data type overflow (4 billion exceeds the 2 billion max). Instead, the developer of this "feature" decided that it would be a really nice idea to just ignore the fact that you had a data type overflow and
just treat the range of values as a looping structure. So, his code said, take the aggregated value, run it all the way up to the last value in the range and tak
View Complete Post