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].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.
$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
(EXISTING
})
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
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.
MEMBER [Customer].[State-Province].[a] AS
[Customer].[State-Province].&[CA]&[US]
,[Customer].[State-Province].[a]
Without EXISTING function
Scenario 5 – Attribute relationship is defined
Washington
It contains the value for Seattle only
$462,840.69
'a' shows the value for Washington
Scenario 6 – Attribute relationship is NOT defined
Seattle’s
The slicer overwrites the SET
Adding EXISTING function
Scenario 7 – Attribute relationship is defined with using EXISTING function
Seattle's value
$147,078.52
The slicer overwrites the SET.
‘a’ shows the Washington's value
Scenario 8 – No attribute relationship. Using EXISTING function
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