MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations

Monday Oct 2nd 2006 by William Pearson
Share:

Use IsLeaf() to support conditional logic within calculations. BI Architect Bill Pearson introduces IsLeaf(), and then leads a hands-on practice session with this valuable MDX operator.

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

We have noted in several articles that, while MDX functions comprise the lion’s share of the MDX Essential series, numerous operators are provided within the language. Of the logical, comparison, set, string, and unary operators provided by MDX, we will examine another logical operator in this article. The IsLeaf() operator, like other logical operators, evaluates values and returns a Boolean value. The utility of IsLeaf() becomes clear when we realize its value in helping us to determine the position of a member within a dimensional hierarchy. IsLeaf() more specifically allows us to test if a member is at leaf level, or at the “bottom” level of the dimension to which it belongs.

In this article, we will concentrate upon the useful IsLeaf() operator, from the perspective of its use within a calculation. We will discuss the straightforward purpose of the operator, to ascertain whether a member is a leaf-level member of a dimension; the manner in which IsLeaf() manages to do this; and ways we can leverage the operator to support effective conditional logic to meet various business needs within our own environments.

Along with an introduction to the IsLeaf() operator, this lesson will include:

  • an examination of the syntax surrounding the operator;
  • illustrative examples of uses of the operator in practice exercises;
  • a brief discussion of the MDX results obtained within each of the practice examples.

The IsLeaf() Operator

Introduction

According to the Books Online, the IsLeaf() operator “returns a value whether or not a specified member is a leaf member.” A Boolean value of “True” is returned if the member expression to which it is applied is a leaf member; otherwise IsLeaf() returns “False.” IsLeaf() is often employed in conjunction with the IIF function to conditionally return data, such as a member or members (for example, children of a selected member, if they exist, or the selected member if it has no children), or values.

We will examine in detail the syntax for the IsLeaf() operator after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support a hypothetical business need that illustrates a use for the operator. This will afford us an opportunity to explore some of the basic options that IsLeaf() can offer the knowledgeable user. Hands-on practice with IsLeaf(), where we will create queries that employ the function, will help us to activate what we have learned in the Discussion and Syntax sections.

Discussion

To restate our initial description of its operation, IsLeaf() returns “True” if a specified member expression represents a leaf (or “level 0”) member; otherwise, the operator returns “False.” We can use IsLeaf() to apply conditional logic based upon the location or existence of members. As we have noted to be the case with most MDX functions and operators, pairing the IsLeaf() operator with other MDX operators and functions can help us to leverage its power even further.

Let’s look at syntax specifics to further clarify the operation of IsLeaf().

Syntax

Syntactically, we employ the IsLeaf() operator by specifying the member expression in parentheses to the immediate right of the operator. The operator takes the member expression which is appended to it as its argument, and returns True if the member denoted by the member expression is a leaf member (or, in other words, the member resides at the lowest (0) level of the dimension). If the member specified by the member expression is not a leaf member (or if the member resides at a dimensional level higher than the zero, or “bottom,” level), a False is returned.

The general syntax is shown in the following string:

IsLeaf(Member Expression)

Employing IsLeaf() is, in itself, straightforward. As we have noted, we simply place the member expression under consideration in the parentheses to the right of the operator. As an example, within a query executed against the sample Adventure Works cube, for a dimension named Sales Territory (with a hierarchy of the same name), the following pseudo-expression:


IsLeaf([Sales Territory].[Sales Territory].CURRENTMEMBER) 

Returns True if the current member of the Sales Territory dimension / Sales Territory hierarchy is at level 0.

NOTE: For information on several of the “relative” functions, of which .CURRENTMEMBER is an example, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.

We will practice some uses of the IsLeaf() operator in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the IsLeaf() operator in a couple of queries that illustrate its operation. We will do so in simple scenarios that place IsLeaf() within the context of meeting basic requirements similar to those we might encounter within our respective daily environments. The intent is to demonstrate the use of the operator in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.


Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 2.


Illustration 2: Connecting to the Server ...

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the “+” sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s), as shown in Illustration 3.


Illustration 3: Exposing the Analysis Services Databases in the Object Browser

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 3 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as depicted in Illustration 4.


Illustration 4: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens. We may see multiple cubes here. Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear similar to those shown in Illustration 5.


Illustration 5: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as depicted in Illustration 6.


