averageofChildren vs avg()/count()

hi,

which is better to use: averageofchildren or avg()/count() calculated measure, if i don't want to calculate average over time.

i have read in the "Analysis Services Performance Guide" that is better to avoid using semi-additive functions to improve performance.

Special aggregate functions require unique performance-tuning techniques. They

include DistinctCount and a collection of semiadditive aggregate functions. The

semiadditive functions include: FirstChild, LastChild, FirstNonEmpty,

LastNonEmpty, ByAccount, and AverageOfChildren.

thanks

Christina

Probably the normal average (sum/count) is the better choice if you won't consider time. AverageOfChildren requires a time dimension and Enterprise Edition.|||

i have both time dimension and enterprise edition.

but my question is, i have lots of measures in which i will be using the average. i would like to know if i use averageofchildren is convenient or better use the other way, in terms of performance.

thanks

|||

Now I am not answering your question again, but.. since the average of sum/count will be a calculated member, it will take zero processing time for the calculation, but the sum and count will take processing time (both probably much faster than AverageOfChildren). However, it will take time to calculate the value when a user executes the query. AvaregeOfChildren is used for measures and therefore will take some time to process, but then zero extra time for queries (if aggregated).

How they compare (process sum+count + calculate average vs just process) I don't know, but you can probably test this on your specific system. In general using measures is better than calculated members (faster for end users, but as said, that depends on how long it takes to process the measure vs calculate the member).

Cheers

|||

Thanks HappyCow.

i think i got the point. I'll go for averageofChildren and test accordingly. if i find it slow, will try the other way.

Hope you remain always happy..but not a cow.

Cheers