Aggregation Problem: Sum of Field does not match data.

[DanEvans]

We have a frustrating problem with a summed field in our cube.

Basically, it is like this:
We look in the fact table and see that the sum of a particular field (tinyint) is, say, 652.

When we pull up the aggregation for the user, the cube shows 650, two short of the total.

This obviously seems like a problem with dimensions...i.e.. an inner join is elimitating records. The problem with that theory is that we have checked all of the dimensions for the data in question, and there are no obvious possibilities for ommission. Also, most of the dimensions would cause other fields to be affected as well, and there is only one field impacted on this.

This is a frustrating problem because it seems so basic, and yet we have had a variety of people spending a LOT of time on this. Admittedly, we are rather new to OLAP processing.

Is there anything that anyone can suggest we look into? For instance, we had some currency fields we were summing, and we discoverd that MS has a known bug when aggregating this data type. Is there some similar tidbit of minutia that migh account for this kind of difference?

Your help is appreciated.

Thanks,
Dan

 

[MDXer]

is your cube schema optimized? an unoptimized cube forces the sql spawned to join out to the dimension which I have seen cause data issues going both ways.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck