A second, more efficient way to eliminate empty cells is to use the NONEMPTYCROSSJOIN() function, which combines two sets and produces only the nonempty combinations of the members. This technique is more efficient than the technique that Listing 2 shows in terms of time and memory because Listing 2's code creates all combinations of the stores and customers before eliminating the empty cells.
Be aware that the NONEMPTYCROSSJOIN() function also eliminates any combination of members that includes a calculated member. Listing 3 shows an example in which the query looks for nonempty combinations of years and measures. Even though the combination of 1997 and Profit is nonempty, the query eliminates the combination because Profit is a calculated member. If you rewrite Listing 3's query so that it uses the NON EMPTY keyword separately from the CROSSJOIN() function, the query includes the calculated measures Profit and Sales Average.
Even though the function eliminates calculated members, NONEMPTYCROSSJOIN() is still useful. In queries that combine large numbers of members from different dimensions, the performance of NONEMPTYCROSSJOIN() can be more than an order of magnitude faster than a typical CROSSJOIN() function. You should always consider using NONEMPTYCROSSJOIN() in large, complex queries.
A third technique for eliminating empties is using the MDX function ISEMPTY(). This function accepts a tuple (a list of dimension members from different dimensions) and tests whether the tuple has an associated box of measures. If no box exists, the function returns True. ISEMPTY() is useful when you're creating conditional logic or filtering members from one dimension. I frequently use the ISEMPTY() function to determine which time dimension members have data loaded in a cube. In the code that Listing 4 shows, I use ISEMPTY() to determine the most recent month that contains data in the Sales cube. Then, with the most recent month as a point of reference, I use the LASTPERIODS() function to display the most recent 3 months. You can use this technique to create reports that shift forward in time when you load new data into the OLAP cube.
The fourth MDX technique uses the COALESCEEMPTY() function to eliminate empties. COALESCEEMPTY() replaces empty values with an alternative formula. The code in Listing 5 shows how to use COALESCEEMPTY() to display empty values as N/A. This technique is equivalent to using conditional logic to replace empty expressions; for example, you could use a conditional expression such as IIF( ISEMPTY( <<expr1>> ), <<expr2>>, <<expr1>>). The difference is that COALESCEEMPTY() can accept a variable list of expressions. The function continually checks each expression until it finds one that isn't empty, then returns to the first nonempty expression from its list. COALESCEEMPTY() requires that all its parameters are string type or numeric type; you can't mix the two types. (This requirement is similar to the IIF() function's requirements.) Note that I use a trick in Listing 5's code to circumvent this limitation and check the tuple to see if it represents an empty data cell and replace it with a string. The trick is to embed the string in another calculated member so that COALESCEEMPTY() doesn't identify the second parameter as a string type.
Empty cells are so common in OLAP that you can't ignore them when you're developing MDX queries. Empties can affect not only the numeric correctness of a result but also Analysis Services' performance and memory utilization. Always consider options such as using NONEMPTYCROSSJOIN() or ISEMPTY() to filter sets and improve the efficiency of your queries. Also, when you count items in a set, be aware from the outset of whether you intend to count the empties. When you effectively manage your empty cells, you improve query performance and make analysis easier.