Aggregate Function Average of Children does not work
Marc
another question :-) - I have a mesure sales price. I want to use the
Aggregate Funktion Average of children. But it seems that it does not work.
I'm testing sql server 2005 64bit edition Enterprise Edition from MSDN on a
64bit box, not the real one.
How can this happen?
Regards,
Marc
privatenews
Thank you for posting in MSDN newsgroup!
If I understand the issue correctly, when you change the aggregate function
of a measure to averageofchildren, it does not take effect, and SUM is
still used by the measure. If I'm off-base, please let me know.
This behavior could occur because
AverageOfChildren,FirstChild,LastChild,FirstNonEmpty and LastNonEmpty are
semi-additive and treats the Time dimension different from the other
dimensions. Please refer to the following link for details:
http://msdn2.microsoft.com/en-us/library/ms175356.aspx
The description in above link (BOL) is not very clear about
AverageOfChildren, and I have forward this feedback to the proper channel.
The AverageOfChildren only applys when aggregating via Time dimension.
Actually, when you try to create a new measure in cube, when you select
usage, you could see "average over time" which is for AverageOfChildren.
To get the result of average behavior you want, you may want define a Sum
and a Count measure, then create a calculated measure (in the cube script)
which divides the two base measures.
If anything is unclear, or you have further questions, please feel free to
let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Marc
This was exactely my issue - I missunderstood the concept of the
AvarageOfChildren aggregate function. I thought this is an average over all
dimensions, not just over the time dimension and the rest with the sum
aggregate function. mmmm.
OK, I will create a calculated measure in the cube script as proposed.
Thanks!
Marc
privatenews
My pleasure. :-)