Two Facts In a Measure Group – One Correct, One Incorrect

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.
Totally frustrating.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Two Facts In a Measure Group – One Correct, One Incorrect

  1. Dan says:

    Makes you wonder how everything else works doesn\’t it?  Glad you figured it out, but wow what a doozy.  Almost seems like something was messed up with the metadata possibly.  Did you have to refresh the data source view at all, wouldn\’t think so.  How weird though.  Makes you want to dig into it more and figure it out; sometimes you just need to accept it and move on.

  2. Mosha says:

    Jesse – I don\’t think you solved the problem, you probably just hid it. I don\’t know what the problem is, of course, but usually it has to do with attribute relationships – I suggest you double check those. If RI is broken – even stranger things could happen.

  3. Unknown says:

    hi, I\’m facing a similar problem. The problem is my count of id does not match. I tried every possible thing but nothing worked.So i don\’t know what to do next. need help

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s