MDX Optimization Properties

Optimization properties in MDX can improve the performance of Essbase formulas and calculated members, as well as the performance of MDX queries that rely on these. Learn about using NONEMPTYMEMBER and NONEMPTYTUPLE for querying large sets while skipping empty values.

Optimization properties are applicable to outline members with formulas and calculated members only. Stored members are not associated with these properties.

The NONEMPTYMEMBER and NONEMPTYTUPLE properties enable MDX in Essbase to query on large sets of members or tuples while skipping formula execution on non-contributing values that contain only #MISSING data.

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, the use of NONEMPTYMEMBER and NONEMPTYTUPLE in calculated members and formulas conserves memory resources, allowing for better scalability, especially in concurrent user environments.

NONEMPTYMEMBER

NONEMPTYMEMBER nonempty_member_list

where nonempty_member_list is one or more comma-separated member names or calculated member names from the same dimension as the formula or calculated member.

Use a single NONEMPTYMEMBER property clause at the beginning of a calculated member or formula expression to indicate to Essbase that the value of the formula or calculated member is empty when any of the members specified in nonempty_member_list are empty.

NONEMPTYTUPLE

NONEMPTYTUPLE "("nonempty_member_list")"

where nonempty_member_list is one or more comma-separated member names or calculated member names, each from different dimensions.

If any formula-dependent dimension is omitted from nonempty_member_list, it may lead to incorrect results, as not all dimensions will be added to the formula cache.

Use a single NONEMPTYTUPLE property clause at the beginning of a calculated member or formula expression to indicate to Essbase that the value of the formula or calculated member is empty when the cell value at the tuple given in nonempty_member_list is empty.

Example

The following query calculates a member [3 Month Units] that represents the sum of Units (items per package) for the current month and the previous two months, where Units data is not missing.

The calculated member [3 Month Units] calculates Units shipped for last three months. If the units shipped for [MTD] (units shipped in a year) is empty, it follows that Units data is empty for all months in the Year; therefore, the sum of Units shipped for last three months is also empty. Because the row axis in the query is very large and sparse, the NONEMPTYTUPLE property would significantly increase the performance of the query in this case.

WITH MEMBER [Measures].[3 Month Units] AS
'
 NONEMPTYTUPLE ([Units], [MTD])
  Sum(
       {
        ClosingPeriod(Time.Generations(5), Time.CurrentMember),
        Time.CurrentMember.Lag(1),
        Time.CurrentMember.Lag(2)
        }, 
        Units
   )
'
SELECT
 {Units, [3 Month Units]} ON COLUMNS,
 NON EMPTY 
  CrossJoin(
     Stores.Levels(0).Members,
     [Store Manager].Children
  )
ON ROWS
FROM Asosamp.Basic
WHERE (Mar);

This query returns the following grid (results truncated):

Table 4-20 Output Grid from MDX Example

(axis)	Items Per Package	3 Month Units
(017589, Carrie)	610	1808
(020408, Debra)	584	1778
(020486, Kalluri)	551	1670
(047108, Kimberley)	593	1723
(051273, Madhukar)	541	1642
(056098, Melisse)	607	1750
...	...	...