Illustration 6: Click the New Query Button with the Adventure Works Cube Selected

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as shown in Illustration 7.


Illustration 7: Adventure Works Cube Metadata Appears ...

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

Procedure: Satisfy Business Requirements with MDX

Let’s assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. Analysts within the Controllers’ Group, with whom we have worked in the past to deliver solutions to meet various ad hoc reporting and analysis needs, inform us that they have received a request to generate simple averages for a specific trending task that has been discussed at a recent meeting with the Controllers.

The analysts tell us that the averages under immediate consideration involve Sales Order Counts, but that they want to develop an approach that will work equally well with other measures that have similar analysis potential. The desired end is a simple average, to be calculated by averaging the number of Sales Orders recorded on the first day of each operating month (typically a dramatically larger number of Sales Orders are taken on the first day), and the number of Orders taken on the last day of the same month. The analysts emphasize that they are aware that more precise averages can be generated. This simple average is adequate for the limited purpose for which it is designed, they tell us, much as similar “quick and dirty” calculations have heretofore been performed manually within the realms of headcount and inventory.

Our client colleagues inform us that they initially need to understand a means, within MDX, of distinguishing leaf-level members within the Date dimension of the Adventure Works cube, from the general membership of the dimension. This method of testing whether a member is leaf-level or not is something that they hope to be able to extrapolate to uses with other dimensions, as well.

After we introduce the concepts behind the IsLeaf() operator, much as we have done in the earlier sections of this article, we offer to illustrate the use of IsLeaf() to meet the immediate needs. We propose to present a two-stage example, to solidify the analysts’ new understanding, as well as to assist in rounding their overall MDX “vocabularies.” We then set about the assembly of our examples to illustrate uses of IsLeaf().

Procedure: Use the IsLeaf() Operator to Perform Conditional Logic within a Calculation

Per the request of our client colleagues, we will first construct a simple query to provide an illustration of the use of the IsLeaf() operator within a common context, the definition of a calculation based upon conditional logic. Our first example will serve as both an introduction to a means of distinguishing leaf-level members within the Date dimension, as requested by the analysts, and as a basis for the subsequent satisfaction, through several additional steps, of the business requirement to present the simple average of Sales Order Counts.

1.  Type (or cut and paste) the following query into the Query pane:


-- MDX048-001-1 IsLeaf() Operator:  Conditional Logic in Definition of a Calculation
WITH 
MEMBER
   [Measures].[BegCount]
AS
   IIF(ISLEAF([Date].[Calendar].CURRENTMEMBER),
       [Measures].[Order Count],
   ([Date].[Calendar].CURRENTMEMBER.FIRSTCHILD, [Measures].[BegCount]))
   
SELECT
   {[Measures].[BegCount]}ON COLUMNS,
   {[Date].[Calendar].MEMBERS} ON ROWS
FROM 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 8.


Illustration 8: Our Initial Query in the Query Pane ...

The above query supplies the Order Count for each day of each Calendar Month in the cube, and, in effect, the basis for the simple average that our client colleagues have requested. We will calculate the ending Order Count for each month in a very similar calculation in an extension of the query in the steps that follow. In both calculations, we put the IsLeaf() operator to work in applying conditional logic to generate the Order Count value, if the Current Member of the Date dimension / Calendar hierarchy is a leaf-level member (meaning a day / date). We have directed, via the conditional logic of the IIF() function, that if the Current Member is not a leaf-level member, then the Order Count for the First Child member of the Date dimension (Calendar hierarchy) is returned, at the level of the Current Member.

NOTE: For more detail surrounding the IIF() function, see String / Numeric Functions: Introducing the IIF() Function and String / Numeric Functions: More on the IIF() Function, both members of the Database Journal MDX Essentials series. Moreover, see Member Functions: More "Family" Functions, within the same series, for more information about the .FirstChild function.

We have specified that the Calendar Date members are to populate the rows axis to provide, to some extent, a quick means of reasonability testing of the logic within the calculation that we have defined, as we shall see.

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 9.


Illustration 9: Click Execute to Run the Query...

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


Illustration 10: Results Dataset (Partial View) – IsLeaf() Operator within a Calculation

