Logical Functions: IsSibling(): Conditional Logic within Filter Expressions

Tuesday Jan 2nd 2007 by William Pearson
Share:

Use IsSibling() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsSibling() in calculations, and provides hand-on practice in its use within the MDX Filter() 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. Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In IsSibling(): Conditional Logic within Calculations, another article within my MDX Essentials series, we introduced the IsSibling() function, from the perspective of its use within a calculation. We discussed the straightforward purpose of this logical function, to return a value indicating whether or not a member we specify is the sibling of another member we specify. (By “sibling,” of course, we mean that the two members share the same parent.) We discussed the manner in which IsSibling() 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.

In this article, we will examine IsSibling(), once again as a conditional logic modifier, but within the context of a filter. Combining IsSibling() with the MDX Filter() function is another way we commonly see it in action in the business environment, and our exposure to the practical aspects of its employment in this way will serve to round out our overall awareness of the potential of IsSibling(). From the perspective of its use in combination with Filter(), this article will include:

  • A review of the general syntax surrounding the function;
  • Illustrative examples of uses of the function in practice exercises;
  • A brief discussion of the MDX results obtained within each of the practice examples.

The IsSibling() Function

Introduction

As we related in IsSibling(): Conditional Logic within Calculations, the Books Online tell us that 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 is a sibling of the second specified member; 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 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 based upon the nature / status of members.

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 hypothetical business needs that illustrate uses 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.

NOTE: For more detail surrounding the Filter() function, see Basic Set Functions: The Filter() Function, a member of my Database Journal MDX Essentials series.

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 and operators, pairing the IsSibling() function with other MDX functions and operators can help us to leverage its power even further.

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

Syntax

