Returns the set of level 0 (leaf) members that contribute to the value of the specified member.
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 )
Parameter | Description |
---|---|
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.Sample database.
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.Sample]
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.Sample]
These queries return the following grid:
(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.
For this example, a third hierarchy called [Small Items] was added to the Products dimension.
The following query
SELECT {Units} ON COLUMNS, Leaves ([Small Items]) ON ROWS FROM [Asosamp.Sample]
Returns the the following grid:
(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].