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

Monday Apr 2nd 2007 by William Pearson
Share:

Use IsAncestor() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsAncestor() 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.

Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In IsAncestor(): Conditional Logic within Calculations, another article within my MDX Essentials series, we introduced the IsAncestor() function from the perspective of its use within a calculation. We discussed the straightforward purpose of this logical function, to return whether or not a specified member is an ancestor of another member we specify (By “ancestor,” of course, we mean a member from which the specified member is descended within a dimensional hierarchy.) We discussed the manner in which IsAncestor() 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 IsAncestor(), once again as a conditional logic modifier, but within the context of a filter. Combining IsAncestor() 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 IsAncestor(). From the perspective of the use of the IsAncestor() function 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 IsAncestor() Function

Introduction

As we related in IsAncestor(): Conditional Logic within Calculations, the Books Online tell us that the IsAncestor() function “returns whether a specified member is an ancestor of another specified member.” A Boolean value of “True” is returned if the member expression to which the function is applied is an ancestor of the second specified member; otherwise IsAncestor() returns “False.” In its capacity, as a logical function, to “test” the nature / status of a member, IsAncestor() 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 relationship between members as ancestor / descendant.

We will examine in detail the syntax for the IsAncestor() 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 IsAncestor() can offer the knowledgeable user. Our current examination will focus upon the use of IsAncestor() within the context of a filter. Hands-on practice with IsAncestor(), 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, IsAncestor() returns “True” if a specified member expression represents an ancestor of another member (that is, lies between the secondary member and the top / “All” dimensional level) that we specify within a given use of the function; otherwise, the function returns “False.” We can use IsAncestor() to apply conditional logic, based upon the location and / or existence of members. As we have noted to be the case with most MDX functions, pairing IsAncestor() with other MDX functions can help us to leverage its power much further than we might do in an attempt to use it in standalone fashion.

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

Syntax

To review the syntax involved with employing the IsAncestor() function, we specify the primary member expression (the member which we are testing as to “ancestor status”) and the secondary member expression (the member in relation to 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 an ancestor of the secondary member expression (or, in other words, if the primary member lies somewhere between the secondary member and the “top” of the dimensional hierarchy).

If the member specified by the primary member expression is not an ancestor of the secondary member (or if the primary member and the secondary member belong to different dimensions) a False is returned, as we might expect.

The general syntax is shown in the following string:

IsAncestor(Primary_Member_Expression, Secondary_Member_Expression)

Employing IsAncestor(), like most of the MDX logical functions, is, in the mechanical sense, straightforward. As we have noted, we simply place the primary and secondary member expressions, respectively, within the parentheses to the right of the function. To use the example from our syntax discussion in IsAncestor(): Conditional Logic within Calculations, 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,

IsAncestor([Geography].[Geography].[State-Province].[South Australia],
      [Geography].[Geography].CurrentMember) 

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

  • Cloverdale
    • 6105
  • Findon
    • 5023
  • Perth
    • 6006

Each of the listed members is a descendant of South Australia in the cube, as shown in Illustration 1.


Illustration 1: Descendants of South Australia ...

Depending upon the structure of the query (and specifically upon whether the syntax defining axes, etc., eliminates nulls), if members of other dimensions, or members of levels higher than South Australia 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 IsAncestor() 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 IsAncestor() function 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 IsAncestor() 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 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

Procedure: Satisfy Business Requirements with MDX

We will assume a business scenario upon which to base our practice examples, 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 IsAncestor() function, which we introduced to them within the scenario described in IsAncestor(): 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 a given member is the ancestor of another specified member (or, inversely, to apply a test to ascertain whether a given member is the descendant of another specified member). This time, they need a general way to filter members with the same Ancestor (actually, Parent, in the simple scenario they lay out for the example) from a broader dimension membership, that includes members with different Ancestors / Parents. As an example, they have an immediate need to determine values for two measures, Reseller Sales Amount and Reseller Order Quantity, for Calendar Year 2004, for the Reseller member or members (by Name) that exist, within the Reseller dimensional hierarchy, and which belong to the Warehouse Business Type. (Reseller Business Type “Warehouse” is the Ancestor / Parent whose Descendants / Children we seek to return).

To review the structure, the Reseller dimension within the Adventure Works cube contains members at different levels. The Reseller Name Level, representing the individual Resellers, is subordinate to the Reseller Business Type (consisting of Specialty Bike Shop, Value Added Reseller, and Warehouse types), within the Reseller Type hierarchy of the Reseller dimension. The Reseller dimensional structure is depicted in Illustration 2.


Illustration 2: The Reseller Type Hierarchy of the Reseller Dimension

The analysts tell us that the values under immediate consideration involve Reseller Sales Amount and Reseller Order Quantity, but, as is typically the case in our collaborative sessions, they want to develop an approach that will work equally well with other measures that have similar analysis potential. (We have noted, many times in past sessions, that they often derive parameterized queries in Reporting Services from the basic MDX syntax we assemble together, and can thus create self-serve reports that allow information consumers to dictate what measure they wish to analyze, and myriad other options, at run time). The desired end is to simply return the non-zero Reseller Sales Amount and Reseller Order Quantity totals, for each Reseller of Warehouse Business Type, for Calendar Year 2004.

As is so often the case, this basic need might be easily met a number of ways with an MDX query. The analysts throw a further twist into the requirement, however, that tends to make our “choice of tools” lean more toward the topic of this article. In addition to being likely to parameterize the calendar year at runtime, they also want to be able to support parameterization of the Business Type within the Reseller dimension (Reseller Type hierarchy) when executing the report (that is, to be able to change it from, say, Warehouse to Value Added Reseller). Once 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 developers ready access to the MDX syntax that underlies them – many enterprise packages, of course, do not).

After we initially explain the use of the IsAncestor() function as one candidate for meeting the requirement, our client colleagues state that they are interested in understanding how they might apply filtering 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 is an Ancestor (a Parent is only one example, obviously, so IsAncestor() is obviously a more flexible function for the ideas they have) to another specified member, or group of members, is something that they hope to be able to extrapolate to uses with other dimensions, as well.

We offer to illustrate the use of IsAncestor() to meet the immediate need, proposing to present an additional example, as well, 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 IsAncestor().

Procedure: Use the IsAncestor() 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 IsAncestor() 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 members with a specified Ancestor (in this case, as we have noted, a Parent) within the Reseller 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 all except specified descendants from the dimension for presentation purposes.

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


-- MDX054-001-1 Employing the Filter() / IsAncestor() Combination
SELECT
   {[Measures].[Reseller Sales Amount], 
      [Measures].[Reseller Order Quantity]} ON AXIS(0),
   NON EMPTY
      {FILTER(
        [Reseller].[Reseller Type].[Reseller Name].MEMBERS,
           ISANCESTOR([Reseller].[Reseller Type].[Business Type].[Warehouse],
        [Reseller].[Reseller Type].CURRENTMEMBER))} ON AXIS(1)
FROM
   [Adventure Works]
WHERE 
         ([Date].[Calendar].[Calendar Year].[CY 2004])

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


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

The above query selects the Reseller Sales Amount and Reseller Order Quantity for all Reseller Name members, filtered by the condition “ ... for whom

Business Type is Warehouse.

Our IsAncestor() function forms the “search condition” of “Reseller Name members that share the Ancestor Warehouse Business Type.”

2.  Execute the query by clicking the Execute button in the toolbar, as depicted in Illustration 4.


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

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


Illustration 5: Results Dataset (Partial View) – IsAncestor() Function within Filter() Function

In the returned dataset, we see that the query delivers the intended result: the Reseller Sales Amount and Reseller Order Quantity are returned for the Reseller Names with common Ancestor, Warehouse Business Type.

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

Because the Ancestor happens also to be the Parent in the present case, we can easily verify the returned data by substituting an expression containing the .Children function into our query to perform a quick check of the completeness and accuracy of the population of the returned dataset. Let’s create a new query to do exactly that.

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.

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