To review the syntax involved with employing the IsSibling() function, we specify the primary member expression (the member are testing as to “sibling status”) and the secondary member expression (the member against which we are testing the primary member) within parentheses to the immediate right of the function. The function takes the member expressions, which are 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 (or, in other words, 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 (that is to say, 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)

We noted the ease with which we can employ IsSibling() in IsSibling(): Conditional Logic within Calculations. We simply place the primary and secondary member expressions, respectively, under consideration 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 expression:

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

returns True if the current member of the Geography dimension / Geography hierarchy is any of the following:

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

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

Depending upon the structure of the query (and specifically upon whether the syntax 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() operator in a couple of queries that illustrate its operation, this time focusing on combinations with the MDX Filter() function. We will do so in simple scenarios that place IsSibling() 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. 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

Procedure: Satisfy Business Requirements with MDX

We will assume a scenario upon which to base our practice example, as we consistently do within articles of this series: we have received a request for assistance from representatives of our client, a team of analysts within the Controllers’ Group of the Adventure Works organization. The group informs us that they have determined a further need for our assistance in their use of the IsSibling() function, which we introduced to them in IsSibling(): Conditional Logic within Calculations.

Our client colleagues tell us that they need, once again, to understand a means, within MDX, of distinguishing whether or not members are siblings. This time, they need a general way to filter non-sibling members from a broader dimension membership that includes sibling members. As an example, they have an immediate need to determine a measure, Reseller Sales Amount, for Calendar Year 2004, for the Sales Territories member or members that exist, within the Sales Territories dimensional hierarchy, at the same level as the United States.

To review the structure, the Sales Territories dimension within the Adventure Works cube contains members at different levels. Reseller Sales Amount is aggregated no lower than the Country level for some territories, while the “lowest level value” exists for one, the United States, at a Regional level (Central, Northeast and Southwest United States, for example). The Sales Territories dimensional structure is shown in Illustration 1.


Illustration 1: The Sales Territory Dimensional Hierarchy

The Adventure Works analysts tell us that they need to present the Reseller Sales Amount for the Sales Territories member or members that share the same parent within the Sales Territories dimensional hierarchy as the United States. They wish to do so with a single query, and ask us if, based upon what they have learned about the IsSibling() function, the same sort of logic might be used in a filter of the Sales Territories within a query crafted to return the Sales information.

We review the concepts behind the IsSibling() function that we introduced in our last discussion with our client colleagues, and then we offer to illustrate the use of IsSibling() to meet the immediate needs. The client representatives acquiesce, and we set about the assembly of our first example to illustrate the use of IsSibling() in combination with the Filter() function.

Procedure: Use the IsSibling() Function to Perform Conditional Logic within a Filter Expression

Per the request of our client colleagues, we will first construct a simple query to provide an illustration of the use of the IsSibling() operator within a common context, the definition of a filter based upon conditional logic. Our first example will serve as an introduction to a means of distinguishing sibling members within the Sales Territory dimension. This will address the request of the analysts; the results of this determination will form the basis for illustrating a means for meeting their business requirement to filter non-sibling members from the dimension for presentation purposes.

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


/*  MDX051-001 IsSibling() Operator: 
       Conditional Logic within Filter() Function  */
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
   {FILTER(
     [Sales Territory].[Sales Territory].MEMBERS, 
        ISSIBLING([Sales Territory].[Sales Territory].CURRENTMEMBER, 
    [Sales Territory].[Sales Territory].[Sales Territory Country].[United States]))
 
  }ON AXIS(1)
FROM 
   [Adventure Works]
WHERE 
    [Date].[Calendar].[Calendar Year].[CY 2004]

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


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

The above query selects the Reseller Sales Amount for all Sales Territory members, filtered by the condition “ ... that are siblings of the Sales Territory Country United States.” Our IsSibling() function forms the “search condition” of “Sales Territory Country members that are siblings of the United States.”

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


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

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


Illustration 4: Results Dataset – IsSibling() function within Filter() Function

In the returned dataset, we see that the query delivers the intended result: the Reseller Sales Amount is returned for the United States and its single sibling within the Sales Territory dimension. We can verify that Canada is the only sibling by a quick review of the dimensional structure within the Metadata pane, which appears as partially shown in Illustration 5.


Illustration 5: The Single Sibling in the Metadata Pane (Partial View)

It is important to note that only Canada is selected, despite the fact that other Countries exist within the dimension. The key criterion that is met for selection as a sibling is that the two members have a common parent – in this case the North America Sales Territory Group.

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

Our client colleagues express satisfaction with the example we have provided, and agree with our suggestion that another example will further reinforce their understanding. This time, we suggest, we will derive the MDX to meet a requirement, and then add a Named Set to contain the logic, a practice that can mean flexible reuse of the code in a reporting scenario, as we have seen in previous articles. (Properly implemented Named Sets can also boost performance, in addition to providing a means for enforcing consistent logic among the report authors that we support.)

As an illustration, we formulate a business requirement that relates to Sales Associates, one of several Employee groups at Adventure Works. Let’s say that we wish to present the Reseller Sales Amount attributable to individual sales people for Calendar Year 2004. We are given to understand that Reseller Sales Amounts can only be associated with Employees assigned to Sales, although the values associated with non-managers – the actual salespeople – are the values in which we are interested for our immediate purposes. (The values associated with management personnel typically contain “rolled up” values for those sales people within their management spheres as at least part of their totals, so we wish in this case to exclude them).

To paraphrase the requirement, then, we are interested in retrieving the Reseller Sales Amount for “employee siblings” within the sales department. (While many other Employees reside at the same dimensional level within the Employee dimension, we confirm our understanding that, since only sales Employees can have an associated Reseller Sales Amount value, it will be sufficient to retrieve employees members with the associated values; filtering for sibling members will then help in eliminating sales managers from consideration.)

We will begin a new query, and build a proposed approach in multiple steps.

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

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


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

Let’s begin with an “intuitive” approach – as a means of crafting a core query, as well as generating a result that will form a basis for contrast between a listing of “all Sales employees with an associated Reseller Sales Amount value” (including the sales managers I mentioned earlier) and our ultimate focus of sibling members of the Sales organization with an associated Reseller Sales Amount value.”

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


-- MDX051-002-1  Initial Attempt at a Sales Employee Listing
        
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
              
   NONEMPTY( {[Employee].[Employee Department].MEMBERS})ON AXIS(1)
      
FROM
 
   [Adventure Works]
WHERE 
[Date].[Calendar].[Calendar Year].[CY 2004]

The Query pane appears, with our input, as shown in Illustration 7.


Illustration 7: 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 depicted in Illustration 8 appears.


Illustration 8: Results Dataset – Unfiltered Employee Members

In the returned dataset, we see the unfiltered list of Employees with an associated Reseller Sales Amount value. As we have discussed, these members happen to be sales personnel, but the presented list contains non-sibling Employees. We can verify this by inspecting the dimensional structure in the Analysis Services Cube Browser, a view of which appears in Illustration 9.


Illustration 9: The Employee Dimension Hierarchy – Relevant Members

We can see that, while fourteen employees exist at the bottom level (Level 5), several more members exist when we count higher levels (including the “All” level) within the dimension. Our ultimate objective is to deliver the sibling Sales members – in this case, the fourteen individuals appearing within Level 5. We ask for the name of one of the salespersons within the level, so as to be able to specify it as the secondary member expression within the IsSibling() function (the one against which the Current Members will be compared to determine “siblinghood.”)

8.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX051-002-1, and place it in a meaningful location.

Our next step will be to filter the non-sibling members from the Employees listed in the returned dataset. We will do this within the query first, before finalizing the solution by placing the working logic into a Named Set we create for that purpose in the last step. We are given the name Jae B. Pak, which we are able to locate within the hierarchy we examined earlier in a view from the Cube Browser. We can thus construct the qualified name of our “basis” member, at the appropriate point within the modifications we will next perform upon our initial query.

9.  Replace the top line of the query (commented out) with the following:

-- MDX051-002-2  Adding the Filter() / IsSibling() Combination

10.  Select File -> Save MDX051-002-1.mdx As ..., name the file MDX051-002-2, and place it in a meaningful location.

11.  Place the cursor to the immediate right of the left curly brace - “ { “ – following the NONEMPTY keyword (currently on the fourth line of the query).

12.  Press the ENTER key four times to “push down” the rest of the line, and to add space between the remaining “NONEMPTY(“ and the rest of the line.

13.  Between what is now the fourth (containing “NONEMPTY(“ ) line and the fifth (containing “{[Employee].[Employee Department].MEMBERS})ON AXIS(1)”) line of the query, type in the following syntax:

      FILTER(

14.  Place the cursor to the immediate right of the MEMBERS keyword (currently on the sixth line of the query), between “MEMBERS” and the right curly brace - “ } “ - that is at its right.

15.  Insert a comma ( “,” ) to the immediate right of the MEMBERS keyword.

16.  Press the ENTER key four times, once again to “push down” the rest of the line, and to add space between the remaining “... MEMBERS,” and the rest of the line.

17.  Between what is now the sixth (containing “[Employee].[Employee Department].MEMBERS,”) line and the seventh (containing “})ON AXIS(1)”) line of the query, type in the following syntax:

ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,

18.  Press the ENTER key two times, once again, to add space between the new line and the line immediately underneath it.

19.  Between what is now the seventh (containing “ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,” line and the eighth (containing “})ON AXIS(1)”) line of the query, type in the following syntax:

[Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))

