Measuring NONEMPTYMEMBER/TUPLE in MDX Part 1

Welcome to MDX Monday. There is a page in the Essbase Tech Ref entitled "MDX Optimization Properties". It describes the use of NONEMTPYMEMBER and NONEMPTYTUPLE in MDX. Supposedly these keywords will speed up MDX queries and member formulas (in ASO) by eliminating the inclusion of missing sets from the formula execution. There is anecdotal evidence that this does speed things up. It seems like it's very difficult to pin down when it does help things. I'm on a mission to figure this out so if you've got examples of it helping, please leave a comment letting me know about it.

I thought I'd start out with the ASOsamp example given in the Tech Ref. I tested out four scenarios.

The Tests

1. Run the example as is, including the NONEMPTYTUPLE statement.
2. Remove the NONEMPTYTUPLE statement.
3. Change the NONEMPTYTUPLE ([Units],[MTD]) to NONEMPTYMEMBER [Units]
4. Change the NONEMPTYTUPLE ([Units],[MTD]) to NONEMPTYMEMBER [MTD]

The query time results are below. Each returned the exact same set.

The Results

1. 0.32s
2. 9.64s
3. 0.21s
4. 9.63s

I got the following warning message for the 4th test. This makes sense because the Sum() function is using [Units] as the value it is summing up.
Warning: Non empty member directive [MTD] for member [3 Month Units] will be ignored since it is from a different dimension.

So the "non-empty directive", as the Essbase application log puts it, does improve performance on this relatively complex MDX statement. It is interesting that NONEMPTYMEMBER is slightly faster (I tested it many times to make sure it wasn't an anomaly) than NONEMPTYTUPLE. I think next I'll try testing this same example in a member formula and see what the differences are.

Question of the day: What was the first version of Essbase you used?