Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 5, 2005

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

By William Pearson



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
    {[Measures].[Units Shipped]} ON COLUMNS,
     {DESCENDANTS([Product].[All Products].[Food], [Product].[Product Name])} 
         ON ROWS
   ([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:

    [Measures].[Vol Shipped]
    '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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM