MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function - Page 2
July 5, 2005
The CoalesceEmpty() Function
As we discussed in my MDX Essentials article Logical Functions: The IsEmpty() Function, in working with multidimensional data sets, we are often confronted with empty cells. Data is often sparse in these sets by their very physical nature. Because, as a simple example, every product might not be sold at every store in every time period (to cite an instance from the dimensions of the FoodMart2000 sample environment), we will see empty cells in abundance in a data set that contains intersects of these dimensions. Moreover, as many of us have noted, in working with crossjoins of any magnitude, we often encounter a dramatic manifestation of empty cells, as a general rule. Empty cells mean nulls, and nulls can mean incorrect results in using MDX to support analysis in reporting. While the very few sample cubes with which we are provided in an installation of Analysis Services are remarkably free of sparsity, this certainly does not reflect reality in general. Sparsity, as we have intimated, is a fact of life, but not necessarily something we want to reflect in our reports.
Issues that range between failure of downstream reports to execute and mere inconveniences (such as inordinate white space, bizarre characters, and other formatting gaffes, in reports, browsers, and so forth) can be the consequence of failing to restrain empties at some point between the data source and the reporting application, be it Reporting Services or another enterprise application connected to an Analysis Services cube. One of the many beauties of the integrated Microsoft BI solution (composed of the powerful MSSQL Server / Analysis Services / Reporting Services combination) lies in the fact that this is easily done within more than one layer (for instance, within the OLTP database, OLAP cube, or the report itself). We have several options at our disposal for managing the emptiness, at one or all of these levels, through elimination, suppression, or, in the case of CoalesceEmpty(), substitution.
While elimination or suppression, the end objectives of which are to completely remove empty items from the final presentation, is often the ideal approach, a substitution approach becomes a de facto conclusion in many scenarios, as well. Substitution is particularly appropriate when it is simply not possible to filter nulls from the OLAP data we are bringing into our report, but can be useful sometimes for other reasons, as well. Chiefly, we may not want to simply strip out all items with null values, but, instead, may want to report upon the very fact that there was no activity in certain cases. Reporting Services, as well as other reporting applications, manages aggregations fine when nulls are involved, working in conjunction with Analysis Services; what we are typically seeking with substitution, as with most "empties management," is the accomplishment of presentation objectives, and sometimes those objectives include simple replacement of nulls with, say, a zero ("0") or an "N/A."
This article addresses using the CoalesceEmpty() function to enforce substitution, as an approach to sparsity via MDX. Performing substitution within MDX offers us multiple options for managing nulls when it comes to reporting considerations, particularly when the enterprise is working with the integrated Microsoft solution. Because we can use CoalesceEmpty() within calculated members and named sets within Analysis Services, we can deliver "null-free data" to the DataSets we construct within Reporting Services (or other reporting applications we might be using to report from an Analysis Services cube). In addition, we can leverage the function in the reporting layer to perform its magic within the DataSet query that underlies the report. As I constantly emphasize in my series here at Database Journal and elsewhere, the multi-layered architecture that is Microsoft business intelligence offers us a great deal of similar opportunities for creative customization and optimization, in a wide range of scenarios, if we consider "layered intelligence" from an integrated perspective.
NOTE: For hands-on practice in handling empties in an OLAP report, from a Reporting Services perspective, see my Database Journal article MSSQL Server Reporting Services: Black Belt Components: Manage Nulls in OLAP Reports.
The purpose of the CoalesceEmpty() function is, quite simply, to convert an empty cell value to a non-empty value. The "coalesced value" returned by the function can be a string or a number. CoalesceEmpty() works with two parameters that we supply it: the cell that we wish it to examine and a list of strings / numbers (the "list" often consists of a single member). In performing its intended operation, the function simply returns the first of the parameters provided that is not empty. (We will look at the details of the syntax in the next section.)
CoalesceEmpty() requires that the two parameters be of the same type (string or number), as we shall see. In our practice session, however, we will examine an approach to handling scenarios where we wish to return a coalesced value whose type differs from that of the initial value expression (the first parameter). This is a common need, as the requirement we face may be to substitute a string, such as "Not in Stock" in data where an empty / null is detected.
Let's look at some syntax illustrations to further clarify the operation of CoalesceEmpty().
Syntactically, in using the CoalesceEmpty() function to return a coalesced value, we supply two value expressions, as we mentioned earlier. CoalesceEmpty() looks at the first value expression we provide, and, if it is null, returns the second value expression. If we have listed multiple strings or numbers in the second expression, CoalesceEmpty() looks at each in turn, returning the first non-null member it encounters as the coalesced value.
The syntax for CoalesceEmpty() is shown in the following options:
CoalesceEmpty (<<String Value Expression>>, <<, String Value Expression ... >>)
CoalesceEmpty (<<Numeric Value Expression>>, <<, Numeric Value Expression ... >>)
The Value Expression upon which we seek to "apply" the CoalesceEmpty() function is specified first within the parentheses following the keyword CoalesceEmpty. The function evaluates the first Value Expression as non-null or null. If the first Value Expression evaluates as non-null, the function simply returns the first Value Expression as the coalesced value. If the first Value Expression evaluates as null, then CoalesceEmpty() returns the first non-null member within the second Value Expression as the coalesced value. If the last member within the second Expression is null, the entire operator returns null.
As we have mentioned, there are string and numeric "variations" of CoalesceEmpty(). The function can return a string when supplied string value expressions, or it can return a number when supplied number value expressions. As we mentioned earlier, we will undertake a practice example where we handle a need to return a string when encountering a numeric value expression.
We will practice some uses of the CoalesceEmpty() function in the section that follows.