Optimization properties can improve the performance of formulas and calculated members, as well as the performance of queries that rely on them.
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 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 "("nonempty_member_list")"
where nonempty_member_list is one or more comma-separated member names or calculated member names, each from different dimensions.
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.
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.Sample
WHERE (Mar);
This query returns the following grid (results truncated):
(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 |
... | ... | ... |