AverageOfChildren Standard Vs Enterprise

Hello,

My question may be a simple one, is there a way to do an aggregate average function without buying enterprise edition. When I try to average a dimension instead of summing (for example a field with Percents) I get a error that the AverageOfChildren feature is not avaible in the Standard version of SQL 2005. Is this really true or did I set something up wrong.

I would have a hard time convincing my company to buy Enterprise edition because basic functionality like averaging is not available in the standard edition... I have found the features by edition page: http://msdn2.microsoft.com/en-us/library/ms143761.aspx however there is nothing on here that tells you simple dimension aggregations are unavailable in the Standard Edition.

Can some one tell me if there is a way to get around this without spending 10G on an upgraded version?

Thanks,
josh1234

It is correct that some aggregation functions are not available in the Standard Edition - specifically the semi-additive ones. For an average, however, you should be able to get around the issue quite easily. Create a measure with aggregation function "Sum" for the measure you want to average. Create another measure (for the same field in the database) with the aggregation function "Count". Now, create a calculated member in the cube, which divides the sum by the count. Remember to check for division by zero:

Example:

CREATE MEMBER CURRENTCUBE.MyAverage AS

IIF([Measures].[MyCount] = 0,

NULL,

[Measures].[MySum]/[Measures].[MyCount],

NON_EMPTY_BEHAVIOR = [Measures].[MyCount];