Aggregation functions in Calculated Measures displays wrong values.

Hi,

I think this calculated measure implementation is making me absent minded, so if this seems like a silly question, please ignore my behaviour but do answer to my post :-)

I will ask this question with a sample data: consider that the cube consists of School Children's names as the first dimension (school_children) and date(jan, feb....) as the second dimension. the measure (M) is the 'exam scores' of the school children.

jan feb mar
school_children M M M
--
tony 50 20 40
bony 10 40 40
mony 60 60 70

Now when i add a calculated measure where I want to display the avg marks of each. so in the calculated measures formula I add: Avg([Measures].[M]). (This is how it is in the Oracle OLAP :-))

But this does not display the average of all tony's scores in a new column M2 (calculated measure). it just displays the same values as the measure M.

so what is happening here? how to get the average then? I do not want to use an avg Aggregation. I thought that I would probably have to programmatically convert all avg functions to something like this: [Measures].[M] / count([Measure].[M]=tony or soemthing like this. not sure again.

The Avg function receives a set as a first parameter, and the measure you want to calculate the average of as a second.

The second parameter is optional, so in this case you are saying to Analysis Services: "give me the average of the measures in the current query context for the set [Measures].[M]" That is: (Measures].[M] / 1)

Since you want to calculate the avg along the time dimension, you should say:

Avg([Time],[2006].Members, [Measures].[M]).

For mor information on Avg see:

http://msdn2.microsoft.com/en-us/library/ms146067.aspx

and

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1

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.

...