MDX Essentials: Logical Functions: The IsEmpty() Function - Page 3

November 1, 2004

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the ISEMPTY() function in a manner that illustrates its operation within a multi-step example. We will first create a query that attempts to meet a business need as stated by a hypothetical group of information consumers. The resulting dataset will illustrate a scenario in which we might want to manage an empty underlying cell. We will then expose a means, using the ISEMPTY() function, to provide the results we desire in the final presentation of the data.

We will call upon the MDX Sample Application again, as our tool 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 request from a group of information consumers in the Logistics unit of the FoodMart2000 organization. The request is for support in the presentation of some data, housed within the Warehouse sample cube, regarding Product shipping volumes for 1998. The consumers wish to see the total Units Shipped for FoodMart200 products by Product Family. They also wish to see a second column in the display that presents a percent of parent value - that is, they want to see the percentages of total product units shipped that can be attributed to each Product Family.

We will begin by composing a simple query to meet the business requirement, which will illustrate an effect of an empty cell upon the display of the data. We will then eliminate the problem in subsequent steps, after discussing why the problem exists in the first place. My objective is thus to illustrate use of the function to meet the hypothetical business requirement we have outlined, as a means of reinforcing the concepts in our minds. Once we have activated the concepts, they can be triggered upon meeting a similar situation in our respective business environments.

To initiate action, let's attempt to meet the requirement in a somewhat intuitive way.

5.  Type the following query into the Query pane:


-- MDX25-1:  Tutorial Query Step 1
WITH MEMBER
   [Measures].[% Total Products]
AS
   '[Measures].[Units Shipped]/([Measures].[Units Shipped],[Product].Parent)'
SELECT
   {[Measures].[Units Shipped], [Measures].[% Total Products]} ON COLUMNS, 
   {DESCENDANTS([Product].[All Products], [Product].[Product Family], 
      SELF_AND_BEFORE)} ON ROWS
FROM
   [Warehouse]
WHERE 
   ([Time].[Year].[1998])

The purpose of the WITH MEMBER section of the query is to create a calculated member to provide the "percent of parent" (that is, percent of total Product Units Shipped) contributed by each of the Product Family members, including the total of all Product Families. Reason tells us that the Total Products line in the data set returned should represent 100.00 %, and discussions with members of the intended audience confirms that this is what they wish to see. Herein lies a complication that rests at the heart of a need for management of empty cells, as we shall see shortly.

The SELECT statement simply requests the calculated member, alongside the Units Shipped measure for each respective Product Family's activity for 1998. At this stage, we have added no formatting for the calculated member.

NOTE: For a detailed introduction to calculated members, see the following Database Journal articles: Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives. In addition, for an introduction to the .Parent function, see MDX Member Functions: The "Family" Functions.

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

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 2 appears.


Illustration 2: Initial Results Dataset

Upon initial blush, we can see that something funky seems to be happening with the calculated member for % Total Products, but to ascertain how the calculated member is performing overall, we first need to format it to indicate percentages correctly. What we see initially is a rounding effect that is making the effectiveness of the calculated member unclear.

7.  Select File -> Save As..., name the file MDX25-1, and place it in a meaningful location. Leave the file open for the next step.

8.  Modify the comment line to read:

-- MDX25-2:  Tutorial Query Step 2

9.  Save the file as MDX25-2, to protect MDX25-1.

Now we will take another preparatory step and add formatting into the WITH MEMBER clause, to make your percentages appear in a manner that is useful to the information consumers.

10.  Insert the following string:

     , FORMAT_STRING = '#.00%'

between the existing fifth line of the query (counting the comment line, and shown below):

'[Measures].[Units Shipped]/([Measures].[Units Shipped],[Product].Parent)'

and the existing sixth line of the query, the SELECT keyword.

The Query pane appears as shown in Illustration 3, with changes circled in red.


Illustration 3: Modified Query in Query Pane (Compressed View)

11.  Execute the query by clicking the Run Query button in the toolbar, once again.

The Results pane is populated once more, and the dataset depicted in Illustration 4 appears.


Illustration 4: Results Dataset, with Percent Formatting Enhancement

We see the percentages displayed, filling the blanks that existed before due to undesirable rounding.

12.  Select File -> Save to save the query as MDX25-2. Leave the file open for the next step.

13.  Modify the comment line to read:

-- MDX25-3:  Tutorial Query - Final

14.  Save the file as MDX25-3, to protect MDX25-2.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers