MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function

Tuesday Jul 5th 2005 by William Pearson
Share:

Discover the CoalesceEmpty() function as a means of managing nulls for presentation purposes. MSAS Architect Bill Pearson provides hands-on practice in the basics, as well as examining a more sophisticated approach with CoalesceEmpty().

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

Within the world of OLAP, sparsity is pervasive. There are many reasons to want to display something other than an empty space in a report, where a "blank" is about as welcome, to most readers, as silence on a radio show. In an Analysis Services cube, as well as in the reporting layer of an integrated business intelligence solution, we have various options for the elimination or suppression of, and substitution for, nulls. This article concerns itself with one of those options for "handling the emptiness," the CoalesceEmpty() function.

In this article, we will introduce and overview the CoalesceEmpty() function. We will first comment upon the general operation of CoalesceEmpty(), and then we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the rudimentary uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

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.

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the CoalesceEmpty() function in a manner that illustrates its operation. We will do so in simple scenarios that place CoalesceEmpty() within the context of meeting a business need, illustrating a simple case of its use, where the Value Expressions are of the same type (numeric, in our example) and a more involved case, where we wish to substitute a string when we encounter a null in a numeric value expression.

To begin, we will construct a SELECT query with a clearly defined set, then put CoalesceEmpty() to use in manipulating the returned dataset to meet the business needs of a group of hypothetical information consumers. The intent is, of course, to demonstrate the operation of the CoalesceEmpty() function in a straightforward manner.

Let's return to the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

Let's assume, for our practice example, that we have received a call from the Logistics department of the FoodMart organization, requesting some information surrounding units shipped for a given time frame upon which they plan to perform some basic analysis. The Logistics information consumers specifically wish to know the total units shipped figure for each of the Product Names carried within USA Warehouses, for the year 1997. The group has become focused on this product / time combination initially to do some per unit analysis of various logistics costs within the United States, where the majority of the organization's business is conducted.

We will first create a basic query that returns the units shipped totals for each of the Product Names, over the year under consideration. The consumers state that they may want to extend their specification, based upon the appearance of the initial dataset.

Let's construct a simple query, therefore, to return the requested information, presented by Units Shipped (as the column) and the Product Names (as rows). Moreover, we will filter by USA Warehouses and the Year 1997.

5.  Type the following query into the Query pane:


--MDX33-01: Using CoalesceEmpty() in a Simple Scenario - Step 1:
--Core Dataset containing Nulls
SELECT 
    {[Measures].[Units Shipped]} ON COLUMNS,
     {DESCENDANTS([Product].[All Products].[Food], [Product].[Product Name])} 
         ON ROWS
FROM
    [Warehouse]
WHERE
   ([Warehouse].[All Warehouses].[USA], [Time].[1997])  

6.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset partially depicted in Illustration 1 appears.


Illustration 1: Result Dataset – Preparation for Use of CoalesceEmpty() Function

We see the Units Shipped measure populating the column axis, and the Product Name level of the Product dimension appearing on the row axis. We note too, in scrolling down the dataset, that, although sparsity is not considerable in our dataset, several nulls appear within the Product Names.

7.  Select File -> Save As, name the file MDX033-01, and place it in a meaningful location.

8.  Leave the query open for the next section.

Next, let's say that our information consumers are provided with the full set of data that we have generated. They examine the data, and then request that we replace the "blanks" with zeros ("0"). They state that some of the intended audience for the information has expressed confusion with regard to the nulls, not knowing whether the blank cells mean "no units shipped," or that data has been somehow omitted from the cube.

This requirement represents an opportunity to employ the "numeric option" for the CoalesceEmpty() function. As we discussed earlier, the function can return a number when supplied number value expressions. We take the following actions to meet the new consumer request:

9.  Within the query we have saved as MDX033-01, replace the existing comment line of the query with the following:

-- MDX033-02, Use COALESCEEMPTY() to supply a Numeric Replacement for Nulls

10.  Save the query as MDX033-2, to prevent damaging MDX033-1.

11.  Insert the following syntax between the above comment line and the SELECT keyword in the existing query:


