NonEmptySubset
Given an input set, the MDX NonEmptySubset function for Essbase returns a subset of that input set in which all tuples evaluate to nonempty. An optional value expression may be specified for the nonempty check.
This function can help optimize queries that are based on a large set for which the set of nonempty combinations is known to be small. NonEmptySubset reduces the size of the set in the presense of a metric; for example, you might request the nonempty subset of descendants for specific Units.
NonEmptySubset is used to reduce the size of a set before a subsequent analytical retrieval.
Syntax
NonEmptySubset (set [, value_expression [, dimension...]])Parameters
- set
-
The set to reduce
- value_expression
-
A value expression--ideally, a stored member or a simple formula. For each tuple in set, if value_expression is nonempty, the tuple is returned as part of the subset. Otherwise, it is removed.
- dimension
-
One or more (comma-separated) dimensions from which to return the non-empty subset
Notes
Value_expression, if used, should be a stored member or simple formula. If value_expression is a complex formula, the retrieval of the nonempty subset is not optimized.
Example
The following example gets the bottom 10 products in terms of Units (items per package), and then returns the CrossJoin of that set and the level 0 members (zip codes) of [Albany - NY].
WITH SET Bottom_10
AS '
BottomCount(
Leaves(Products),
10,
Units
)
'
SELECT
{Units}
ON COLUMNS,
NonEmptySubset(CrossJoin(Bottom_10, Leaves([Albany - NY])))
ON ROWS
FROM Asosamp.BasicThis query returns the following grid:
Table 4-118 Output Grid from MDX Example
| (axis) | Items Per Package |
|---|---|
| Digital Cameras,12201 | 4 |
| Camcorders,12201 | 3 |
| Photo Printers, 12201 | 2 |
| Digital Recorders, 12201 | 2 |
| Desktops,12201 | 3 |
| Digital Cameras,12212 | 5 |
| Camcorders,12212 | 2 |
| Photo Printers, 12212 | 3 |
| Flat Panel, 12212 | 1 |
| HDTV,12212 | 1 |
| Home Theater, 12212 | 1 |
| Desktops, 12212 | 2 |
| Notebooks,12212 | 1 |
| Digital Cameras,12223 | 1 |
| Camcorders,12223 | 1 |
| Photo Printers,12223 | 4 |
| HTDV,12223 | 1 |
| Notebooks,12223 | 1 |
| Camcorders,12229 | 4 |
| HDTV,12229 | 1 |
| Home Theater,12229 | 3 |
| Desktops,12229 | 1 |
| Digital Cameras,12249 | 2 |
| Photo Printers,12249 | 3 |
| Projection TVs,12249 | 1 |
| HDTV,12249 | 2 |
| Home Theater,12249 | 1 |
| Digital Recorders,12249 | 1 |
| Notebooks,12249 | 1 |
| Camcorders,12257 | 2 |
| Photo Printers,12257 | 4 |
| Projection TVs,12257 | 2 |
| HDTV,12257 | 1 |
| Home Theater,12257 | 3 |
| Digital Recorders,12257 | 1 |