Logical Functions: IsGeneration(): Conditional Logic within Calculations

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.

Overview

It should come as no
surprise, to anyone who works with MDX on a regular basis, that, in working
with multidimensional data, it is not uncommon to find ourselves confronted
with business needs to have some calculated members give different results,
depending upon “where in the cube” they are being evaluated. It is in these
scenarios where we find great utility in leveraging the combination of conditional
logic and the properties of sets, tuples, hierarchies, levels and / or members
to determine the “evaluation location” of the member(s) of interest. Based
upon the location that is ascertained, we can render the formula required to
give the result we need for the given “position.”

We have seen this concept
in action in several articles within my MDX
Essentials series. We can test whether the current member is
positioned at, above or below a given level by using the IS operator, or
a function that integrates the IS operator, such as IsSibling()
or IsAncestor(). We can also use other approaches, such as comparing
the level ordinal of a member to the ordinal of another level of interest (via
the .Ordinal function), or by comparing a given member’s ancestor
to an ancestor of interest (through a combination of the Ancestor()
and .UniqueName functions, among other possible approaches).

NOTE: For a detailed
look at the .CurrentMember function, see MDX
Member Functions: "Relative" Member Functions. For a hands-on introduction to the IS
operator, see The IS
Operator. For information about the IsSibling()
function, see Logical
Functions: IsSibling(): Conditional Logic within Calculations
and Logical
Functions: IsSibling(): Conditional Logic within Filter Expressions.

For
more about the IsAncestor() function, see Logical
Functions: IsAncestor(): Conditional Logic within Calculations and Logical
Functions: IsAncestor(): Conditional Logic within Filter Expressions. Moreover, for a hands-on introduction to the Ancestor()
function, see MDX
Member Functions: The "Family" Functions. Finally, for details surrounding the .UniqueName function, see String
Functions: The .UniqueName Function.

(All
articles are members of my MDX Essentials series
at Database Journal.)

In
this lesson, we will expose another logical function which we can use for
testing a member for which a cell is being calculated, the IsGeneration()
function. IsGeneration(), like other logical functions and
operators, performs an evaluation and returns a Boolean value. The utility
of IsGeneration() becomes clear when we realize the capability that it
gives us to determine the “position,” together with the relationship to
progenitors, of a member within a dimensional hierarchy. IsGeneration()
more specifically allows us to test whether or not a specified member exists
within a specified generation. (The effective use of IsGeneration()
as a testing mechanism is dependent upon a good understanding of the manner
with which generation numbers are assigned within Analysis Services. For
this reason, we will overview the generation concepts in our preliminary commentary
surrounding the function.)

Similar to IsLeaf(), IsSibling(), IsAncestor()
and other MDX functions, IsGeneration() can best be employed to
apply conditional logic within a couple of ways: as a component within a
calculation, and as a component within a filter expression. In this
article, we will concentrate upon IsGeneration() 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
ancestor of another specified member; the manner in which IsGeneration()
accomplishes 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 IsGeneration() function, and a brief
discussion of generation number assignment, 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 IsGeneration() Function

Introduction

According to the Books
Online, the IsGeneration() function “returns true if the member
indicated … is in the generation specified….” Otherwise, the function returns
false. Also, if the member we specify evaluates to an empty member, the IsGeneration()
function returns false. A Boolean value of “True” is returned if the member
to which the function is applied resides within the generation number
specified by the Numeric Expression we provide, and “False” is returned
if it does not (or if the member expression we supply identifies an empty
member).

Part of understanding the
operation of IsGeneration() centers upon an understanding of how generation
numbers are assigned (since the number is used as the “comparison target”
within the function, which determines the result of a comparison test to be
“true” or “false”). The assignment of generation numbers actually
begins with leaf-level members, who are assigned a zero (“0”) generation.
The assignment of a generation number to a non-leaf level member is
based upon a rule of “one” (“1”) plus the number of levels existing between the
leaf level of a given hierarchy and the parent of the tree – in effect, it is
the “number of steps away” from the leaf level members of the hierarchy
which it inhabits. To illustrate the concept, a straightforward hierarchy
(together with assigned generation numbers), Date.Calendar, from
the sample Adventure Works cube, is partially depicted in Illustration
1.

Things can become somewhat
more complicated, of course, in cases of ragged hierarchies, where parent
visibility becomes important (only visible members factor into the generation
number assignment, for example). We will stay within the bounds of
balanced hierarchies for the purposes of our examples, but it is important to
remember that this is often a consideration in the real world.

We will examine in detail the
syntax for the IsGeneration() 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 IsGeneration()
can offer the knowledgeable user. Hands-on practice with IsGeneration(),
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, IsGeneration() returns “True” if a
specified Member Expression is the number of “steps” specified (by the Numeric
Expression) away from the leaf level of the containing hierarchy.
Alternatively, “False” is returned if the specified Member Expression
is not the specified number of steps away, or if the Member
Expression evaluates to an empty member. We can use IsGeneration() 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 IsGeneration()
with other MDX functions can help us to leverage its power much further
than we might in attempts to use it in standalone fashion.

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

Syntax

Syntactically, we employ the IsGeneration()
function by specifying the Member Expression (the member which we
are testing as to “generation member status”) and the Numeric Expression (the
generation number in relation to which we are testing the Member
Expression) within parentheses to the immediate right of the function. The function
takes the Member Expression and Numeric Expression thus appended as
its arguments, and returns True if the member denoted by the Member Expression
exists within the specified generation number (Numeric Expression)
of the hierarchy within which it resides (or, in other words, if the Member
Expression lies the number of steps specified by the Numeric Expression
from the leaf level of the containing hierarchy.

If the member specified by the Member
Expression is evaluated as an empty member, or if the Member Expression
does not exist within the generation number specified by Numeric
Expression, then a False is returned, as we have noted.

The general syntax is shown in
the following string:

IsGeneration(Member_Expression, Numeric_Expression) 

Employing IsGeneration() is as
straightforward, in the mechanical sense, as working with most of the
MDX logical functions, assuming that we have an adequate grasp of
the meaning of generation, as we have discussed in earlier sections . As
we have noted, we simply place the Member Expression and Numeric
Expression, respectively, in the parentheses to the right of the function. As an example, and as a confirmation of some of
the details of the generation information presented in Illustration 1
above, within a query executed against the sample Adventure Works cube, for
the dimension named Date (with a hierarchy of Calendar), the
following pseudo-expression:

IsGeneration([Date].[Calendar].CurrentMember, 4)

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

• CY 2001

• CY 2002

• CY 2003

• CY 2004

Each of the listed members is a “resident” of generation
number 4 of the Date.Calendar hierarchy – which appears among the
other levels as noted in Illustration 2.

Depending upon the structure of the query (and specifically
upon whether the syntax defining axes, etc., eliminates nulls), if members of
other hierarchies, or members of generations other than number 4,
within the hierarchy, were returned in, say, the row axis of the dataset, their
associated values would be null.

We will practice some uses of the IsGeneration() function
in the section that follows.