I came across something today which stumped me for quite a bit.
For the last week I’ve been working on getting a test cube up with some real world data. I populated the cube with about 90 days of test data and started browing. All was well until I noticed one of the measures had a sum of a negative number when looked at on the month level. Confused I drilled down to the day level and the numbers looked normal. Drilled up, negative number, drilled down, postive numbers. How in the world could these positive numbers add up to a negative number?
Positive numbers on day level
Negative number on month level
I concluded that it was a problem with the data type. It was only when the total was in the billions that we received the negative number.
The measure in question was performing a sum on the record from the fact table. On the individual row level this is usually a small number, less than twenty. However, there are a few million rows per hour resulting in a total sum of that fact of more than 100 million for the day. The column in the table has a data type of integer (even though it could have been a smallint).
My first idea was to change the DataType of the measure to BigInt (max value of 9,223,372,036,854,775,807). Gave it a shot and received an error run running the Aggregation Design Wizard. This didn’t work since the DataType of a measure which has an AggregationType of Sum must be set to Inherited (or what the inhertied datatype is). Only Count and Distinct Count can have their DataType set to something besides the native data type…I quickly realized that the fix wouldn’t come within SSAS.
That meant that either the DSV had to be changed or the view had to be altered in the RDBMS. I opted to change the view to cast the fact as a BigInt (my reason for not using the DSV will come in a later post). This worked and the number is now aggregating at the month level correctly.
Interestingly enough this was not a problem in AS2000.
Moral of the story: Consider what the total sum is going to be of each your measures and set the datatype to the maximum number, not the data type on the individual row level.