Leaves

The Leaves function compactly describes large sets of members or tuples while avoiding pre-expansion of the set before retrieval. Because large sets tend to be very sparse, only a few members contribute to the input member (have non #Missing values) and are returned. As a result, Leaves consumes less memory resources than the equivalent nonempty Descendants function call, allowing for better scalability, especially in concurrent user environments.

Members with #MISSING values are not included in the return set.

When member is on the primary hierarchy, the return set is the set of descendants at level 0 that are nonempty.

The set returned by Leaves is the set of nonempty descendants at level 0, with a few differences. For example, when member is from an alternate hierarchy, the return set contains all primary, stored, level 0 members whose values are aggregated into member's value. These contributing members may be either:

Direct descendants of member along the alternate hierarchy
Members that contribute value to a direct descendant of member by means of a shared member

In most cases, the Leaves function does not pre-expand the set prior to retrieval. Thus it requires less memory resources than the Descendants function, allowing for more scalability in dealing with large sets, especially in a high-concurrency user environment. Large sets tend to be very sparse; therefore, very few members are returned given the current point of view as defined by the MDX current member stack.

For example, a healthcare provider may have a database containing Doctor and Geography dimensions. While there may be hundreds of thousands, even millions, of doctors, only a fraction have data associated with them for a given geographic location. Leaves is ideal for queries where the set is large but is sparse at a given point of view:

Select {[Copayments]} ON COLUMNS
CrossJoin(Leaves ([Doctors]), Leaves([Santa Clara County]) ON ROWS

The Leaves function is beneficial for queries on large dimensions.

In some cases, Leaves does require pre-expansion of sets, limiting the memory savings. Pre-expansion of sets likely will occur when the input member to Leaves is:

On an Accounts dimension
On a Time dimension
On a dimension with fewer than 10,000 members

Syntax

Leaves ( member )

Parameters

member: The member for which contributing leaf members are sought

Notes

This function is applicable only to aggregate storage databases. Using Leaves() with a non aggregate-storage input member returns an error.
Leaves() is supported only for members in stored hierarchies. Using Leaves with a member in a dynamic hierarchy returns an error.
If you modify the return set of Leaves with a metadata function such as Head, Tail, or Subset, then the query is not optimized. For example, querying for half of the Leaves set reduces performance to about the same as for the nonempty Descendants function call.
Leaves() is recommended for use on large, sparse dimensions. In general, use Leaves() to optimize performance when the input set contains 10,000 members or more. For smaller, denser input sets, using the NON EMPTY keyword on an axis with CrossJoin might improve performance.

Example

The following examples are based on the Asosamp.Basic database.

Example 1 (Leaves)

The following query returns the Units (items per package) for all level 0 Personal Electronics products for which the Units data is not #MISSING:

SELECT
{Units} ON COLUMNS,
Leaves([Personal Electronics]) ON ROWS
FROM [Asosamp.Basic]

Because Leaves returns nonempty, level 0 descendants, the above query is identical to the following query:

SELECT
{Units} ON COLUMNS,
NON EMPTY Descendants([Personal Electronics], [Products].Levels(0), SELF) ON ROWS
FROM [Asosamp.Basic]

These queries return the following grid:

Table 4-106 Output Grid from MDX Example

(axis)	Items Per Package
Digital Cameras	3041
Camcorders	3830
Photo Printers	6002
Memory	23599
Other Accessories	117230
Boomboxes	10380
Radios	20009

[Handhelds] was omitted from the result set because it has a value of #MISSING for the measure Units.

Example 2 (Leaves)

For this example, a third hierarchy called [Small Items] was added to the Products dimension.

Outline excerpt with a member [Small Items] added to Products dimension. [Small Items] has children [Digital Cameras], [Camcorders], and [Handhelds/PDAs].

The following query

SELECT
{Units} ON COLUMNS,
Leaves ([Small Items]) ON ROWS
FROM [Asosamp.Basic]

Returns the the following grid:

Table 4-107 Output Grid from MDX Example

(axis)	Items Per Package
Digital Cameras	3041
Camcorders	3830
Memory	23599
Other Accessories	117230

In addition to the primary members [Digital Cameras] and [Camcorders], Leaves also returned the primary members [Memory] and [Other Accessories], because these level-0 members contributed to [Small Items] via [Handhelds/PDAs].