Data Warehousing and Data Science

21 September 2010

Aggregate Not Shown

Filed under: Analysis Services — Vincent Rainardi @ 6:10 pm

Perhaps this post would come handy if one day you encountered this issue: The cube displays Measure1 at the day level but not at month or year level. I was guessing:

  1. The dimension relationship between Measure1 and other dimensions (other than date dim)
  2. The attribute relationship from day to month in the date dimension
  3. Aggregate design assigned to the partition where Measure1 is located
  4. Scope on MDX calc that overwrites the month & year level with null

After scratching my head investigating all the above systematically, the answer was: none of the above. The cause was there was a default member set on one of the dims.

A tip to troubleshoot “measure is empty” issue:

  1. Copy the BIDS project to project2.
  2. Open project2 and in the dimension relationship remove all of the MGs except 1 (the one that you are having problem with)
  3. In the dimension relationship remove half of the dimensions (the one that you think is unlikely to cause the issue)
  4. Deploy & process the cube as different name, e.g. cube2.
  5. If the measure is displayed, it means that one of the dims that you removed caused the problem. If the measure is not displayed, it means that one of the dims that are still there caused the problem.
  6. Keep doing 3 & 4 until you found the dim in question.

Note: when doing #3, instead of removing the dim you can also set the relationship to none (right click the intersection cell and choose delete, no need to click ellipsis and go into the dialog box).

As usual I welcome any discussion and questions at Not just about this post, but also about data warehousing and BI in general.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

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

Blog at

%d bloggers like this: