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
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)
$293,709.71
$407,050.25
Bikes
$9,359,102.62
$9,162,324.85
Query 1 produced a meaningful and expected results.
Query 2
Aggregate(existing{[Product].[Category].[Bikes],[Product].[Category].[Accessories]})
$9,652,812.33
$9,569,375.10
Clothing
Components
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:
Where ({A, B, C})
In Query 1, the set {[Product].[Category].[Bikes],[Product].[Category].[Accessories]} was static. The query behaved like this:
({[Product].[Category].[Bikes],[Product].[Category].[Accessories]})
And it produced the same result as we saw in Query 1.
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.
,[Product].[Category].[x] ON 1
FROM [Adventure Works];
x
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.