Dienstag, 17. November 2009

EXISTING keyword

As the documentation states the EXISTING keyword forces a specified set to be evaluated within the current context instead of being evaluated within the context of the cube that contains the members of the set. Mosha Pasumasky declares "that Exists function and EXISTING operator are generalization of hierarchy related functions such as Descendants. Indeed, the process of building an hierarchy is nothing more then applying Exists between attributes which build individual levels of that hierarchy." So EXISTING shows only elements that are actually relevant in the given context.

If we are looking for the number of cities in each country in the Adventure Works Cube and use the following query:

WITH
MEMBER [CITY_COUNT] AS COUNT([GEOGRAPHY].[CITY].[CITY].MEMBERS)
SELECT {[CITY_COUNT]} ON 0
,[GEOGRAPHY].[GEOGRAPHY].[COUNTRY].MEMBERS ON 1
FROM [ADVENTURE WORKS]

we do not get the intended result:

CITY_COUNT
Australia 587
Canada 587
France 587
Germany 587
United Kingdom 587
United States 587

That's the total count of cities for every country - apparently the evaluation took place in the context of the cube and not in the current context. With EXISTING we get what we want:

WITH
MEMBER [CITY_COUNT] AS COUNT(EXISTING([GEOGRAPHY].[CITY].[CITY].MEMBERS))
SELECT {[CITY_COUNT]} ON 0
,[GEOGRAPHY].[GEOGRAPHY].[COUNTRY].MEMBERS ON 1
FROM [ADVENTURE WORKS]

provides the result:

CITY_COUNT
Australia 40
Canada 50
France 36
Germany 51
United Kingdom 35
United States 375

As already mentioned here EXISTING can also be used to solve some problems with multiselects.

In his Blog Chris Webb wrote about the performance of EXISTING in combination with NONEMPTY and stated "if you are using NonEmpty and Existing together, do the NonEmpty first and then the Existing" - but admitted that he has no sound explanation for the observed effect.

Keine Kommentare:

Kommentar veröffentlichen