Aggregations causing bad numbers?

Last week I wrote about incorrect totals being returned when performing a drilldown. I believe I know what the cause of it is – aggregations.

 

Figure One: Grand Totals are correct – they represent the total from the fact table

 

Figure Two: Drill down into Country Tier. Totals have changed

 

Figure Three: Altered partition 3/8/07 so it contains no aggregations and fully process. Also fully process 3/12/07. 3/8/07 now returns correct totals and 3/12/07 does not (numbers have changed). 

I have no idea what’s going on except something is wacked in the aggregations. This has happened in several measure groups, not just one.

Anyone know what’s going on or have experienced something similar? My end users are finding these bad numbers and it’s really causing a loss in confidence in Analysis Services.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Aggregations causing bad numbers?

  1. Chris says:

    One thing to check is your attribute relationships. Take a simple time example: you have Year, Quarter and Month attributes and you have set up relationships from Month to Quarter and Quarter to Year – if have you got a relationship going from Month to Year too then this can potentially cause incorrect numbers. You should always create your relationships as long chains because AS understands transitive relationships; redundant relationships as described confuse it. Just and idea…

  2. Greg says:

    Jesse, one quick way to validate that your attribute relationships are correct is to use BIDS Helper to validate them for you:
    http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Dimension%20Health%20Check
     
    It\’s not released yet, so you\’ll have to compile the source code yourself for the time being.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s