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

October 2, 2006

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

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