# MDX - Leaves function

### Question

•

Hi to all,

I have a problem with leaves function. what exactly this function gives back? Example, if I have a time dmension, with a simple Hierarchy Year-Quarter-Month-Day where day is the leaf member of the dimension, if I use leaves function with time dimension what gives me back?? all the day?? or gives me back all the attributes, so the attribute year, the attribute quarter and so on??

THe problems cames from the fact that I have to write an mdx statement that gives me back all the leaf members of the currentmember: I have found the solution descendants(currentmember,,leaves) but I need to know how the leaves function works.

Thanks.

Francesco

Monday, December 08, 2008 1:27 PM

• This article explains the difference between hierarchy and measure group leaves - Descendants() can return the former, Leaves() the latter:

#### How to work with Dimension Leaves

Dimension leaves is one of the fundamental concepts in OLAP. Dimension leaves are used in variety of situations – from defining calculations on the leaves to aggregate up, and to different UI icons for leaves vs. non-leaves. But while it may seem that this concept is simple, it is actually more complicated. Most of the confusion comes from the fact, that there are two distinct concepts, both of which are commonly called leaves:

• Hierarchy leaves
• Measure group leaves

In this article we will explain the differences between the two and discuss the Analysis Services and MDX support for both of these concepts.

...

Retrieve hierarchy leaves

The MDX function to get the hierarchy leaves is Descendants(member,,LEAVES). It can be used to get all the leaves under the current position in the hierarchy by using

Descendants(hierarchy.CurrentMember,,LEAVES)

...

Position on measure group leaves

In AS2000 there was no easy way to get to the granularity of facts in MDX automatically. The writer of MDX was supposed to know which levels were disabled (i.e. excluded from the granularity) and use <>.MEMBERS on the lowest non-disabled level. The client applications could discover the lowest non-disabled levels for each measure group (or non virtual cube in AS2000 terms) by means of looking at MDSCHEMA_MEASURES schema rowset, which contained comma separated list of lowest non disabled levels in the LEVELS_LIST column. But this was very cumbersome way, which had to be adjusted for parent child and ragged hierarchies, and I don’t know any customer who used this method.

Therefore, in AS2005, we provided easy way to get to the measure group leaves, by the means of Leaves function. Leaves function can be used in one of the two forms:

1. Leaves() – positions on the granularity of the measure group by all of the dimensions related to that measure group
2. Leaves(dim) – positions on the granularity of the measure group only in the specified dimension.

Note, that it is really exceptional case, where in MDX you can use dimension. Everywhere else, MDX accepts hierarchy. But since granularities are defined in terms of the attributes rather then hierarchies, using dimension here makes sense.

So what does Leaves function returns ? The answer is that it returns a subcube which can be used inside SCOPE statement or at the left-hand side of the assignment. It does not return a set ! Again, this makes sense, since subcubes are defined in terms of attributes, but sets are defined by hierarchies. This is one of the reasons why Leaves function is not safe to be used in the SELECT statement, or inside named sets or in other MDX expressions. While Analysis Services may convert subcube returned by Leaves function to set (by choosing attribute hierarchies corresponding to the attributes), it is not supported usage.

Monday, December 08, 2008 4:07 PM

### All replies

• This article explains the difference between hierarchy and measure group leaves - Descendants() can return the former, Leaves() the latter:

#### How to work with Dimension Leaves

Dimension leaves is one of the fundamental concepts in OLAP. Dimension leaves are used in variety of situations – from defining calculations on the leaves to aggregate up, and to different UI icons for leaves vs. non-leaves. But while it may seem that this concept is simple, it is actually more complicated. Most of the confusion comes from the fact, that there are two distinct concepts, both of which are commonly called leaves:

• Hierarchy leaves
• Measure group leaves

In this article we will explain the differences between the two and discuss the Analysis Services and MDX support for both of these concepts.

...

Retrieve hierarchy leaves

The MDX function to get the hierarchy leaves is Descendants(member,,LEAVES). It can be used to get all the leaves under the current position in the hierarchy by using

Descendants(hierarchy.CurrentMember,,LEAVES)

...

Position on measure group leaves

In AS2000 there was no easy way to get to the granularity of facts in MDX automatically. The writer of MDX was supposed to know which levels were disabled (i.e. excluded from the granularity) and use <>.MEMBERS on the lowest non-disabled level. The client applications could discover the lowest non-disabled levels for each measure group (or non virtual cube in AS2000 terms) by means of looking at MDSCHEMA_MEASURES schema rowset, which contained comma separated list of lowest non disabled levels in the LEVELS_LIST column. But this was very cumbersome way, which had to be adjusted for parent child and ragged hierarchies, and I don’t know any customer who used this method.

Therefore, in AS2005, we provided easy way to get to the measure group leaves, by the means of Leaves function. Leaves function can be used in one of the two forms:

1. Leaves() – positions on the granularity of the measure group by all of the dimensions related to that measure group
2. Leaves(dim) – positions on the granularity of the measure group only in the specified dimension.

Note, that it is really exceptional case, where in MDX you can use dimension. Everywhere else, MDX accepts hierarchy. But since granularities are defined in terms of the attributes rather then hierarchies, using dimension here makes sense.

So what does Leaves function returns ? The answer is that it returns a subcube which can be used inside SCOPE statement or at the left-hand side of the assignment. It does not return a set ! Again, this makes sense, since subcubes are defined in terms of attributes, but sets are defined by hierarchies. This is one of the reasons why Leaves function is not safe to be used in the SELECT statement, or inside named sets or in other MDX expressions. While Analysis Services may convert subcube returned by Leaves function to set (by choosing attribute hierarchies corresponding to the attributes), it is not supported usage.

Monday, December 08, 2008 4:07 PM
• Thanks a lot!

Francesco Vanin

Thursday, December 11, 2008 10:28 AM