Aggregations in Analysis Services STANDARD edition

I have fields in my cube where SUM is not a good aggregation (rate fields for example). I thought I should just set them to No aggregation, but it tells me that semi-additive aggregations are not available in Standard Edition (?). So, two questions...

1) How do I avoid representing these fields incorrectly in Standard Edition? What aggregation should they be set to? I don't want users to point Excel to them and get this big meaningless number!

2) How/Where do I produce Averages, etc? (I'm thinking that I can only do this in the Report or Excel - that it can't be done in the cube, is that right?)

Thanks for you help...

- Jim

You're correct that Standard Edition does not support semi-additive aggregations. These functions include ByAccount, AverageOfChildren, LastChild, FirstChild, LastNonEmpty, and FirstNonEmpty. If these are the right aggregations to use, you might be able to set up a calculated member that gives you something similar by going to the leaf level and then controlling the aggregation to the level your interested in. If you do this, make the measure you are "replacing" not visible to your users.

Regarding averages, store a SUM and a COUNT measure and do a ratio in a calculated member.

Good luck,
Bryan

|||

If you don't have enterprise edition, then this workaround might help:

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/08/3439.aspx

or

http://lorentsnv.spaces.live.com/blog/cns!235C78448ABCFC07!134.entry

Aggregate Function is not allowed in Standard Server Edition

I'm trying to use None as an aggregate function on a measure but I get an error saying "Aggregate Function None is not allowed in the Standard server edition". The funny thing is that I'm running the Developer Edition.

Is the Developer Edition the same as the Standard edition? Has anyone else run into this?

This recent OLAP Newsgroup thread may help:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/36855351fd3096b2/c7107957b1da7a13?#c7107957b1da7a13

>>

Aggregation function None is not allowed in Standard edition

From:		Chris Webb - view profile
Date:		Fri, Oct 20 2006 12:24 pm
Email:		Chris Webb <onlyforpostingtonewsgro...@.crossjoin.co.uk>
Groups:		microsoft.public.sqlserver.olap

Not yet rated Rating:
show options

Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author

Hi Roger,

Have you set the Deployment Server Edition property appropriately on your project?

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!856.entry

HTH,

Chris
--
Chris Webb, MVP
Analysis Services and MDX Consultancy: http://www.crossjoin.co.uk
Blog: http://cwebbbi.spaces.live.com/

- Hide quoted text -

- Show quoted text -

"Roger" wrote:
> On my development machine, I initially installed SQL Server 2005 standard
> edition. When I attempted to add a measure with Aggregation function of None,
> I got the error message when processing - "Aggregation Function None is not
> supported in Standard Edition". Obviously I cannot install the enterprise
> edition on Win XP. But, I install the developer edition. But, when I go to
> process the cube again, I still get the same error message. Any ideas how to
> tell VS 2005 to recognize that I have the developer edition and not the
> standard edition.

Reply

3 From: Roger - view profile Date: Fri, Oct 20 2006 12:38 pm Email: Roger <R...@.discussions.microsoft.com> Groups: microsoft.public.sqlserver.olap Not yet rated Rating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author Thank god!!!!!! That worked!!!

>>