April 23, 2010 at 12:13 pm
Is there a way to replicate the AverageOfChildren aggregate function
using SSAS 2005 Standard Edition? It is semi-additive and only
available in Enterprise Edition but I am wondering if there is a way
to achieve the same result using Standard Edition, with a little more
work of course.
April 26, 2010 at 1:02 pm
Define your measure with an MDX expression
Something like
Avg(Descendants(DimensionName.CurrentMember, ChildMember level you wish to use), Measures.Measure you would like to Avg)
Good luck.
April 26, 2010 at 1:46 pm
So you can define one calculated measure which uses an average aggregate function for time dimension but a sum aggregate function for all other dimension types? My MDX skills are lacking, which is why I am asking this basic question.
April 26, 2010 at 2:01 pm
jcaldwell-752832 (4/26/2010)
So you can define one calculated measure which uses an average aggregate function for time dimension but a sum aggregate function for all other dimension types? My MDX skills are lacking, which is why I am asking this basic question.
I think the short answer here is no, but your follow up makes me think that perhaps I did not understand your original query.
If you create a measure that is an average, and you apply that measure across any or all of your dimensions you are still going to get an average.
April 26, 2010 at 2:09 pm
AverageOfChildren is an AVG in a time dimension but is a SUM in all other dimension types. It is only available in Enterprise Edition. It works well for snapshot data. For example, if you count a group of people every single month at various locations, you will want SUM the population throughout your location hierarchy because they are different people in different locations; but month to month, you will want to AVG them because they are the same people, more or less. It works well for inventory snapshots as well.
April 26, 2010 at 4:36 pm
jcaldwell-752832 (4/26/2010)
AverageOfChildren is an AVG in a time dimension but is a SUM in all other dimension types. It is only available in Enterprise Edition. It works well for snapshot data. For example, if you count a group of people every single month at various locations, you will want SUM the population throughout your location hierarchy because they are different people in different locations; but month to month, you will want to AVG them because they are the same people, more or less. It works well for inventory snapshots as well.
Apologies to all. Apparently I need to brush up on AverageOfChildren.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply