Logical Functions: IsSibling(): Conditional Logic within Calculations

Monday Dec 4th 2006 by William Pearson
Share:

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

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

In this lesson, we will expose another useful member of the MDX toolset, the IsSibling() function. The general purpose of IsSibling(), a logical function, is to return whether or not a member that we specify is the sibling of another member we specify. (By “sibling,” of course, we mean that the two members share the same parent.)

The IsSibling() function, like other logical functions and operators, evaluates values and returns a Boolean value. The utility of IsSibling() becomes clear when we realize the capability that it gives us to determine the “position,” together with the “parentage,” of a member within a dimensional hierarchy. IsSibling() more specifically allows us to test whether the member shares the same parent, and, therefore, occupies a relative “position” the same hierarchical “distance” from the parent, as another member that we specify within the dimension to which it belongs.

Similar to IsLeaf(), IsSibling() can best be employed to apply conditional logic within a couple of primary ways: as a component within a calculation, and as a component within a filter expression. In this article, we will concentrate upon IsSibling() from the perspective of its use within a calculation. We will discuss the straightforward purpose of the function, to ascertain (and indicate) whether a member is the sibling of another specified member; the manner in which IsSibling() manages to do this; and ways we can leverage the function to support effective conditional logic to meet various business needs within our own environments.

Along with an introduction to the IsSibling() function, this lesson will include:

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

The IsSibling() Function

Introduction

According to the Books Online, the IsSibling() function “returns whether a specified member is a sibling of another specified member.” A Boolean value of “True” is returned if the member expression to which the function is applied (to which I will refer as the “primary member expression” throughout this article) is a sibling of the second specified member (the “secondary member expression”); otherwise IsSibling() returns “False.” In its capacity, as a logical function, to “test” the nature / status of a member, IsSibling() is often employed in conjunction with the IIF function to conditionally drive the return of data, such as a member or members, or values, based upon the nature / status of members as siblings.

We will examine in detail the syntax for the IsSibling() function 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 simple, hypothetical business needs that illustrate a use for the function. This will afford us an opportunity to explore some the basic options that IsSibling() can offer the knowledgeable user. Hands-on practice with IsSibling(), 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, IsSibling() returns “True” if a specified member expression represents a sibling of (that is, shares a parent with) another member that we specify within a given use of the function; otherwise, the function returns “False.” We can use IsSibling() to apply conditional logic based upon the location or existence of members. As we have noted to be the case with most MDX functions, pairing the IsSibling() function with other MDX functions can help us to leverage its power much further than we might in an attempt to use it in standalone fashion.

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

Syntax

Syntactically, we employ the IsSibling() function by specifying the primary member expression (the member we are testing as to “sibling status”) and the secondary member expression (the member against which we are testing the primary member expression) within parentheses to the immediate right of the function. The function takes the member expressions thus appended to it as its arguments, and returns True if the member denoted by the primary member expression is a sibling of the secondary member expression (or, in other words, if the primary member shares the same parent as the secondary member).

If the member specified by the primary member expression is not a sibling of the secondary member (or if the primary member has a different parent than the secondary member) a False is returned, as we might expect.

The general syntax is shown in the following string:

IsSibling(Primary_Member_Expression, Secondary_Member_Expression)

Employing IsSibling() is, in itself, straightforward. As we have noted, we simply place the primary and secondary member expressions, respectively, in the parentheses to the right of the function. As an example, within a query executed against the sample Adventure Works cube, for the dimension named Geography (with a hierarchy of the same name), the following pseudo-expression:

IsSibling([Geography].[Geography].CURRENTMEMBER,  
    [Geography].[Geography].[Country].[United States] ) 

Returns True for the current member of the Geography dimension / Geography hierarchy for each of the following:

  • Australia
  • France
  • Germany
  • United Kingdom
  • United States

We note that the primary member appears, having been treated within MDX as its own sibling.

Depending upon the structure of the query (and specifically upon whether the syntax defining axes, etc., eliminates nulls), if members of levels subordinate to the Country level, within the Geography hierarchy, were returned in, say, the row axis of the dataset, their values would be null.

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 IsSibling() function 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 IsSibling() function within a couple of queries that illustrate its operation, focusing, within this article, upon scenarios where we use the function to support conditional logic within a calculation. (We examine its use in combination with the MDX Filter() function in another article of this series). We will undertake our practice exercises within scenarios that place IsSibling() within the context of meeting basic requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the use of the function 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. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index:

Prepare MSSQL Server Management Studio to Query Analysis Services

This procedure will take us through opening a new Query pane, upon which we will create our first query within the section that follows.

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 reporting and analysis needs, inform us that they have received a request to generate some simple values for a specific trending task that has been discussed at a recent meeting with the Controllers.

The analysts tell us that the values under immediate consideration involve Sales Order Counts, but, as always, that they want to develop an approach that will work equally well with other measures that have similar analysis potential. The desired end is to simply return the Order Count recorded on each day of a given operating month (typically, a dramatically larger number of Sales Orders are taken on the first day). The analysts further advise us that they will likely want to parameterize within the report layer of their business intelligence solution (they use Reporting Services for enterprise relational and OLAP reporting).

While this basic need might be easily met a number of ways with an MDX query, the analysts throw a further twist into the requirement: In addition to being likely to parameterize the month at runtime, they also want to be able to support parameterization of the level within the Date dimension (Calendar hierarchy) when executing the report. Again, the richness of MDX affords us a number of avenues to this objective. While parameterization is itself not a consideration in our current level of query design, we want to make it easy to accomplish within Reporting Services (the same concept would, of course, apply with other OLAP reporting tools that afford developer access to the MDX syntax that underlies them).

After we initially explain the use of the IsSibling() function as a candidate for meeting the requirement, our client colleagues state that they are interested in understanding how they might apply conditional logic via this function, within the context of a practical scenario such as the immediate requirement. A method of testing whether or not a specified member, or group of members, share the same parent as another member we specify is something that they hope to be able to extrapolate to uses with other dimensions, as well.

We offer to illustrate the use of IsSibling() to meet the immediate need, proposing to present a couple of examples, 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 IsSibling().

Procedure: Use the IsSibling() Function 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 IsSibling() function within a common context, the definition of a calculation based upon conditional logic. Our first example will serve as an introduction to a means of distinguishing the presence of a sibling relationship between members of the Date dimension (we will work with the month of January in Calendar Year 2004, within our initial example), as requested by the analysts, as a basis for meeting the business requirement to present the simple daily Order Counts.

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


-- MDX050-001 ISSIBLING()Function: Conditional Logic in 
--   the Definition of a Calculation

WITH 
MEMBER
   [Measures].[SelectCount]
AS
   IIF(
   
      ISSIBLING([Date].[Calendar].CURRENTMEMBER, 
         [Date].[Calendar].[Date].[January 1, 2004]),
      [Measures].[Order Count],
   
   NULL)
   
SELECT
   {[Measures].[SelectCount]}ON AXIS(0),
   NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM 
   [Adventure Works]

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


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

The above query returns the Order Count for each member of the Date dimension (Calendar hierarchy) in the cube; we use non-empty to screen the results to show our “focus” month, January, 2004, and the siblings of our specified, date-level primary member expression, January 1, 2004.

Recall that we have said that we might accomplish our ends through alternative methods. The approach we are taking here allows us to parameterize the secondary member expression. In doing so, we could set up a hierarchical picklist within Reporting Services whereby information consumers might select a given date, a month, quarter, and so forth, to drive the level for which values are returned. The obvious advantage is that consumers can dictate both the level of the date hierarchy and the specific “focus” member of the hierarchy itself, within the level (in our example, the month for which they wish to display the value for the corresponding children). In some circumstances such “double leverage” provided by a single parameter might be seen as a highly desirable efficiency.

In the SelectCount calculation, we put the IsSibling() function to work in applying conditional logic to generate the Order Count value: if the Current Member of the Date dimension / Calendar hierarchy (the primary member expression of our function) is a sibling of the secondary member expression (in the immediate example, the day / date of January 1, 2004), than the corresponding Order Count value is presented. Alternatively, we have directed, via the conditional logic of the IIF() function, that if the primary member expression (the Current Member of the Date dimension / Calendar hierarchy) is not a sibling of the secondary member expression (that is, the member’s parent is something other than the month of January, 2004), than the Order Count value is returned as null. (While we might have displayed all values, including nulls, we eliminated nulls in our present exercise by preceding the rows specification with the NON EMPTY keyword.)

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.

For more information about, and hands-on practice with, the sort of parameterization of MDX queries to which I refer within this article, see the member articles of my MSSQL Server Reporting Services series.

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


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

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


Illustration 3: Results Dataset (Partial View) – IsSibling() Function 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 January 2004 dates, which share the same parent member (the month of January, 2004), obviously, as the secondary member expression of January 1, 2004. Again, the conditional test of “sibling-hood” is applied via a calculated member within which we have leveraged the IsSibling() function.

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

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing the IsSibling() function. We will use a similar query within another such example next, to confirm understanding of the concepts. This query will provide an illustration of the use of the IsSibling() function within the context we have already seen, the definition of a calculated member based upon a comparison. And as before, we will base our example upon a local scenario described by the client representatives.

