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

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];

AverageOfChildren Aggregation function gives error.

Hi

This is a very peculiar problem, and I did not find any entry on the net regarding this.

Background: I have written .Net 2.0 programs that use AMO and create the complete gamut of SSAS 2005 objects at one go. They create Dimensions, Cubes, MeasureGroups, Measures, Partitions....etc. This is working and after processin the objects, they are browsable also.

Now the problem is: When i create a measure, I am not specifying any AggregationFunction (Not even 'None'), but the default aggregation 'SUM' is added automatically.

question 1: Why is this? How to avoid this?

Since I dont want the 'SUM' aggregation for some of the measures, I open BI studio and change one of the measure's aggregation function to 'AverageOfChildren', I get an error as soon as i do this. The measure is now underlined with the curly red (indicating an error). The error msg displayed is: Cube xyz cannot be saved because:

Cube 'xyz' > Measure Group 'abc' > Measure 'def' : Aggregate function AverageOfChildren is not allowed in Standard server edition.

question 2: What is this? Why is this? how to go about further from this?

Is this really because we use the standard server edition? (I dont know which edition we use)

Thanks and Regards

Vijay R

Yes, some functionality is only available in the Enterprise SKU. The BI Studio environment uses a property to allow you to develop targeting a particular SKU (which will change the client-side validation -- the red squigglies). This allows for a visual way to design an Analysis Services database that limits its features to the standard SKU. When the project is deployed, validation is done again by the server.

In general, semi-additive measures are only available in enterprise edition (like AverageOfChildren, LastNonEmpty, FirstNonEmpty and None).

Hope that helps.

Average aggregation

Hi! experts!

I just want to use the average in our measure group.

Unfortunately, there wasn't average aggreation, except averageOfChildren.

How can I implement the average aggreation?

thanks in advance.

Hi from Brazil,

"averageOfChildren" is same "Average" agregation function.

Note: It run just over SQL Server Enterprise or developer version. Not over Standard Version

Regards!

|||

AverageOfChildren will only average over the time dimension - so a typical approach to average over fact rows is:

Create a measure with "sum" aggregation on the fact table field to be averaged

Create a measure with "count" aggregation and row binding (created by default for a new measure group)

Create a calculated measure like: "average" = "sum" / "count"