none
MDX [Exists] - what happens when measure group parameter not specified

    Question

  • My exists function call should be resolved without having to refer to any measure group. I am only combining attribute hierarchies belonging to the same dimension. I would like that only auto-exists comes into play. How to make sure SSAS only uses information of the dimension and does not travers any measure group to solve the Exists function call.

    Is it sufficient not to mention the measure group (or does SSAS then use the default measure group)?

    Exists(set1, set2) VS Exists(set1, set2, "MeasureGroupName")

    Regards
    Stefan
    Wednesday, February 24, 2010 3:05 PM

Answers

  • So if I understood you well there is no way to get a result set without refering to at least one measure group? Isn't there a way to achieve this? From a performance perspective it seems to be suboptimal.


    Actually, It is my understanding that if both sets in the EXISTS parameters are from attributes in the one dimension, then SSAS uses the same internal mechanism to return the results as the auto-exists functionality and that it should be a relatively quick operation as only the dimension indexes need to be referenced.

    It is only if the two sets are from different dimensions that you need to use the third parameter. Or you can use just the first and third parameters to get members that have facts in a given measure group.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thursday, February 25, 2010 9:15 PM

All replies

  • Hi Stefan,

    From BOL:

    If the optional <Measure Group Name> is provided, the function returns tuples that exist with one or more tuples from the second set and those tuples that have associated rows in the fact table of the specified measure group.

    Measure group rows with measures containing null values contribute to Exists when the MeasureGroupName argument is specified.

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

    Also,

    Earlier in this topic, we defined cube space as the product of the members of its attribute hierarchies. The concept of auto-exists limits this cube space to those cells that actually exist. Members of an attribute hierarchy in a dimension may not exist with members of another attribute hierarchy in the same dimension.

    For example, if you have a cube that has a City attribute hierarchy, a Country attribute hierarchy, and an Internet Sales Amount measure, the space of this cube only includes those members that exist with each other. For example, if the City attribute hierarchy includes the cities New York, London, Paris, Tokyo, and Melbourne; and the Country attribute hierarchy includes the countries United States, United Kingdom, France, Japan, and Australia; then the space of the cube does not include the space (cell) at the intersection of Paris and United States.

    When querying cells that do not exist, non-existing cells return nulls; that is, they cannot contain calculations and you cannot define a calculation that writes to this space. For example, the following statement includes cells that do not exist.

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

    In other words, if you do a CROSSJOIN, it will automatically exclude non-existing tuples, so you will not have to worry about them. From the second article:

    SELECT
       [Customer].[Country].[United States] *
          [Customer].[State-Province].Members
    ON 0
    FROM [Adventure Works]
    WHERE Measures.[Internet Sales Amount]

    is an example of this functionality - it will not give you state-provinces which do not exist under USA.

    Also, the article defines cube space:

    Cube space
    Cube space is the product of the members of a cube's attribute hierarchies with the cube's measures.

    Therefore, you can just perform a crossjoin between your attribute hierarchies, and it should exclude the empty tuples without using the EXISTS function. It is useful when you would like to restrict the auto-exists functionality to only one measure group. In the opposite case, it will evaluate over all the cube measures.

    Also, a difference between using crossjoin and EXISTS is the result set - the crossjoin result will give you tuples including members from both sets, while EXISTS will result in a set consisting of tuples from the first specified set only.


    Boyan Penev --- http://www.bp-msbi.com
    Thursday, February 25, 2010 12:06 AM
  • So if I understood you well there is no way to get a result set without refering to at least one measure group? Isn't there a way to achieve this? From a performance perspective it seems to be suboptimal.

    In the case combining attributes hierarchies from the same dimension SSAS should be able to produce the result set without having to read any measure group.

    In my case I need to get back a set consisting of tuples from the first specified set only => I will use EXISTS. Another option would be to use a crossjoin() combined with an extract().

    Regards
    Stefan

    Thursday, February 25, 2010 8:32 AM
  • So if I understood you well there is no way to get a result set without refering to at least one measure group? Isn't there a way to achieve this? From a performance perspective it seems to be suboptimal.


    Actually, It is my understanding that if both sets in the EXISTS parameters are from attributes in the one dimension, then SSAS uses the same internal mechanism to return the results as the auto-exists functionality and that it should be a relatively quick operation as only the dimension indexes need to be referenced.

    It is only if the two sets are from different dimensions that you need to use the third parameter. Or you can use just the first and third parameters to get members that have facts in a given measure group.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thursday, February 25, 2010 9:15 PM