Unaggregatable M2M Dimensions
I was called upon to troubleshoot an issue with creating an aggregation design for an existing cube. The Design Aggregation Wizard would refuse to design aggregations with the following cryptic message:
Errors in the aggregation designer. The ‘Perspective’ dimension has no attribute for the aggregation design. The server cannot design aggregations when this attribute is missing.
Upon a closer look, it turned out the Perspective dimension joins the measure group via a many-to-many relationship. More importantly, its IsAggregatable property was set to False to suppress the All member because it would be meaningless to aggregate data across the members in this particular dimension. Instead, the cube script would set the dimension default member with an ALTER statement.
How do we solve this horrible problem? Here is my solution:
- Set IsAggregatable property of the dimension attribute back to True.
- Add a scope assignment to the cube script to nuke the All member. To be more specific, here we set the All member to null to take advantage of the fact that most browsers suppress empty cell and the All member won’t show up.
Root ([Perspective]) = null;
- Design aggregations as usual.
Notice that I don’t remove the ALTER statement to assign a default member. If there is no default member, you would see that slicing by any other dimension other than Perspective would miraculously return no data, just like it would if no data exists in the cube. You must set up a default member if you remove or set the All member to null.
