What does MDX Aggregate() do with a single argument

mdxssas

I understand how to use the MDX Aggregate() and Sum() functions, and the differences between them.

(One interesting one is that the Sum of a measure defined at a higher level in a hierarchy over that level's Children multiplies the measure by the number of children – whereas Aggregate "correctly" returns just the value defined at the higher level).

The documented syntax on MSDN is:

Aggregate(Set_Expression [ ,Numeric_Expression ])

I've always used it with both arguments. But what does Aggregate do when only the set_expression argument is provided? The documentation (again from MSDN) is pretty obscure:

If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure.

I tried it in an MDX query like this:

WITH MEMBER WeekSummedTotal AS
Aggregate([Days].[WeeksAndDays].CurrentMember.Children)
SELECT 
{Measures.ThingoCount,Measures.WeekTotal,Measures.WeekSummedTotal} ON 0,
[Days].[WeeksAndDays].[WeekName] ON 1
FROM DateGRoupingTest

What would this do? Would Aggregate aggregate the cube's default measure over the set? Or the set Measures.Members? Or the set of other measures specified on the 0 axis?

None of these! The query runs and returns results, but the calcd measure WeekSumTotal shows #Error, with a completely nonsensical error:

Aggregate functions cannot be used on calculated members in the measures dimension

Now this is true, but completely irrelevant. None of the other measures in the query is calculated, and in fact the cube doesn't have any calculated members. So what is Aggregate() actually trying to do here? Is this error message (again, in MDX!) completely misleading?

ADDITION: @whytheq in the answer below suggested creating the calculated measure using Aggregate, but creating it on a spare dimension hierarchy rather than in the Measures dimension. This works, but only if the cross-join with the [All] member of the selected "any old…" dimension is included.
Creating the measure there also makes it impossible to put the two (base) measures and the calculated measure on the same axis. If I try to do this:

{Measures.ThingoCount,Measures.WeekTotal,[Ages].[Age Key].WeekSummedTotal} ON 0,

I get the deeply-unhelpful error message:

Members, tuples or sets must use the same hierarchies in the  function.

which, I think, translates to "I can't make a set using the , (UNION) function between members of Measures and members of [Ages].[Age Key] because they're members of different dimensions".

My conclusion, thanks to your informative answers, is that Aggregate() with a single argument is a tricky beast; I wonder why it was designed with the second argument optional?

I've also noted that trying to create my calculated member on my Ages dimension (only one hierarchy, only one attribute) gives me the misleading error message:

The 'Ages' dimension contains more than one hierarchy, therefore
the hierarchy must be explicitly specified.

unless I explicitly specify the hierarchy. MDX has so much potential, but the learning curve would be that much gentler if MS had put more effort into making it feed back errors properly.

Best Answer

What would this do? Would Aggregate aggregate the cube's default measure over the set? Or the set Measures.Members? Or the set of other measures specified on the 0 axis?

Aggregate function aggregates the set over the current measure for Measures dimension. And a measure is "current" if it is in scope. If a measure is not in scope, the default member from measures dimension is considered for aggregation.

A measure can be added to scope in many ways like

Having the measure on axes

with member [Customer].[Customer].abc as
aggregate([Customer].[Customer].members)


select [Customer].[Customer].abc on 0,
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]}  on 1
from [Adventure Works]

In the above example the member abc was calcualted twice, once for each measure.

Using Subcube

with member [Customer].[Customer].abc as
aggregate([Customer].[Customer].members)


select [Customer].[Customer].abc on 0
from (select {[Measures].[Internet Sales Amount] } on 0 from [Adventure Works])

Having the measure in definition

with member [Customer].[Customer].abc as
aggregate([Customer].[Customer].members, [Measures].[Internet Sales Amount])


select [Customer].[Customer].abc on 0
from [Adventure Works]

In Where clause

with member [Customer].[Customer].abc as
aggregate([Customer].[Customer].members)


select [Customer].[Customer].abc on 0
from [Adventure Works]
where [Measures].[Internet Sales Amount]

As suggested by whytheq, have the member on some other dimension-hierarchy combo. Otherwise, the aggregate function would probably lead to the calculated member self-referencing itself.

Related Topic