WITH
MEMBER
    [Measures].[Vol Shipped]
AS
    'COALESCEEMPTY( [Measures].[Units Shipped], 0)'

12.  Replace the following (the first line under the SELECT keyword):

    {[Measures].[Units Shipped]} ON COLUMNS,

With the following:

{[Measures].[Vol Shipped]} ON COLUMNS,

The Query pane appears as shown in Illustration 2, with the new additions highlighted.


Illustration 2: CoalesceEmpty() in Place to Substitute Nulls with Zeros

13.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset partially depicted in Illustration 3 appears.


Illustration 3: Partial Result Dataset – Zeros Substituted for Nulls

We note that the nulls have been replaced with zeros, as expected.

14.  Re-save the file as MDX033-2.



We have used the CoalesceEmpty() function, in its numeric "incarnation," to replace nulls with a number, zero. The string version of CoalesceEmpty() works in much the same manner, except that it substitutes "string for string."

Let's take a look at an example where we again use CoalesceEmpty(), but where the requirement includes a bit of a twist: We will be asked to replace nulls occurring within a numeric value with a string – a word versus a number. We will base the scenario upon the original core query, taking the following setting into consideration.

Let's say that we have discussed the results in our last dataset with the Logistics group, which is happy with the fact that we are able to make substitutions for the nulls, but wishes to make a change. The consumers have determined that the presentation will be enhanced by the use of a word, "None," instead of zeros (apparently this was suggested by an overly excitable senior manager) where no shipping volume of a given product occurred for the year. We agree to make the change, and proceed as follows:

15.  Within the query we have saved as MDX033-2, replace the existing comment line of the query with the following:

-- MDX033-3, Use COALESCEEMPTY() 
to Replace a Null Numeric Val Exp w String

16.  Save the query as MDX033-3 to protect its predecessor.

17.         Replace the syntax between the above comment line and the SELECT keyword in the existing query, currently appearing as:


WITH
MEMBER
    [Measures].[Vol Shipped]
AS
    'COALESCEEMPTY( [Measures].[Units Shipped], 0)'

With the following:


WITH
MEMBER
   [Measures].[NoneShipped] 
AS
    ' "None" '
MEMBER
   [Measures].[Vol Shipped]
AS
    'CoalesceEmpty([Measures].[Units Shipped], [Measures].[NoneShipped])'

We are replacing the calculated member we used initially with two calculated members, the second of which relies upon the first to store the string that we wish to return, under the cloak of another measure, to be substituted for null values that are encountered. Because we have assigned the output of the second calculated member to the name we have used within our SELECT statement [Measures].[Vol Shipped]), further alterations within the query are not necessary.

The Query pane appears as shown in Illustration 4, with the new additions highlighted.


Illustration 4: CoalesceEmpty() Arranged to Substitute Null Numeric Value with a String

18.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset partially depicted in Illustration 5 appears.


Illustration 5: Partial Result Dataset – String Substituted for Null Numeric Value

We note that the nulls have been replaced by the word "None," as requested by the information consumers.

19.  Re-save the file as MDX033-3.

We deliver the new results to the Logistics information consumers, who accept with thanks the results as meeting the current need.

20.  Select File --> Exit to close the MDX Sample Application, when ready.

Summary ...

In this lesson, we explored the MDX CoalesceEmpty() function. Beginning with a discussion of the pervasiveness of sparsity within OLAP cubes as a circumstance inherent to their nature, we introduced CoalesceEmpty() as an avenue to handling that sparsity from a presentation perspective. We commented upon the general operation of CoalesceEmpty(), and then we examined the syntax of the function.

We undertook illustrative examples to gain hands-on experience with the function in practice exercises, where we described a business need as specified by a hypothetical group of information consumers. We examined a simple case where we used CoalesceEmpty() to substitute a numeric value for a null numeric value. We then considered a more complicated scenario, where we went beyond the standard uses (substitution of numeric value for numeric value, or string value for string value) and substituted a string value for a null numeric value through the use of stepped calculated members. Finally, we briefly discussed the results datasets we obtain in the practice examples.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved