# existing keyword: current context and cube context

• ### Question

• http://msdn.microsoft.com/en-us/library/ms145541.aspx

said the existing keyword change the context from cube context to current context. How to understand cube context?  Does cube context mean ignore all other dimensions even they appear in the axis/where?

Thursday, December 9, 2010 9:00 AM

• Hi Daniel,

Have a look at this post:

http://ssas-musings.blogspot.com/2009/11/existing-keyword.html

It shows an example of what you can do with Existing.

In brief, cube context does mean we are ignoring the current coordinate and we are evaluating over the whole cube. As in the example above, if you want the count of all cities by requesting all members in the city level of a geography hierarchy, by default you will get the count of all members there regardless of whether you have specified Country on an axis or in the WHERE clause. If you apply Existing, the set will get counted for each coordinate resulting in counts of cities per country.

Boyan Penev --- http://www.bp-msbi.com
• Marked as answer by Friday, December 10, 2010 2:54 AM
Thursday, December 9, 2010 1:18 PM
• Not quite :)

Named sets are evaluated in the context they are created in - which includes the slicer axis (WHERE). Maybe the best way to describe what I mean is examining these two queries:

WITH
SET a AS
[Geography].[City].[City]
SET b AS
EXISTING ([Geography].[City].[City])
MEMBER c_a AS
COUNT(a)
MEMBER c_b AS
COUNT(b)
MEMBER c_c AS
COUNT([Geography].[City].[City])
MEMBER c_d AS
COUNT(EXISTING ([Geography].[City].[City]))
SELECT
{c_a,c_b,c_c,c_d} ON 0,
{[Geography].[Country].[Country]} ON 1
WHERE [Geography].[Geography].&[Australia]

and (same but with no WHERE clause):

WITH
SET a AS
[Geography].[City].[City]
SET b AS
EXISTING ([Geography].[City].[City])
MEMBER c_a AS
COUNT(a)
MEMBER c_b AS
COUNT(b)
MEMBER c_c AS
COUNT([Geography].[City].[City])
MEMBER c_d AS
COUNT(EXISTING ([Geography].[City].[City]))
SELECT
{c_a,c_b,c_c,c_d} ON 0,
{[Geography].[Country].[Country]} ON 1

Boyan Penev --- http://www.bp-msbi.com
• Marked as answer by Saturday, December 11, 2010 10:15 AM
Friday, December 10, 2010 7:58 AM

### All replies

• Hi Daniel,

Have a look at this post:

http://ssas-musings.blogspot.com/2009/11/existing-keyword.html

It shows an example of what you can do with Existing.

In brief, cube context does mean we are ignoring the current coordinate and we are evaluating over the whole cube. As in the example above, if you want the count of all cities by requesting all members in the city level of a geography hierarchy, by default you will get the count of all members there regardless of whether you have specified Country on an axis or in the WHERE clause. If you apply Existing, the set will get counted for each coordinate resulting in counts of cities per country.

Boyan Penev --- http://www.bp-msbi.com
• Marked as answer by Friday, December 10, 2010 2:54 AM
Thursday, December 9, 2010 1:18 PM
• Thanks, it even doesn't consider the slicer. it still return 587

WITH
MEMBER [CITY_COUNT] AS COUNT( [GEOGRAPHY].[CITY].[CITY].MEMBERS)
SELECT {[CITY_COUNT]} ON 0
,[GEOGRAPHY].[GEOGRAPHY].[COUNTRY].MEMBERS ON 1
where [Geography].[City].&[San Francisco]&[CA]

Friday, December 10, 2010 2:56 AM
• Try EXISTING in front of the set. As I said:

"by default you will get the count of all members there regardless of whether you have specified Country on an axis or in the WHERE clause"

Boyan Penev --- http://www.bp-msbi.com
Friday, December 10, 2010 3:09 AM
• IN the book of "SQL server 2008 MDX step by setp", it said:

Working with Sets in expressions: when set employed in an expression, sets are NOT affected by current cell’s context.

Doesn't that mean for a standalone set, it will be affected by current context? In my second test, it only shows one cell, so based on that can we say standalone set is affected by current context?

1: set in expression

WITH member

[CITY_COUNT] AS count( [GEOGRAPHY].[CITY].[CITY].MEMBERS)

SELECT {[CITY_COUNT]} ON 0

where [Geography].[Postal Code].&[03276]&[Tilton]&[NH]

2: standalone set (not in expression)

WITH set

[CITYS] AS ( [GEOGRAPHY].[CITY].[CITY].MEMBERS)

SELECT {[CITYS]} ON 0

where [Geography].[Postal Code].&[03276]&[Tilton]&[NH]

Friday, December 10, 2010 5:05 AM
• The second set is a named set. The book is talking about sets in general - not about named sets specifically, imho.

The second (named) set contains one item because it gets affected by the slicer context at the time of its creation.

Boyan Penev --- http://www.bp-msbi.com
Friday, December 10, 2010 5:37 AM
• So we can say:

1: named set is evaluated in the current context

2: set inside calculated member is evaluated in the CUBE context

Friday, December 10, 2010 6:55 AM
• Not quite :)

Named sets are evaluated in the context they are created in - which includes the slicer axis (WHERE). Maybe the best way to describe what I mean is examining these two queries:

WITH
SET a AS
[Geography].[City].[City]
SET b AS
EXISTING ([Geography].[City].[City])
MEMBER c_a AS
COUNT(a)
MEMBER c_b AS
COUNT(b)
MEMBER c_c AS
COUNT([Geography].[City].[City])
MEMBER c_d AS
COUNT(EXISTING ([Geography].[City].[City]))
SELECT
{c_a,c_b,c_c,c_d} ON 0,
{[Geography].[Country].[Country]} ON 1
WHERE [Geography].[Geography].&[Australia]

and (same but with no WHERE clause):

WITH
SET a AS
[Geography].[City].[City]
SET b AS
EXISTING ([Geography].[City].[City])
MEMBER c_a AS
COUNT(a)
MEMBER c_b AS
COUNT(b)
MEMBER c_c AS
COUNT([Geography].[City].[City])
MEMBER c_d AS
COUNT(EXISTING ([Geography].[City].[City]))
SELECT
{c_a,c_b,c_c,c_d} ON 0,
{[Geography].[Country].[Country]} ON 1