What does MDX Aggregate() do with a single argument?

Question

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.

Why can't you just have it as Sum([Days].[WeeksAndDays].CurrentMember.Children, [Measures].SomeMeasure)? Problem solved. — SouravA
Of course, that's much easier and does give me the result I want (though for my purposes, Aggregate is better than Sum). I'm just trying to understand MDX better: I wondered what this "no second argument" usage of Sum/Aggregate actually did, experimented, and found the results confusing. But I think you and @whytheq have explained what's happening. — SebTHU
@SebTHU this is a very basic mdx error Members, tuples or sets must use the same hierarchies in the function.: if you create a set say using curly braces {x,y,z} then x, y and z need to be of the same dimensionality - or you get that error. A similar basic error is the opposite of that one - if you create a tuple using normal braces (x,y,z) then x,y and z must be from different hieraries. — whytheq
@SebTHU I wonder why it was designed with the second argument optional? - it is easy to change your query so you can see when aggregate is very useful - I'll add an edit to my answer. — whytheq
Here's an example of use case for one parameter. But note using it with way breaks the dimension hierarchy, usually when I do this I cross join the members from the hierarchy: stackoverflow.com/q/17682164/84206 — AaronLS

whytheq whytheq · Accepted Answer · 2015-08-19T11:39:04

Taking this section of the MSDN definition:

...this function aggregates each measure within the current query context ...

each measure in the context of your script is the following:

{Measures.ThingoCount,Measures.WeekTotal,Measures.WeekSummedTotal}

Now Measures.WeekSummedTotal is a calculated members in the measures dimension - hence the error.

I'd imagine something like the following would function ok, where you use Aggregate to create a member in a dimension other than Measures?:

WITH 
  MEMBER [SomeSpareDim].[SomeSpareHier].WeekSummedTotal AS 
    Aggregate
    (
      [Days].[WeeksAndDays].CurrentMember.Children * [SomeDim].[SomeHier].[All]
    ) 
SELECT 
  [SomeSpareDim].[SomeSpareHier].WeekSummedTotal ON 0
 ,[Days].[WeeksAndDays].[WeekName] ON 1
FROM DateGRoupingTest;

The above can be changed to show Aggregate being very useful:

WITH 
  MEMBER [Days].[WeeksAndDays].[Last3Weeks] AS 
    Aggregate
    (
      {
       [Days].[WeeksAndDays].[Weekx]
      ,[Days].[WeeksAndDays].[Weeky]
      ,[Days].[WeeksAndDays].[Weekz]
      }
    ) 
SELECT 
  {Measures.ThingoCount,Measures.WeekTotal} ON 0
 ,{
     //<< the following custom aggregated member will work for any measure, that is ON 0, via Aggregate
     //it can be mixed up with the normal members of the same hierarchy like the following
    [Days].[WeeksAndDays].[Last3Weeks]  
   ,[Days].[WeeksAndDays].[WeekName].members
  } ON 1
FROM DateGRoupingTest;

What does MDX Aggregate() do with a single argument?

2 Answers