Aggregation - Incorrect results

All,

I have noticed a behaviour in SSAS 2005 that I would like to put forward to understand it better and to know if anyone else have encountered similar issues.

I have Cube with 4 measure groups (with only 1 visible to user. Others are for implentation for Many to Many dimension and Currency conversion). The measure group visible to user has 1 partitions initially and other are created on fly using AMO. The aggregation design is cloned from the initial partition. When I create aggregation design using the inbuilt aggregation design wizard, it is working fine. But when I use the Agg manager utility from the BIDS Helper addin and create agg from querylog and process the cube, the result returned from the cube is incorrect.

My question : Why does an aggregation making the cube to return incorrect result?

Cheers,

Arun

Hi Arun,

Does your aggregation include an attribute from a non-materialized reference dimension (see issue below)?

...

To make a long story short, I had manually designed aggregations that I shouldn't have. We have a reference dimension with materialized set to False. This reference dimension should not have been included in any aggregations. The Aggregation Design Wizard is smart enough not to do this, however you can hack the XMLA to do so yourself. The Aggregation Manager Utility included in SP2 also allows one to add aggregations to these type of dimensions.

...

What the correct behavior should be

When Materialize is set to False for reference dimension, aggregation should not include any attribute from the reference dimension. The correct behavior should be the processing would raise an metadata validation error. A design change request to implement this has been filed to the next version of Analysis Services.

What is the problem

With this aggregation included in the cube, the aggregation creation routine does not know how to aggregate the measure from fact and the reference dimension attribute as the fact does not have the attribute from the reference dimension, therefore the measure is null for this aggregation.

...