In the partial view of the returned dataset, we see that the calculation accomplishes the intended purpose – generating the Order Count for the individual dates (the leaf level for the Date dimension / Calendar hierarchy), while returning the Order Count for the first child member of the Date dimension / Calendar hierarchy, at the level of each current member, for the members that are not at the leaf level.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX048-001-1, and place it in a meaningful location.

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing the IsLeaf() operator. We will next use the query we have crafted, we tell them, with minor modifications, as a basis for a richer query that delivers the monthly average Order Count, alongside the underlying calculations and measure, to allow us to visually verify the intended operation.

Because the next query is significantly enhanced, we will begin it from scratch to save the time involved in altering the first query.

4.  Select File --> New from the main menu.

5.  Select Query with Current Connection from the cascading menu that appears next, as shown in Illustration 11.


Illustration 11: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

6.  Type (or cut and paste) the following query into the Query pane:


-- MDX048-001-2 IsLeaf() Operator:  Conditional Logic in Definition 
--   of a Calculation, Part II
WITH 
MEMBER
   [Measures].[BegCount]
   
AS
   IIF(ISLEAF([Date].[Calendar].CURRENTMEMBER),
   
       [Measures].[Order Count],
       
   ([Date].[Calendar].CURRENTMEMBER.FIRSTCHILD, [Measures].[BegCount])) 
MEMBER
   [Measures].[EndCount]
   
AS
   IIF(ISLEAF([Date].[Calendar].CURRENTMEMBER),
   
       [Measures].[Order Count],
       
   ([Date].[Calendar].CURRENTMEMBER.LASTCHILD, [Measures].[EndCount])) 
   
MEMBER
   [Measures].[AvgDailyCount]
   
AS
   ([Measures].[BegCount]+ [Measures].[EndCount])/2
   
MEMBER
   [Measures].[TotalMoOrderCount]
   
AS
   [Measures].[Order Count]
      
SELECT
   {[Measures].[BegCount], [Measures].[EndCount],
    
      [Measures].[AvgDailyCount], [Measures].[TotalMoOrderCount]}
      
   ON COLUMNS,
   
   {[Date].[Calendar].[Month].MEMBERS} ON ROWS
FROM 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 12.


Illustration 12: Our Initial Query in the Query Pane ...

7.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset partially shown in Illustration 13 appears.


Illustration 13: Results Dataset (Partial View) – IsLeaf() Operators within a Calculation

In the returned dataset, we see that the query appears to meet the business requirements outlined by the client analysts group. We have delivered a simple average, AvgDailyCount, based upon 1) the number of Sales Orders recorded on the first day of each operating month (through calculation BegCount), and 2) the number of Orders taken on the last day of the same month (through calculation EndCount), dividing the sum of the two calculations by two (2). Both calculations employ the IsLeaf() operator, in the manner we have explained in our first example above, to support conditional logic to determine the level of the member of the Date hierarchy that is under examination, and then retrieving the associated value based upon the outcome of this test. We show each of the values involved in the calculation of the average, together with the average itself and the total number of Sales Orders for the month, as a means of presenting data useful in helping us to ascertain that our calculations are performing as expected.

The client representatives confirm that the immediate goal of a simple Sales Order average has been met. Moreover, they state that the illustration we have provided will be easily extrapolated to other scenarios where they need to perform an action, or to present a value, based upon the outcome of a test as to whether a given dimensional member is leaf-level or not.

8.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX048-001-2, and place it in a meaningful location.

9.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we shifted our examination from the MDX functions, as we have upon occasion in the past, to concentrate upon another MDX operator. We introduced the IsLeaf() operator, noting that, like other logical operators, it evaluates specified values and returns a Boolean value. We explained that a large part of the IsLeaf() operator’s utility lies in its capability to support conditional logic in calculations and expressions within Analysis Services.

After discussing the straightforward purpose of the IsLeaf() operator, to ascertain whether a specified dimensional member is a leaf member, we examined the manner in which IsLeaf() manages to do this, touching upon ways we can leverage the operator within calculations and expressions to meet various business needs in our own environments. Next, we examined the syntax with which we employ IsLeaf(). Finally, we undertook illustrative examples whereby we put the IsLeaf() operator to work, initially within a simple illustration to illustrate its general operation, and then in a query containing multiple calculations, two of which used the IsLeaf() operator in a manner detailed within our first illustration, to meet the business need of a hypothetical client. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» 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