OBIEE: NonEmpty MDX for Essbase (and Microsoft) - the secret life of PERF_PREFER_SUPPRESS_EMPTY_TUPLES

PERF_PREFER_SUPPRESS_EMPTY_TUPLES option in OBIEE Database features reminds me of the Japanese Fugu dish. It might be very tasty or deadly, if prepared without appropriate understanding. I have good news, unlike Fugu, you don't have to learn 3 years to use PERF_PREFER_SUPPRESS_EMPTY_TUPLES.

So what is it? If you Google, you will not find much, except the following description:
"PERF_PREFER_SUPPRESS_EMPTY_TUPLES: This is for Essbase only. If enabled, instead of applying non empty on the axis, which may contains a very sparse set. Each cross-join of two dimensions will have empty tuples suppressed before cross-joining another dimension. "

(You might also find out that in 10g, it used to be "environment variable OBIS_Essbase_NonEmptyTuples_Generation.Database.Catalog.CubeTable".)

 

This is no longer 100% correct. One of my customers (well done Dov Ziskin) persuaded Oracle Support to release this feature for Microsoft Analysis Services as well, as patch 18669600 (For OBIEE 11.1.1.7.140415 only).   

So what is PERF_PREFER_SUPPRESS_EMPTY_TUPLES? 

Before I continue, a confession: because of internal reasons, I never worked with Essbase, So I hope that what I describe for OBIEE with Microsoft Analytical Services is correct for Essbase as well.

When running MDX, the default code produced by OBIEE, creates an AxisSet that crossjoins all relevant dimensions and only then uses NONEMPTY function on the combination of the AxisSet and the relevant mesures. As a result, when combining large Dimensions there might be performance degradation and on Microsoft, sometimes the error: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." (As I said, Very large dimensions). Enabling the PERF_PREFER_SUPPRESS_EMPTY_TUPLES adds the NONEMPTY to each crossjoin. So we use only combinations that has values in measures. It gives a performance boost in those cases. (1-2 seconds instead of 3-4 minutes or the above error in my cases).

If it's so great, why the Fugu allegory?
Because the MDX doesn't "know" what measures to use when using the "NONEMPTY" in the early stage of the AxisSet creation on the dimensions and uses the "Default Measure / Default Member". If your Default Measure covers all cases of data in the cube, life is great. If not, you will have incorrect results. So you must be sure you have a good default measure or create a synthetic one, that might be combination of all possible cube values. (for example on Microsoft: ALTER CUBE [Your Cube Name] UPDATE DIMENSION [Measures], DEFAULT_MEMBER = [Measures].[Your New Default Measure])