-- MDX054-001-2  Employing the Filter() / IsAncestor() Combination:  
--   Population Check
SELECT
   {[Measures].[Reseller Sales Amount], 
      [Measures].[Reseller Order Quantity]} ON AXIS(0),
   NON EMPTY
      {[Reseller].[Reseller Type].[Business Type].[Warehouse].CHILDREN}

   ON AXIS(1)
FROM
    [Adventure Works]
WHERE 
   ([Date].[Calendar].[Calendar Year].[CY 2004])

Because the Ancestor in our example happens to be a Parent, we can derive the same row axis definition via the use of the .Children function. While this will not necessarily allow for the potential parameterizations that our client colleagues have requested (the reason behind our selecting the IsAncestor() function as an approach to meeting the original business requirement), it will still serve to help us check our simple example results.

NOTE: For detailed information about the .Children function, see my article MDX Member Functions: The "Family" Functions, within the Database Journal MDX Essentials series.

7.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is populated by Analysis Services, and the dataset that appears is identical to the dataset that was returned by our first query above.

8.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX054-001-2, and place it in the location where you placed the first query.

Our client colleagues express satisfaction with the example we have provided, and agree with our suggestion that another example will further reinforce their understanding. Together we formulate a business requirement that relates to the Bikes Product Category, one of several Product Categories sold by Adventure Works. Let’s say that we wish to present, once again, the non-zero Reseller Sales Amount and Reseller Order Quantity attributable to the sales of Products within the Bikes Product Category within Calendar Year 2004. The requirement is straightforward, and virtually mirrors the first example with regard to applicable approaches.

To paraphrase the requirement, then, we are interested in retrieving the Reseller Sales Amount and Reseller Order Quantity (where non-empty) for those Products, sold within Calendar Year 2004, which share the Ancestor Bikes at the Product Category level, within the Product dimension. Our client colleagues confirm the accuracy of the statement, and we set about the construction of a new query.

9.  Select File --> New from the main menu, once again.

10.  Select Query with Current Connection from the cascading menu that appears next, as we did earlier.

A new tab, with a connection to the Adventure Works cube appears in the Query pane, as before.

11.  Type (or cut and paste) the following query into the new tab of the Query pane:


-- MDX054-002 Employing the Filter() / IsAncestor() Combination
SELECT
   {[Measures].[Reseller Sales Amount], 
      [Measures].[Reseller Order Quantity]} ON AXIS(0),
   NON EMPTY
      {FILTER(
          [Product].[Product Categories].MEMBERS,
             ISANCESTOR([Product].[Product Categories].[Bikes],
                [Product].[Product Categories].CURRENTMEMBER))} 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 ...

12.  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 (Partial View) – IsAncestor() Function within Filter() Function

In the returned dataset, we see the list of Product members with an associated Reseller Sales Amount and Reseller Order Quantity value. The selected members this time, however, are not simply direct children (they are “grandchildren,” as it were), although one direct child member, Mountain Bikes, tops the results dataset (and serves effectively, by the way, as a total row for the other members in the dataset.) We can verify this by inspecting the dimensional structure in the metadata pane for the Product Categories hierarchy of the Products dimension, a partial view of the relevant portion of which appears in Illustration 9.


Illustration 9: The Product Categories Hierarchy of the Product Dimension (Partial View)

Our ultimate objective, then, to deliver the members who share the Bikes Product Category as an Ancestor, appears to have been met.

13.  Select File -> Save MDXQuery3.mdx As ..., name the file MDX054-002, and place it in the same location as the other queries we have saved within this session.

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 with the IsAncestor() function (perhaps within calculated members and / or named sets, as well, as we have demonstrated with other logical functions in other articles of the series) is useful in meeting reporting and analysis requirements.

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

Summary ...

In this article, we extended our examination of the IsAncestor() 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 IsAncestor() within the business environment.

We next reviewed the general syntax involved in using IsAncestor(). Finally, we undertook illustrative examples whereby we put the IsAncestor() function to work, in combination with the Filter() function, using simple illustrations, which might be extended for use within calculated members and / or named sets, to illustrate its general operation to meet hypothetical business needs. 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