MDX: SET overwrite and the use of EXISTING function

[SQLPFE]

First published on MSDN on Feb 11, 2013

The SET overwrite is largely impacted by the attribute relationships and the behavior does depend on whether the attribute is below or above or no relationship in the relationship chains.

For illustrate the point, I run a test base on the following test environment:

• SQL Server 2012 Analysis Services Multidimensional Model
• Database: AdventureWorksDW2012Multidimensional-EE ( Adventure Works for SQL Server 2012 ).
• Storage Mode: MOLAP
• Ref: EXISTING Keyword (MDX)

Case 1:  The SET is in [City] level. The slicer is in [State-Province] level. [State-Province] is 1 to many to [City]. [City] is in the below the relationship chain.

Here is the example query

WITH
  MEMBER [customer].[city].a AS
    Aggregate
    (
      {
        [Customer].[City].&[Redwood City]&[CA]
       ,[Customer].[City].&[Spokane]&[WA]
       ,[Customer].[City].&[Seattle]&[WA]
      }
    )
SELECT
  [Measures].[Internet Sales Amount] ON 0
 ,{
    [Customer].[City].&[Redwood City]&[CA]
   ,[Customer].[City].&[Spokane]&[WA]
   ,[Customer].[City].&[Seattle]&[WA]
   ,[customer].[city].a
  } ON 1
FROM [Adventure Works]
WHERE
  (
    [Date].[Calendar].[Month].&[2008]&[4]
   ,[Customer].[State-Province].&[WA]&[US]
  ); 

Scenario 1: Attribute Relationship is defined

If we keep the existing attribute relationship defined between [City] and [State-Province] in the sample [AdventureWorksDW2012Multidimensional-EE] database, we get the following result

	Internet Sales Amount
Spokane Seattle	$6,275.72 $3,527.85
a	$18,890.50

The SET ignores the slicer in WHERE clause. The value of "a" includes the value from Redwood City, CA

Scenario 2: Attribute Relationship is NOT defined

If we remove the attribute relationship between [City] and [State-Province] from the dimension, the concept of “auto exist” kicks in. The aggregation value of a does not include Redwood City, CA anymore.

	Internet Sales Amount
Spokane Seattle	$6,275.72 $3,527.85
a	$9,803.57

The slicer in the WHERE clause overwrites the SET. The value of "a" does not includes Redwood City, CA

The use of existing function

Scenario 3: Attribute Relationship is defined

WITH
  MEMBER [customer].[city].a AS
    Aggregate
    (
      (EXISTING
        {
          [Customer].[City].&[Redwood City]&[CA]
         ,[Customer].[City].&[Spokane]&[WA]
         ,[Customer].[City].&[Seattle]&[WA]
        })
    )
SELECT
  [Measures].[Internet Sales Amount] ON 0
 ,{
    [Customer].[City].&[Redwood City]&[CA]
   ,[Customer].[City].&[Spokane]&[WA]
   ,[Customer].[City].&[Seattle]&[WA]
   ,[customer].[city].a
  } ON 1
FROM [Adventure Works]
WHERE
  (
    [Date].[Calendar].[Month].&[2008]&[4]
   ,[Customer].[State-Province].&[WA]&[US]
  ); 

	Internet Sales Amount
Spokane Seattle	$6,275.72 $3,527.85
a	$9,803.57

With EXISTING function, the Slicer in the WHERE clause overwrites the SET

Scenario 4:

The EXISTING function has no impact on the result if no attribute relationships are defined.

You get the same result just as without using the Existing function

	Internet Sales Amount
Spokane Seattle	$6,275.72 $3,527.85
a	$9,803.57

Case 2: The SET is in [State-Province] level. The slicer is in [City] level. [State-Province] is 1 to many to [City]. [State-Province] is in the above the relationship chain.

WITH
  MEMBER [Customer].[State-Province].[a] AS
    Aggregate
    (
      {
        [Customer].[State-Province].&[CA]&[US]
       ,[Customer].[State-Province].&[WA]&[US]
      }
    )
SELECT
  [Measures].[Internet Sales Amount] ON 0
 ,{
    [Customer].[State-Province].&[CA]&[US]
   ,[Customer].[State-Province].&[WA]&[US]
   ,[Customer].[State-Province].[a]
  } ON 1
FROM [Adventure Works]
WHERE
  (
    [Date].[Calendar].[Month].&[2008]&[4]
   ,[Customer].[City].&[Seattle]&[WA]
  ); 

	Internet Sales Amount
Washington	$3,527.85	It contains the value for Seattle only
a	$462,840.69	'a' shows the value for Washington

Scenario 6 – Attribute relationship is NOT defined

	Internet Sales Amount
Washington	$3,527.85	Seattle's
a	$3,527.85	The slicer overwrites the SET

Adding EXISTING function

WITH
  MEMBER [Customer].[State-Province].[a] AS
    Aggregate
    (
      (EXISTING
        {
          [Customer].[State-Province].&[CA]&[US]
         ,[Customer].[State-Province].&[WA]&[US]
        })
    )
SELECT
  [Measures].[Internet Sales Amount] ON 0
,{
    [Customer].[State-Province].&[CA]&[US]
   ,[Customer].[State-Province].&[WA]&[US]
   ,[Customer].[State-Province].[a]
  } ON 1
FROM [Adventure Works]
WHERE
  (
    [Date].[Calendar].[Month].&[2008]&[4]
   ,[Customer].[City].&[Seattle]&[WA]
  ); 

Scenario 7 – Attribute relationship is defined with using EXISTING function

	Internet Sales Amount
Washington	$3,527.85	Seattle's value
a	$147,078.52	The slicer overwrites the SET.‘a’ shows the Washington's value

Scenario 8 – No attribute relationship. Using EXISTING function

	Internet Sales Amount
Washington	$3,527.85	Seattle's value
a	$3,527.85	Seattle's value

Remark

Based on the testing, if you have attribute relationship defined (you should for best practices and performance reasons), we should use EXISTING function to force a specified set to be evaluated within the current context.

C S John Lam | Premier Field Engineer - SQL Business Intelligence