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

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.

-- 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.

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.

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.

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

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.

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.

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.

-- 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.

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

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.

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.