locked
existing keyword: current context and cube context RRS feed

  • 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

Answers

  • 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 Daniel Wu 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
    FROM [Adventure Works]
    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
    FROM [Adventure Works]


    Boyan Penev --- http://www.bp-msbi.com
    • Marked as answer by Daniel Wu 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 Daniel Wu 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
     FROM [ADVENTURE WORKS] 
      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

      FROM [ADVENTURE WORKS] 

      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

      FROM [ADVENTURE WORKS] 

      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
    FROM [Adventure Works]
    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
    FROM [Adventure Works]


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