Many function names in MDX are very family-friendly. Children, parent, ancestors, descendants, ascendants, are all terms we often use in telling family stories. Except ascendants. I never really understand what this word means until I ran into this word in MDX.

Ancestors VS. Ascendants

I checked the www.thefreedictionary.com, and here are the definitions:

ascendant – someone from whom you are descended (but usually more remote than a grandparent).
ancestor – a person from whom another is directly descended, esp. someone more distant than a grandparent.

These two definitions are very close. Nothing really stands out for me to tell the difference.

Fortunately, in MDX, the difference between them is very easy to tell and also easy to demonstrate.

Ascendants() – returns all of the ancestors of a member in a set

That is, from the member itself up to the top of the member’s hierarchy. This is easy to see in this query.

SELECT
   Measures.[Reseller Order Count] ON COLUMNS,
   Ascendants(
        [Sales Territory].[Sales Territory].[Northwest]
   ) ON ROWS
FROM
   [Adventure Works]

In the [Sales Territory] dimension, Northwest is part of United States, which is part of North America. [All Sales Territories] is the root member and sits on top of all other members.  

With one Ascendants() function, we get all the ancestors, including the member [Northwest]itself and the most top level member, the [All Sales Territories].

Plural S returns set; singular returns member

Notice that the above query returns a set back. This set contains all the members in [Northwest]‘s family tree.

With the same logic, we can make a good guess that the function Ancestors() will return a set too, while the function Ancestor() will only return a member.

This logical thinking actually applies to other functions as well. Children() will return a set, while FirstChild() returns a member.

Ancestor() VS. Ancestors()

The word Ancestors is a plural, so you might think that it will return all the upward members in the hierarchy. It is true, sort of. It can return all the upward members in the hierarchy, but only one at a time.     

The following query can demonstrate this:

SELECT
   Measures.[Reseller Order Count] ON COLUMNS,
   Ancestors(
        [Sales Territory].[Sales Territory].[Northwest],
        1
   ) ON ROWS
FROM
   [Adventure Works]

We can only see United States because Ancestors() function can only return one specific ancestor at the specified level.

If you change the level number to 2, you should see only North America.

Again, the plural only means that it is returning a set; a one-member only set in this case.

Ancestor() and Ancestors() – they return one specific ascendant member at a time, at a specific level

If you change the above query to use the singular Ancestor() function, you will get the same result, only that it is a member, not a set.