The complete query is as follows, if cutting and pasting is the preference:


-- MDX051-002-2  Adding the Filter() / IsSibling() Combination
        
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
              
   NONEMPTY( {
      FILTER(
         [Employee].[Employee Department].MEMBERS,
            ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
         [Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))
      })ON AXIS(1)
      
FROM
 
   [Adventure Works]
WHERE 
   [Date].[Calendar].[Calendar Year].[CY 2004]

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


Illustration 10: Our Modified Query in the Query Pane ...

20.  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 shown in Illustration 11 appears.


Illustration 11: Results Dataset – Sibling Employee Members

In the returned dataset, we see the now-filtered list of Employees. We can see that the Employees that appear in the returned dataset represent members that are siblings of member Jae B. Pak (and include Jae B. Pak), as shown in Illustration 8 above, with an associated Reseller Sales Amount value.

21.  Select File -> Save MDX051-002-2 to save the file.

Let’s finalize our efforts by placing the logic within a Named Set. Creating the Named Set within Analysis Services will allow for easy reuse of a predefined sibling Sales Employees group in Reporting Services, as well as other client applications, where we might, for example, define a row in a report through the use of a single object.

22.  Replace the top line of the query (commented out) with the following:

-- MDX051-002-3  Reusable Named Set using Filter() / IsSibling() Combination

23.  Select File -> Save MDX051-002-2.mdx As ..., name the file MDX051-002-3, and place it with the queries saved earlier.

24.  Place the cursor to the immediate right of the statement inserted above (in the top row).

25.  Press the ENTER key four times to “push down” the rest of the line, and to add space between the comment line and the SELECT keyword.

26.  Insert the following into the space between the comment line and the SELECT keyword:


WITH
SET
   [SALES OPERATIVES] 
 
AS
   'FILTER(
   
      [Employee].[Employee Department].MEMBERS,
   
         ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
   
      [Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))'

Here we have simply defined a Named Set containing the logic that we used within our row axis definition in the previous example.

27.  Replace the following four lines within the Select statement (currently lines 13, 14, 15, and 16):


FILTER(
         [Employee].[Employee Department].MEMBERS,
      ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
   [Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))

with the following:

[SALES OPERATIVES]

The complete query is as follows, if cutting and pasting is the preference:


-- MDX051-002-3  Reusable Named Set using Filter() / IsSibling() Combination
WITH
SET
   [SALES OPERATIVES] 
 
AS
   'FILTER(
   
      [Employee].[Employee Department].MEMBERS,
   
         ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
   
      [Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))'
         
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
              
   NONEMPTY( {
   
    [SALES OPERATIVES]
   
   })ON AXIS(1)
      
FROM
 
   [Adventure Works]
WHERE 
   [Date].[Calendar].[Calendar Year].[CY 2004]

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


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

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

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


Illustration 13: Results Dataset Sibling Employee Members via Named Set

In the returned dataset, we see the same filtered list of employees. This serves to illustrate how we might meet the business need with a Named Set, which, once created within the cube involved, would support easy, consistent reporting via a single object for row / column definitions, and the like.

29.  Select File -> Save MDX049-002-3 to save the file.

The client representatives confirm that their immediate goals have been met, and that the illustrations we have provided can be easily extended to local business scenarios where filtering for sibling members is useful in meeting reporting and analysis requirements.

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

Summary ...

In this article, we extended our examination of the IsSibling() function, exploring its use, once again, as a conditional logic modifier - but this time within the context of a filter, through its combination with the MDX Filter() function. We stated that, along with the IIF() function, this is another commonly employed approach for using IsSibling() within the business environment.

We next reviewed the general syntax involved in using IsSibling(). Finally, we undertook illustrative examples whereby we put the IsSibling() function to work, in combination with the Filter() function, initially within a simple illustration to illustrate its general operation. We followed our initial practice example with another, where we began by employing the IsSibling() / Filter() combination within a direct row-axis definition, before placing the combination within a Named Set, to meet a hypothetical business need. 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