MDX: SET overwrite and the use of EXISTING function
The SET overwrite is largely impacted by the attribute relationships and thebehaviordoes 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
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