Logical Functions: IsGeneration(): Conditional Logic within Calculations
May 7, 2007
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.
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 members 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:
The IsGeneration() Function
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.
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.
Lets look at syntax specifics to further clarify the operation of IsGeneration().
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:
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:
returns True for the current member of the Date dimension / Calendar hierarchy for each of the following:
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.