The developers / authors cite the following example as useful. They would like to create a basic query that returns the Customer Count for the respective month, quarter, half-year and annual levels for Calendar Year 2004. In addition, they are interested in seeing a simple 3-month Rolling Average Customer Count, but they wish for this calculated measure to appear only at the month level, and for a null to appear at the quarter, half-year and annual levels, of the Date hierarchy.

They further specify that they wish to see the calculated measure rounded to two decimal places. Finally, they prefer to present the Date hierarchy levels in the columns, and the measures in the rows, of the returned dataset.

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


Illustration 4: 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.


-- MDX050-002 ISSIBLING()Function: Conditional Logic in 
--   the Definition of a Calculation
WITH
MEMBER
   [Measures].[3-Mo Rolling Avg Customer Count]
AS
   'IIF(
   
      ISSIBLING([Date].[Calendar].CURRENTMEMBER, 
         [Date].[Calendar].[Month].[January 2004]),
     
             AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER), 
         
         [Measures].[Customer Count]),
    
     NULL
    
  )', FORMAT_STRING = "#,###.00" 

SELECT 
    
   {DESCENDANTS(
      [Date].[Calendar].[Calendar Year].[CY 2003]:[CY 2004],
          [Date].[Calendar].[Month],
      SELF_AND_BEFORE)} ON AXIS(0),
    
    
   CROSSJOIN( 
      {[Product].[Product Categories].[Category].[Bikes].CHILDREN },
    
         {[Measures].[Customer Count],
             [Measures].[3-Mo Rolling Avg Customer Count]}
    
         ) ON AXIS(1)
    
FROM
   [Adventure Works]

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


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

Note that we are adding a wider “presentation” date range (by specifying the range between Calendar Year 2003 and Calendar Year 2004 within the row axis) than required by the specification; this is to allow us to see months preceding 2004, so that we can ascertain that the rolling average is working as planned. (We would remove the “[CY 2003]:” portion of the specification after testing the average, as appropriate.)

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 (including the most relevant 2004 data - that containing the 3-month Rolling Average Customer Count - along with a couple of the preceding months of 2003), appears as partially shown in Illustration 6.


Illustration 6: Results Dataset (Partial View) – IsSibling() Functions 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 rolling average, 3-Mo Rolling Avg Customer Count, based upon the total count of customers recorded for a given month (that is, a month that shares the parent of the secondary member expression of January 1, 2004 within the IsSibling() function), plus the two preceding months (as specified within the expression LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER - the “True” portion of our IsSibling() function - divided by the number of months specified within the Avg() function (3, as we stipulate within the function).

Our calculation employs the IsSibling() function, much in the same manner as we have employed and explained it in our first example above: it supports conditional logic to determine the specified “focus” members of the Date dimension, and then retrieves the associated values based upon the outcome of this test. We can see each of the Customer Count values involved in the calculation of the 3-Mo Rolling Avg Customer Count within the returned data set, together with the average itself, as a means of presenting data useful in helping us to ascertain that our calculations are performing as expected.

Example:

 1,053    Total Customer Count for Jan 2004, Dec 2003 and Nov 2003 (311 + 442 + 300)
 Divided by 3 Mos.
 = 351.00  (as indicated in the 3-Mo Rolling Avg Customer Count for Jan 2004

The client representatives confirm that the immediate goal of a simple Rolling Avg Customer Count, the presentation of which has been dictated by the IsSibling() function in a manner that lends itself to the parameterization objectives that will arise at the reporting layer, (which we have explained within our discussion surrounding the earlier example) 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 or not a given dimensional member is the sibling of a specified member.

8.  Select File --> Save MDXQuery2.mdx As ..., name the file MDX050-002, 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 exposed another logical function contained within the MDX toolset, the IsSibling() function, whose general purpose, we learned, is to return a value indicating whether or not a member that we specify is the sibling of another member we specify. We learned that a significant part of the utility of the IsSibling() function lies in the fact that it can be used to test whether the member shares the same parent, and, therefore, the same hierarchical “distance” from the parent, as another dimensional member that we specify.

We noted that, similar to other logical functions, IsSibling() can best be employed to apply conditional logic within a couple of primary ways: as a component within a calculation, and as a component within a filter expression. In this article, we concentrated upon IsSibling() from the perspective of its use within a calculation. We discussed the straightforward purpose of the function, to ascertain whether a member is the sibling of another specified member; the manner in which IsSibling() manages to do this; and ways we can leverage the function to support effective conditional logic to meet various business needs within our own environments.

After introducing IsSibling(), we examined the syntax with which we employ the function. We then undertook illustrative examples whereby we put the IsSibling() function to work, within a couple of simple illustrations, to meet the business needs 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