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:

	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount
	CY 2005	CY 2006	CY 2007	CY 2008
All Products	$3,266,373.66	$6,530,343.53	$9,791,060.30	$9,770,899.74
Accessories	(null)	(null)	$293,709.71	$407,050.25
Bikes	$3,266,373.66	$6,530,343.53	$9,359,102.62	$9,162,324.85

 

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:

	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount
	CY 2005	CY 2006	CY 2007	CY 2008
All Products	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Accessories	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Bikes	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Clothing	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Components	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10

 

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.

	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount
	CY 2005	CY 2006	CY 2007	CY 2008
All Products	$3,266,373.66	$6,530,343.53	$9,791,060.30	$9,770,899.74
Accessories	(null)	(null)	$293,709.71	$407,050.25
Bikes	$3,266,373.66	$6,530,343.53	$9,359,102.62	$9,162,324.85

 

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

 

	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount
	CY 2005	CY 2006	CY 2007	CY 2008
x	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10

 

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.

	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount	Internet Sales Amount
	CY 2005	CY 2006	CY 2007	CY 2008
All Products	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Accessories	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Bikes	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Clothing	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10
Components	$3,266,373.66	$6,530,343.53	$9,652,812.33	$9,569,375.10

 

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