Slice on a Calculated Member that uses the Aggregate function

Applies to: SQL Server 2005 Analysis Services, SQL Server 2008 Analysis Services, SQL Server 2008 R2 Analysis Services, SQL Server 2012 Analysis Services Multidimensional Model

A user reported seeing two different behaviors by running two slightly different MDXs statement with a where clause on a calculated member that used the Aggregation function. In this post, we will try to look at the 2 queries in question and explain the difference in behavior with the Adventure works  Sample DB. 

Query 1

WITH

  MEMBER [Product].[Category].[x] AS

    Aggregate({[Product].[Category].[Bikes],[Product].[Category].[Accessories]})

SELECT

  (

    [Measures].[Internet Sales Amount]

   ,{

      [Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]

    }

  ) ON 0

 ,{

    [Product].[Product Categories].[All Products]

   ,[Product].[Product Categories].[Category].ALLMEMBERS

  } ON 1

FROM [Adventure Works]

WHERE

  [Product].[Category].[x];

Result:

Query 1 produced a meaningful and expected results.

Query 2

WITH

  MEMBER [Product].[Category].[x] AS

    Aggregate(existing{[Product].[Category].[Bikes],[Product].[Category].[Accessories]})

SELECT

  (

    [Measures].[Internet Sales Amount]

   ,{

      [Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]

    }

  ) ON 0

 ,{

    [Product].[Product Categories].[All Products]

   ,[Product].[Product Categories].[Category].ALLMEMBERS

  } ON 1

FROM [Adventure Works]

WHERE

  [Product].[Category].[x];

Result:

In query 2, the only thing the user added was the “existing” function (see the yellow highlight above).The behavior of the query 2 was dramatically different. The result values were repeated for each product category in any given year.

Explanation

If a calculated member expression was “aggregate (<static set>)”, it would act like replacing the where clause with that static set.

With member

[Product].[Category].[c1] as aggregate (A, B, C)

Select

X,

Y

From <cube>

Where [Product].[Category].[c1]

It acted like:

Select

X,

Y

From <cube>

Where ({A, B, C})

In Query 1, the set {[Product].[Category].[Bikes],[Product].[Category].[Accessories]} was static. The query behaved like this:

SELECT

  (

    [Measures].[Internet Sales Amount]

   ,{

      [Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]

    }

  ) ON 0

 ,{

    [Product].[Product Categories].[All Products]

   ,[Product].[Product Categories].[Category].ALLMEMBERS

  } ON 1

FROM [Adventure Works]

WHERE

({[Product].[Category].[Bikes],[Product].[Category].[Accessories]})

And it produced the same result as we saw in Query 1.

What if the set was not static? The replacement did not take place if the set was not static and the set would get computed into numeric values as shown below.

WITH

  MEMBER [Product].[Category].[x] AS

    Aggregate(existing{[Product].[Category].[Bikes],[Product].[Category].[Accessories]})

SELECT

  (

  [Measures].[Internet Sales Amount]

   ,{

      [Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]

    }

  ) ON 0

 ,[Product].[Category].[x] ON 1

FROM [Adventure Works];

The Query 2, adding “Existing” to the set made the set “NOT static”.  The calculated member got evaluated into numeric values just like above and then populated the entire result with these values.

C S John Lam| SQL Business Intelligence | Premier Field Engineering