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 (Download Center: Microsoft® SQL Server® 2012 Evaluation)

·        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

$6,275.72

Seattle

$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

$6,275.72

Seattle

$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

$6,275.72

Seattle

$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

$6,275.72

 
 

Seattle

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

  );

 

Without EXISTING function

Scenario 5 – Attribute relationship is defined

 

           
 

 

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