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.
Discussion
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().
Syntax
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:
String Option:
CoalesceEmpty (<<String Value Expression>>, <<, String Value Expression ... >>)
Numeric Option:
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.
» 
Comment and Contribute
