It’s been awhile since I’ve run into a "bang the head against the wall" problem, however today I ran into a doozy.
I had a measure group (no aggs, two summed measures) associated with three dimensions. There were some modifications done to the fact table that would now allow this measure group to be associated with seven dimensions. Great, more detail! Since these dimensions were already in the database and in the cube, all I’d have to do is just set up a few relationships through the dimension designer, reprocess, and BAM.
I do just that and validate the numbers against the fact table. While measure number one looked correct and returned a sum of 29,875.40, measure two returned incorrect numbers. The fact table had 34,450.60 and the cube was returning 34,448.20 – only a difference of 2.40. I process the indexes, process update the dimensions, reprocess the entire cube database, drop and recreate the partitions, and a few other things - NOTHING. It didn’t make any sense why would one number would match and the other didn’t – they were both coming from the same measure group and fact table.
One by one I removeed the new dimensionlal relationships in the measure group. By removing the first dimension the difference drops to 2.15, dimension number two – 1.87, dimension number three – 1.19, ect. There was no rhyme or reason to the difference narrowing.
After four hours I open up the cube live through Visual Studio, and on the Cube Measure, tab I rearrange the order of the two measures. Just drop and drag the bottom measure above the other. Reprocess, check the numbers..and…what do you know…the numbers finally match up like they should.